Help with a Access 2003 query.

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi
Any help on the following would be most appreciated:

I have 6 fields in a table "Log", Fields "progress 1" to 5 and a "reference"
field. What I need to do is obtain the 5 most current dates from each
progress field and then be able to display the 25 dates with their
references in one query.

Thank you for any help
 
I don't think you can do it in one query. The following is one method that
may work with your current structure.

For best performance, create 5 queries and Save them (qProgTop5_n) and then
use a UNION query to get them all in one query.

SELECT TOP 5 Progress1 as ProgDate
, Reference
FROM Log
ORDER BY Progress1 DESC

SELECT TOP 5 Progress2 as ProgDate
, Reference
FROM Log
ORDER BY Progress2 DESC

Repeat for the other fields.

Now combine those with a union query
SELECT * FROM qProgTop5_1
UNION ALL
SELECT * FROM qProgTop5_2
UNION ALL
SELECT * FROM qProgTop5_3
UNION ALL
SELECT * FROM qProgTop5_4
UNION ALL
SELECT * FROM qProgTop5_5

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top