Find Records with Max Date

[MostlyH2O]

Hi Folks,

I have a query that joins 3 tables. One of the tables (SalaryData) has data
where there may be duplicate records with different dates. Of those
duplicate records, I want the query to show me only the one with the latest
date. The query below shows me all the records - including the duplicates
with older dates. I have tried various ways of using the MAX(DateField)
But can't get it to work. I need to keep the joins the way they are to
retain the proper recordset.

Can anyone help me correct the query below - so I only get the newest
records from the table "SalaryData"- where the field "SalaryData.BenchID" is
the same? (the date field is called "SalaryData.Dat")
SELECT SalaryData.BenchID, SalaryData.OrgID, SalaryData.Min, SalaryData.Mid,
SalaryData.Max, SalaryData.Avr, SalaryData.NumPos, SalaryData.OM,
SalaryData.FY, OrgJobs.OrgID, OrgJobs.BenchID, Benchmarks.CatID,
Benchmarks.BenchID, Benchmarks.Benchmark, Max(SalaryData.Dat) AS Expr1
FROM (Benchmarks INNER JOIN OrgJobs ON Benchmarks.BenchID = OrgJobs.BenchID)
LEFT JOIN SalaryData ON OrgJobs.BenchID = SalaryData.BenchID
WHERE (((OrgJobs.OrgID)=1083 Or (OrgJobs.OrgID) Is Null) AND
((Benchmarks.CatID)=40))
GROUP BY SalaryData.BenchID, SalaryData.OrgID, SalaryData.Min,
SalaryData.Mid, SalaryData.Max, SalaryData.Avr, SalaryData.NumPos,
SalaryData.OM, SalaryData.FY, OrgJobs.OrgID, OrgJobs.BenchID,
Benchmarks.CatID, Benchmarks.BenchID, Benchmarks.Benchmark;
Thanks very much for any help.

Jack Coletti
St. Petersburg, FL

[pietlinden]

Any reason you can't base a query on this one and then do the MAX stuff
there?

[MostlyH2O]

> Any reason you can't base a query on this one and then do the MAX stuff

there?

I'm not sure I understand your question. Are you suggesting that I make a
second query? And then loop through the dataset to find the latest records?
That would be very inefficient given the number of records. I will be
implimenting this on an ASP page which dynamically generates and populates a
multi-record / multi-page set of form choices based on this query. The
user may enter as many as 200 records - at 12 fields each. It also
populates the records with previous years' data. This is actually a
subquery of another query - but I didn't want to complicate my question.

I just need to correct the query below - so I only get the newest
records from the table "SalaryData"- where the field "SalaryData.BenchID" is
the same. (the date field is called "SalaryData.Dat")
SELECT SalaryData.BenchID, SalaryData.OrgID, SalaryData.Min, SalaryData.Mid,
SalaryData.Max, SalaryData.Avr, SalaryData.NumPos, SalaryData.OM,
SalaryData.FY, OrgJobs.OrgID, OrgJobs.BenchID, Benchmarks.CatID,
Benchmarks.BenchID, Benchmarks.Benchmark, Max(SalaryData.Dat) AS Expr1
FROM (Benchmarks INNER JOIN OrgJobs ON Benchmarks.BenchID = OrgJobs.BenchID)
LEFT JOIN SalaryData ON OrgJobs.BenchID = SalaryData.BenchID
WHERE (((OrgJobs.OrgID)=1083 Or (OrgJobs.OrgID) Is Null) AND
((Benchmarks.CatID)=40))
GROUP BY SalaryData.BenchID, SalaryData.OrgID, SalaryData.Min,
SalaryData.Mid, SalaryData.Max, SalaryData.Avr, SalaryData.NumPos,
SalaryData.OM, SalaryData.FY, OrgJobs.OrgID, OrgJobs.BenchID,
Benchmarks.CatID, Benchmarks.BenchID, Benchmarks.Benchmark;
Can it be that my request is simply impossible? I'd be gratefull to know
that too.

Thanks very much for any help.

Jack Coletti
St. Petersburg, FL

[bruce]

You're going to need two queries.

For example, if you are trying to use the Max(SalaryData.Dat) for each
SalaryData.BenchID then you'd use the following SQL for the first
query:

SELECT SalaryData.BenchID, Max(SalaryData.Dat) AS MaxOfDat
FROM SalaryData
GROUP BY SalaryData.BenchID;

Let's say we call this one qryMaxSalaryDat.

Then you'll need to create a second select query based on
qryMaxSalaryDat, SalaryData, OrgJobs, and Benchmarks. Writing the SQL
out for this from scratch will make my head hurt so I'm not going to do
that, but you'll need to join qryMaxSalaryDat and SalaryData by linking
MaxOfDat<->Dat and BenchID<->BenchID. Then add your Benchmark and
OrgJobs tables, linking them to SalaryData as you had done earlier and
using the same criteria. Adding qryMaxSalaryDat to the mix will limit
your result set to only those records in SalaryData with the latest
date per BenchID.

HTH,
Bruce