A.D.Tejpal
adtp at touchtelindia.net
Wed Mar 16 11:24:14 CST 2005
Erwin,
Query based solution will necessarily involve a totals query, which in turn becomes the source for the final select query. However, it is observed that any such query (using the totals query as a source) runs unacceptably slow.
The solution lies in appending the output of totals query into a temporary table and using that table as the source for final query. The term temporary implies that data in this table is temporary. The table itself is permanent, like other regular tables.
It is found that if the final select query is based upon a subquery using IN clause, the execution is extremely slow, even when using temp table as the source. On the other hand, if a subquery for getting running count of rank is used and all ranks per year per theatre lower than the desired one are filtered out, there is dramatic improvement in speed (the data in your sample db gets processed in less than a second).
With the data in your sample db, query based solution for getting top five movies per theatre per year has been worked out (it can be easily modified to suit top 30 values). It is based upon following steps
(a) Totals query (Q_Sales), grouping by year, theatre and movie, reflecting the sum of tickets sold and turnover (box office)
(b) Append query (Q_App) transferring the output of (a) into temporary table T_Temp (after first clearing the temp table of any existing contents).
(c) Select query (Q_TopFivePerYear) based upon T_Temp, showing top five movies (as per tickets sold) per year per theatre. If the top values are required to be as per turnover (instead of tickets sold), the query can be modified suitably.
The code snippet given below displays the desired results on a form in datasheet view. Query Q_TopFivePerYear as per (c) above, serves as record source for this form.
On your confirmation that you are in a position to successfully receive an attachment (for zipped mdb file), sample db demonstrating the solution can be sent to you. eMail address at which it is to be sent, may also please be indicated.
Best wishes,
A.D.Tejpal
--------------
====================================
Sub P_GetTopFivePerYear()
CurrentDb.Execute "Delete * From T_Temp;"
CurrentDb.Execute "Q_App"
DoCmd.OpenForm "F_TopFivePerYear", acFormDS
End Sub
====================================
----- Original Message -----
From: Erwin Craps - IT Helps
To: Access Developers discussion and problem solving
Sent: Tuesday, March 15, 2005 15:42
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