Solved How to pick top 3 out of many...

[Garren.Shannon]

Hey all,

I have to report the top 3 headcount days to the state for 3 count-periods during the year... FirstCount (Sept 1 to Oct 31), SecondCount (Nov 1 to Jan 31st) and LastCount (Feb 1 to Apr 30th).

I have a query that lists all the bus trips between date X to end date Y. All trips have a total headcount (Max Count) for the trip. I need to tally up all the headcounts by day, then report the top 3 days to the state for the count period (First, Second or Last).

So if Sept 11th had 65 trips with a total headcount of 1030 and Sept 4th had 67 trips with total headcount of 1032 and Oct 5th had 64 trips with at total headcount of 1041 and Oct 29 had a total headcount of 1035, I would report Sept 4th (1032), Oct 5th (1041) and Oct 29 (1035).

I think this would be a crosstab query but I can't figure out how to build this. Would I build the initial query to sum by date for max count, then build a crosstab from that query? Or do I need a summation query first, then build a crosstab from that?

Here's the SQL for the current query. It gives me a list of trips by date with the max headcount per trip.

SELECT Trip.[T-Date], Trip.MaxCount, SchoolYrDates.SchoolYear
FROM Trip, SchoolYrDates
WHERE (((Trip.[T-Date]) Between [SchoolYrDates]![CountStartDate] And [SchoolYrDates]![FirstCount]) AND ((SchoolYrDates.SchoolYear)=GetCurrentYear()));


Any help is appreciated.

 

[jdraw]

Would have been better if you provided data rather than a graphic.
You may find this TOP from Allen Brown helpful.

 

[Garren.Shannon]

Would have been better if you provided data rather than a graphic.
You may find this TOP from Allen Brown helpful.

Select TOP 3... awesome answer. Gave me exactly what I needed with a supper short edit to the SQL. Seriously great answer.

 

[Garren.Shannon]

Well, not quite there. TOP is an awesome function and I didn't know about it. However, I still can't seem to find the core solution. Top gives me the top 3 but the query does not total by day... it finds the top 3 days, two of those days should be added together.

Still on the hunt for how to pull the data together.

I use a SchoolYrDates table to record the dates for the School Year. I created a GetCurrentDate() function to find the current school year (which starts in one calendar year and ends in another... see below). I use the function to find what the current school year is whenever I am doing date calculations and queries.

So to pull the first headcount, I need to calculate what the current school year is, pull the CountStartDate and FirstCount values from the SchoolYrDates table that match SchoolYear.

Once I know what the CountStartDate and FirstCount dates are, I can search the TRIPS table for the date ranges between those dates.

So that works and I get them all. But if I try to add a sum to the query, I get following error. Hope this all makes sense to someone. :-(

 

[isladogs]

Use TOP with a subquery. Allen Browne has an example on another page

Microsoft Access tips: Subquery basics

Using subqueries in Microsoft Access. What subqueries are, and examples of using subqueries.

allenbrowne.com

 

[Garren.Shannon]

Use TOP with a subquery. Allen Browne has an example on another page

Microsoft Access tips: Subquery basics

Using subqueries in Microsoft Access. What subqueries are, and examples of using subqueries.

allenbrowne.com

Thanks Isladogs. I figured out what I was doing wrong and my poor logic lead me WAY down a bad path. I actually just needed to add a DLookup() to the query for pulling the dates from the SchoolYrDates table. Once I pulled my head out and pointed it in the right direction, I was able to use the query sum and then the TOP 3 command in the SQL statement.

SELECT TOP 3 Trip.[T-Date], Sum(Trip.MaxCount) AS SumOfMaxCount
FROM Trip, SchoolYrDates
GROUP BY Trip.[T-Date]
HAVING (((Trip.[T-Date]) Between DLookUp("CountStartDate","SchoolYrDates","SchoolYear = '" & GetCurrentYear() & "'") And DLookUp("FirstCount","SchoolYrDates","SchoolYear = '" & GetCurrentYear() & "'")))
ORDER BY Trip.[T-Date];

I don't know why I walked the crazy "Build my own custom functions" path when this was far simpler.

Anyway... thanks for the help all.

 

[isladogs]

Does that really work for the purpose you described earlier?
Did you look at the Top N per group link I suggested in post #4?

Anyway, it is best to avoid using domain functions such as DLookup in a query as it will be very slow.
In addition, you have a Cartesian join.
Instead create a query to list the dates from SchoolYrDates and join that to the dates in the Triptable

For more detail on optimising queries, see my article

Speed Comparison Tests 8

This article covers various methods of optimising queries and the use of the JET ShowPlan feature. It is the eighth in a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on your...

www.isladogs.co.uk

 

[Garren.Shannon]

Well, originally, it was the only way I could figure out how to compress the data initially. When this thing is running, we will have between 50 and 100 trips a day being added into the DB. Each trip is listed under type TF, A, F and M with the super majority being To/From. For reporting to the state, we need totals per day per trip type where TF+A+F are reported and M (miscellaneous) are excluded. Then we need to pull the day totals between date x and y for the reporting period and find the top 3. I could have set the date ranges as fixed dates since they have not changed in a few years but I am planning for the future so I built a table to store dates so they could be changed in the future without having to modify the code. The SchoolYrDates table is small and will grow slow...In 10 years, we will have 10 records. The DLookup is pulling the x and y dates needed to build the query. Then I summarize the headcount and find the top 3.

Can you explain what a Cartesian join is? Never heard that one before. It is a join related by date?

I will read through your article as well.

 

[isladogs]

A cartesian 'join' AKA no join.
For more details on this and other join types see my article

Query Join Types

This article explores the different types of select query joins available in Access and how these affect the query output in each case. In addition it discusses the causes of ambiguous join errors and how to solve these

www.isladogs.co.uk