Erwin Craps - IT Helps
Erwin.Craps at ithelps.be
Thu Mar 17 03:25:20 CST 2005
Thank you
You can send it to Erwin.Craps at ithelps.be
Erwin
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: Wednesday, March 16, 2005 6:24 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] TOP 30 for multiple theaters.
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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com