Scott Marcus
marcus at tsstech.com
Tue Mar 15 06:53:03 CST 2005
Erwin,
Couple of problems...
The data you sent, most customers do not have 30 or more
products/movies. I still did the query. The table fields are very
archaic but I think I interpreted them correctly.
Finally, there are 2 queries... run the one named 'qryTop30ByCustomer'.
My zipped attachment got filtered. So, here are the queries based on the
tables you sent...
qryTicketsByYear:
SELECT CalendarYear,
CUST_GID,
Product_LID,
Sum(Tickets) AS SumOfTickets
FROM HISTO_YearCalender
GROUP BY CalendarYear,
CUST_GID,
Product_LID
HAVING CalendarYear=[What Year?];
qryTop30ByCustomer:
SELECT B.CUST_GID,
B.Product_LID,
B.SumOfTickets
FROM qryTicketsByYear AS B
WHERE B.Product_LID
In (SELECT TOP 30 A.Product_LID
FROM qryTicketsByYear AS A
WHERE A.cust_gid=B.cust_gid
ORDER BY A.SumOfTickets DESC)
ORDER BY B.CUST_GID,
B.SumOfTickets DESC;
Scott Marcus
TSS Technologies, Inc.
marcus at tsstech.com
(513) 772-7000
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Erwin Craps -
IT Helps
Sent: Tuesday, March 15, 2005 5:12 AM
To: Access Developers discussion and problem solving
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