Rocky Smolin - Beach Access Software
bchacc at san.rr.com
Tue Mar 15 09:01:11 CST 2005
Erwin: Would a possible solution, (not well thought through, and involving some code) be to start with a query (actually a SQL statement as the record source for a recordset) that returns all the theatre names - unique values. In code loop through this recordset creating a new query to return the top thirty films for each theatre. 22 theatres, 22 queries. Finally, create another query in code that UNIONs all of the theater queries as the record source for the report. Rocky ----- Original Message ----- From: "Erwin Craps - IT Helps" <Erwin.Craps at ithelps.be> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com> Sent: Tuesday, March 15, 2005 2:12 AM Subject: RE: [AccessD] TOP 30 for multiple theaters. > 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 > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock > Sent: Tuesday, March 15, 2005 10:51 AM > To: accessd at databaseadvisors.com > Subject: RE: [AccessD] TOP 30 for multiple theaters. > > Hi Erwin > > Should we call you Erwin the Chicken? > > As I also wrote - sometimes a temp table is the solution. Neither do I > like them, but here we are talking about 100 records! It's not a > million. > If you still wish to avoid that, create a temp database and put the > table there. Mr. Colby has ready code on his site for this. Creating a > temp database with a temp table properly indexed takes no more than a > split second. > > About the speed, if you open the temp database directly you can use Seek > on the table which is so fast that you hardly will believe it. > > /gustav > >>>> Erwin.Craps at ithelps.be 15-03-2005 10:14:28 >>> > > Thank you for the compliment ;-) > > Well indeed, this could be easely resolved with code But this would > require a temp table and I don't like the use of temp tables. > I also believe that a good query is faster than code when performing a > 'mass' approach. > > And ofcourse to enrich my SQL syntax, I need to migrate my biggest MDB > to SQL server very soon and probably need some SQL knowledge lift... > > Erwin > > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin > - Beach Access Software > Sent: Monday, March 14, 2005 11:04 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] TOP 30 for multiple theaters. > > Erwin: > > This is a really quick job with a bit of code ( I KNOW you're good at > that, > too) and a temp table. Queries are fun, especially the really tough > ones. > Like the NYT Crossword. You can spend all Sunday on it. Maybe it's > because I'm an old coder. But when the queries get this complicated, the > coding solution looks so much easier. > > Rocky > > ----- Original Message ----- > From: "Erwin Craps - IT Helps" <Erwin.Craps at ithelps.be> > To: "Access Developers discussion and problem solving" > <accessd at databaseadvisors.com> > Sent: Monday, March 14, 2005 1:51 PM > Subject: RE: [AccessD] TOP 30 for multiple theaters. > > >>I need a summation TOP 30 per theatre... >> >> >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky > Smolin >> - Beach Access Software >> Sent: Monday, March 14, 2005 4:07 PM >> To: Access Developers discussion and problem solving >> Subject: Re: [AccessD] TOP 30 for multiple theaters. >> >> Erwin: >> >> It looks like a job for a summation query grouping on theatre and > movie, >> count on tickets, and sum on box office. But that's too obvious. >> What's different about your requirement that it won't work that way? >> >> Rocky >> >> ----- Original Message ----- >> From: "Erwin Craps - IT Helps" <Erwin.Craps at ithelps.be> >> To: <accessd at databaseadvisors.com> >> Sent: Monday, March 14, 2005 2:45 AM >> Subject: [AccessD] TOP 30 for multiple theaters. >> >> >> (sorry for sending previous message not compleeted....) >> >> Hi group >> >> I need a query that results in a list for a specific year, > numerating >> all movietheaters and for each theatre a TOP 30 of movies. >> >> Is it posible, and how do I do this to create a query with the > folowing >> result. >> Movietheatre X, movie 01, tickets, boxoffice Movietheatre X, movie 02 >> , tickets, boxoffice Movietheatre X, movie ... , tickets, boxoffice >> Movietheatre X, movie 29 , tickets, boxoffice Movietheatre X, movie 30 > >> , tickets, boxoffice Movietheatre Y, movie 01, tickets, boxoffice >> Movietheatre Y, movie 02 , tickets, boxoffice Movietheatre Y, movie >> ... , tickets, boxoffice Movietheatre Y, movie 29 , tickets, boxoffice > >> Movietheatre Y, movie 30 , tickets, boxoffice Movietheatre Z, movie >> 01, tickets, boxoffice Movietheatre Z, movie 02 , tickets, boxoffice >> Movietheatre Z, movie ... , tickets, boxoffice Movietheatre Z, movie > >> 29 , tickets, boxoffice Movietheatre Z, movie 30 , tickets, boxoffice >> >> I see only the posiblilty to create a query for each theatre and use > a >> union query afterwards. >> Or by the use of a temp table, but I don't like using temp tables... >> >> The source table looks like this. >> >> movietheatre, year, week, movie, tickets, boxoffice >> >> >> thx >> >> >> >> >> >> Erwin Craps >> >> Zaakvoerder >> >> www.ithelps.be/onsgezin >> >> >> >> This E-mail is confidential, may be legally privileged, and is for > the >> intended recipient only. Access, disclosure, copying, distribution, > or >> reliance on any of it by anyone else is prohibited and may be a > criminal >> offence. Please delete if obtained in error and E-mail confirmation > to >> the sender. >> >> IT Helps - I.T. Help Center *** Box Office Belgium & Luxembourg >> >> www.ithelps.be <http://www.ithelps.be/> * www.boxoffice.be >> <http://www.boxoffice.be/> * www.stadleuven.be >> <http://www.stadleuven.be/> >> >> IT Helps bvba* ** Mercatorpad 3 ** 3000 Leuven >> >> IT Helps * Phone: +32 16 296 404 * Fax: +32 16 296 405 E-mail: >> Info at ithelps.be >> >> Box Office ** Fax: +32 16 296 406 ** Box Office E-mail: >> Staff at boxoffice.be <mailto:figures at boxoffice.be> >> >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >