Need Query to Yield Results of Latest Record Input in Each Group

[Tyro]

I am trying to create a query that groups data, but limits the query results
to the most recent record for each group. I have tried using the Totals row
and then using Max and Last but I am not getting the correct results. Thx.

 

[Jeff Boyce]

It all starts with the data...

.... but you haven't described the table structure from which you are trying
to pull this data.

Please describe your data structure, and please post the SQL statement your
query is using.

By the way, "not getting the correct results" doesn't give us much to go
on... Can you describe how it isn't correct?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tyro]

Jeff, thx for your reply. Here is the SQL: "SELECT
dbo_MPOS_ALLOCATIONS.CONTRACT_DIVISION, dbo_MPOS_ALLOCATIONS.USER_DIVISION,
dbo_MPOS_ALLOCATIONS.FISCAL_YEAR, dbo_MPOS_ALLOCATIONS.FACILITY_CODE,
dbo_MPOS_ALLOCATIONS.FUND_CODE, dbo_MPOS_ALLOCATIONS.PROCESSING_DATE,
dbo_MPOS_ALLOCATIONS.ALLOCATION_AMOUNT
FROM dbo_MPOS_ALLOCATIONS
GROUP BY dbo_MPOS_ALLOCATIONS.CONTRACT_DIVISION,
dbo_MPOS_ALLOCATIONS.USER_DIVISION, dbo_MPOS_ALLOCATIONS.FISCAL_YEAR,
dbo_MPOS_ALLOCATIONS.FACILITY_CODE, dbo_MPOS_ALLOCATIONS.FUND_CODE,
dbo_MPOS_ALLOCATIONS.PROCESSING_DATE, dbo_MPOS_ALLOCATIONS.ALLOCATION_AMOUNT
HAVING (((dbo_MPOS_ALLOCATIONS.USER_DIVISION)="3") AND
((dbo_MPOS_ALLOCATIONS.FISCAL_YEAR)="2008") AND
((dbo_MPOS_ALLOCATIONS.FACILITY_CODE)="041") AND
((dbo_MPOS_ALLOCATIONS.FUND_CODE)="z9"));

The field for Processing_Date is a date field. When I run this query I get
all 6 records and I just want the one with the most recent Processing_Date.

 

[Ofer Cohen]

Look at this link about sub queries provided by Allen Browne

http://allenbrowne.com/subquery-01.html

 

[John Spencer]

You can use a subquery to do this. For your very specific query

SELECT CONTRACT_DIVISION
, USER_DIVISION
, FISCAL_YEAR
, FACILITY_CODE
, FUND_CODE
, PROCESSING_DATE
, ALLOCATION_AMOUNT

FROM dbo_MPOS_ALLOCATIONS

WHERE USER_DIVISION="3"
AND FISCAL_YEAR="2008"
AND FACILITY_CODE="041"
AND FUND_CODE="z9"
AND PROCESSING_DATE =
(SELECT MAX(PROCESSING_DATE)
FROM dbo_MPOS_ALLOCATIONS
WHERE USER_DIVISION="3" AND
FISCAL_YEAR="2008" AND
FACILITY_CODE="041" AND
FUND_CODE="z9")

If you had a more generic requirement, I would use a subquery in the from
clause Joined to the table

SELECT CONTRACT_DIVISION
, USER_DIVISION
, FISCAL_YEAR
, FACILITY_CODE
, FUND_CODE
, PROCESSING_DATE
, ALLOCATION_AMOUNT

FROM dbo_MPOS_ALLOCATIONS INNER JOIN
(SELECT Max(Processing_Date) as LastDate
, User_Division, Fiscal_Year, Facility_Code, Fund_Code
FROM dbo_MPOS_ALLOCATIONS
GROUP BY User_Division, Fiscal_Year, Facility_Code, Fund_Code
) as TEMP
ON dbo_MPOS_ALLOCATIONS.Processing_Date = Temp.LastDate
AND dbo_MPOS_ALLOCATIONS.User_Division = Temp.User_Division
AND dbo_MPOS_ALLOCATIONS.Fiscal_Year=Temp.Fiscal_Year
AND dbo_MPOS_ALLOCATIONS.Facility_Code = Temp.Facility_Code
AND dbo_MPOS_ALLOCATIONS.Fund_Code = Temp.Fund_Code

WHERE USER_DIVISION="3"
AND FISCAL_YEAR="2008"
AND FACILITY_CODE="041"
AND FUND_CODE="z9"

This approach would allow you to vary the criteria in where clause as you
need to get various data.
The advantage of approach one is that the query results can be updated - you
can change the data in the fields. The second approach will not allow you
to update the data in the query.



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

 

[Brad Bross]

I am very grateful to all your help ! Thx !!