Query to pull most recent data

[lcoreilly]

Hi All, I have a table in my database that is structured by program ID
(abc123) and reporting period (1-10). Does anyone know how I would
run a query that would pull the most recent data for each program ID?
I need one record for each program, and if there is data for reporting
period 10, I would want that to show, if not -- show 9, etc.

Thanks in advance for any help you can provide.

 

[vanderghast]

The easiest way is to make two queries. The first one pump the max period
per programID:

SELECT programID, MAX(period) AS maxPeriod
FROM data
GROUP BY programID

saved as q1. Next, the second query bring that query and the original data,
join them trough their common field programID AND also data.period with
q1.maxPeriod. In the grid, bring the fields you want to have, from data.

The double join will restrict the records to those matching the period to
the maximum period value, for the given programID.


Vanderghast, Access MVP

 

[Allen Browne]

1. Create a query using this table.

2. Depress the Total button on the toolbar/ribbon (upper sigma icon.) Access
adds a Total row to the query design grid.

3. In the Total row under [program ID], accept Group By.

4. In the Total row under Period, choose Max.

5. Test: it should show the highest period number for each program id. Save.
Close the query.

6. Create a new query using both your original table and the query as input
'tables.' Join the ID from the original table to the MaxOfId in the query.
This limits the query to only the latest ID, but you can output as many
fields as you want.

If you want to do this in one query, use a subquery. Here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[lcoreilly]

Thanks so much... I figured it was something simple!