Gustav Brock
Gustav at cactus.dk
Tue Mar 15 03:51:24 CST 2005
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