M
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
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