Find Max of date that also meets other criteria

[WP]

I am trying to write a query to find a record that has the
max date, but also meets several other criteria. It's
easy to find the max date that satisfies the criteria..I
use the query below for that:

' Works but unable to show serialnum due to group by
SELECT ModelID,max
(dateentered),machinename,coatingname,sizename
FROM tblmaindesign
WHERE
modelid=23 AND typeid=4 AND machinename="W2" and
coatingname="Chrome" AND sizename="STD"
GROUP BY
modelid, machinename,coatingname,sizename ;

However, if I want to show something like serialnum (I
Need to show it of course) that is unique for each record
I have a problem because of the GROUP BY clause in the
above query. I have come up with something that works but
it repeats the criteria in it so there must be a better
way of doing this. Here's how I have the query now:

SELECT ModelID,serialnum,
dateentered,machinename,coatingname,sizename
FROM tblmaindesign as M
WHERE
modelid=23 AND typeid=4 AND machinename="W2" and
coatingname="Chrome" AND sizename="STD" AND dateentered=
(select max(dateentered) from tblmaindesign as s where
s.modelid=M.modelid and typeid=4 and machinename="W2" and
coatingname="Chrome" and sizename="STD") ;

Any pointers on the best way to get the max date along
side something like a unique serialnum would be greatly
appreciated.

Thank You

 

[MGFoster]

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

Just use the columns that uniquely identify the record w/ the max date.


You could also use an INNER JOIN on a tabular subquery:

SELECT m.ModelID, m.serialnum,
D.MaxEntered, M.machinename, M.coatingname, M.sizename

FROM tblmaindesign as M
INNER JOIN
(SELECT modelid, serialnum, MAX(dateentered) As MaxEntered,
machinename, coatingname, sizename
FROM tblmaindesign
GROUP BY modelid, serialnum, machinename, coatingname, sizename )
AS D ON M.Modelid = D.Modelid AND M.serialnum = D.serialnum

WHERE
m.modelid=23
AND m.typeid=4
AND m.machinename="W2"
and m.coatingname="Chrome"
AND m.sizename="STD"

If you get more than you wanted, probably have to add column comparisons
in the JOIN's ON clause.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQd2e/YechKqOuFEgEQI1JgCg2031g4DQ7nqeBq8Y7UZP9Rc7jWEAoM43
GHlGS7tjbZB5a9Lwi9pDNYQK
=VkJE
-----END PGP SIGNATURE-----

 

[WP]

The only problem with the query you suggest is that it
returns more than one record, whereas my query will always
return only one record. I need to find only the most
recent that satisfies the criteria.

 

[WP]

I don't like to answer my own question, but here's a link
that shows exactly what I am trying to do.

http://www.mvps.org/access/queries/qry0020.htm