Gustav Brock
Gustav at cactus.dk
Tue Mar 15 04:24:35 CST 2005
Hi Erwin Ha, so you are stubborn too! OK, if it must be a query so be it. I think Scott's query is close but it seems like he needs some real data as you now provide for testing. /gustav >>> Erwin.Craps at ithelps.be 15-03-2005 11:12:20 >>> You not nice to me, now I'm crying :-( But I want to solve this with a query, sniff, sniff... I'm not getting there with your SQL code Scott. I only get the result of the first week (each record is one week) and not the sum for a year or a specific timeframe. I changed the SQL string so the sub query is a group/sum query but then I get errors saying that I only can have one field as a result. I created a small database to download from here: http://www.ithelps.be/temp/BO_testA2K.mdb HISTO_YearCalender is the table containing the data. As a result I need a movie TOP 30 per theatre based on the box office figures Theatre = CUST_GID Movie = Product_LID Tickets= Tickets BoxOffice=Turnover Like this: CUST_GID 1 Product_LID 1, SumOfTickets, SumOfTurnover Product_LID ... Product_LID 30, SumOfTickets, SumOfTurnover CUST_GID ... Product_LID 1, SumOfTickets, SumOfTurnover Product_LID ... Product_LID 30, SumOfTickets, SumOfTurnover CUST_GID 999 Product_LID 1, SumOfTickets, SumOfTurnover Product_LID... Product_LID 30, SumOfTickets, SumOfTurnover Furthermover I need to be able to set year/week criteria Let the challenge begin.... Erwin