Distinct Last Order Date

[uncgis]

I need to pull only one record from access for each customer based on the last order date. I have tried to use a inner join in Select Query.

I have the following as a where statement:
(Select MAX (SPR_ACTIVITY_DATE) from dbo_SPR WHERE dbo_SPR.SPR_PIDM = SPR_PIDM)

But it pulls no records....can anyone help?


Thanks

 

[PHV]

Any chance you could post the whole SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Garyhou]

I found this on a search I just did for the same kind of problem. Hope it helps:

There's probably other ways, but this should work.

Create a query to gather the distinct equipment IDs and their most recent date:

SELECT equipment, Max(datefield) AS MaxOfdatefield
FROM Tablename
GROUP BY equipment;

Create another query that joins the first query with the table to get the corresponding Upgrade field:

SELECT Queryname.equipment, Queryname.MaxOfdatefield, Tablename.upgrade
FROM Queryname INNER JOIN Tablename ON (Queryname.MaxOfdatefield = Tablename.datefield) AND (Queryname.equipment = Tablename.equipment);