[AccessD] TOP 30 for multiple theaters.

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




More information about the AccessD mailing list