Help with Max Date please ...

[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. This will be implimented on an ASP page.

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;

This is actually a sub-query which is nested in another query. Together
they give me the correct recordset - except that I get duplicate records
with older dates. I have tried to figure out a way to break up the query
into seperate queries, but then my ASP will need to loop exhaustively.

Thanks very much for any help.

Jack Coletti
St. Petersburg, FL

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How about this? Use a subquery to get the Max(Dat):

SELECT SD.BenchID,
SD.OrgID,
SD.Min,
SD.Mid,
SD.Max,
SD.Avr,
SD.NumPos,
SD.OM,
SD.FY,
OJ.OrgID,
OJ.BenchID,
BM.CatID,
BM.BenchID,
BM.Benchmark

FROM (Benchmarks As B
INNER JOIN OrgJobs AS OJ
ON BM.BenchID = OJ.BenchID)
LEFT JOIN SalaryData AS SD
ON OJ.BenchID = SD.BenchID

WHERE BM.CatID=40
AND (OJ.OrgID=1083 Or OJ.OrgID Is Null)

-- This subquery should get what you want

AND SD.Dat = (SELECT MAX(Dat) FROM SalaryData
WHERE BenchID = SD.BenchID)
GROUP BY SD.BenchID,
SD.OrgID,
SD.Min,
SD.Mid,
SD.Max,
SD.Avr,
SD.NumPos,
SD.OM,
SD.FY,
OJ.OrgID,
OJ.BenchID,
BM.CatID,
BM.BenchID,
BM.Benchmark;
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlV8ooechKqOuFEgEQI98wCcCJJQLGui8K0HY2QRE9Rq5n Y21OAAoILQ
WJxPvxRWPZh6R7J606QB2etc
=Q0p3
-----END PGP SIGNATURE-----
MostlyH2O wrote:

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. This will be implimented on an ASP page.

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;

This is actually a sub-query which is nested in another query. Together
they give me the correct recordset - except that I get duplicate records
with older dates. I have tried to figure out a way to break up the query
into seperate queries, but then my ASP will need to loop exhaustively.

[MostlyH2O]

I believe that's what I needed. Thanks VERY much for you answers to both my
posts.

clear skies,
Jack

"MGFoster" <[e-mail]> wrote in message
news:A0****************@newsread1.news.pas.earthli nk.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How about this? Use a subquery to get the Max(Dat):

SELECT SD.BenchID,
SD.OrgID,
SD.Min,
SD.Mid,
SD.Max,
SD.Avr,
SD.NumPos,
SD.OM,
SD.FY,
OJ.OrgID,
OJ.BenchID,
BM.CatID,
BM.BenchID,
BM.Benchmark

FROM (Benchmarks As B
INNER JOIN OrgJobs AS OJ
ON BM.BenchID = OJ.BenchID)
LEFT JOIN SalaryData AS SD
ON OJ.BenchID = SD.BenchID

WHERE BM.CatID=40
AND (OJ.OrgID=1083 Or OJ.OrgID Is Null)

-- This subquery should get what you want

AND SD.Dat = (SELECT MAX(Dat) FROM SalaryData
WHERE BenchID = SD.BenchID)
GROUP BY SD.BenchID,
SD.OrgID,
SD.Min,
SD.Mid,
SD.Max,
SD.Avr,
SD.NumPos,
SD.OM,
SD.FY,
OJ.OrgID,
OJ.BenchID,
BM.CatID,
BM.BenchID,
BM.Benchmark;
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlV8ooechKqOuFEgEQI98wCcCJJQLGui8K0HY2QRE9Rq5n Y21OAAoILQ
WJxPvxRWPZh6R7J606QB2etc
=Q0p3
-----END PGP SIGNATURE-----
MostlyH2O wrote:

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. This will be implimented on an ASP page.

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;

This is actually a sub-query which is nested in another query. Together
they give me the correct recordset - except that I get duplicate records
with older dates. I have tried to figure out a way to break up the query into seperate queries, but then my ASP will need to loop exhaustively.

[Van T. Dinh]

From your handle "MostlyH2O", I recall a story / joke:

2 university professors (one old and one young) were in earnest discussion
about their scientific endeavours. A young female student (and of course,
blond / tall / slim / beautiful / extraordinarily curvaceous and
indescribably sexy-looking - rated 11 out of 10, basically) walked by and
the young professor couldn't take his eyes of her and briefly lost track of
the discussion. The old professor got very annoyed and said:

"Scientifically, what you are looking at is very ordinary, mostly water
(more than 90%, anyway)."

The young professor (who is specialised in Computational Physics and skilled
in time-sharing his brain) retorted within microseconds:

"Yeah. However, I am observing / invetigating surface tension and the
effects of movements on surface tension."

--
Van T. Dinh
MVP (Access)


"MostlyH2O" <jm*@takeitouthrmp.net> wrote in message
news:qU******************@tornado.tampabay.rr.com. ..