min use in query

[Guest]

The following sql in access

SELECT dbo_tblconsumer_dx_detail.dsm_code,
Min([dbo_tblconsumer_dx_header]![event_date]) AS MinimumDate
FROM dbo_tblconsumer_dx_header INNER JOIN dbo_tblconsumer_dx_detail ON
dbo_tblconsumer_dx_header.cons_dx_header_sysid =
dbo_tblconsumer_dx_detail.cons_dx_header_sysid
WHERE (((dbo_tblconsumer_dx_header.cons_entity_id)="55351.033") AND
((dbo_tblconsumer_dx_header.event_date) Between #1/1/2004# And #12/31/2004#))
GROUP BY dbo_tblconsumer_dx_detail.dsm_code
ORDER BY Min([dbo_tblconsumer_dx_header]![event_date]);

gives this resultset

dsm_code MinimumDate
312.9 2/16/2004
V71.09 10/5/2004

I only want the earlier date to show. I am testing this on just one
consumer but once I get it to work it will be used on a much larger
recordset. I need advice. Why isn't the query giving me the minimum date of
the two records.

 

[Guest]

It looks like your groupby's. You're getting the mindate for both dsm_codes

 

[MGFoster]

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

Something like this:

SELECT d.dsm_code,
h.event_date As MinDate

FROM dbo_tblconsumer_dx_header as h
INNER JOIN dbo_tblconsumer_dx_detail as d
ON h.cons_dx_header_sysid = d.cons_dx_header_sysid

WHERE h.cons_entity_id = "553151.033"
AND h.event_date = (SELECT MIN(event_date)
FROM dbo_tblconsumer_dx_header
WHERE cons_entity_id = h.cons_entity_id
AND event_date Between
#1/1/2004# And #12/31/2004#)

ORDER BY h.event_date


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

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

iQA/AwUBQgmJ14echKqOuFEgEQKpmwCg1laN+p+hlTwI1hSlpOcIrTcyrrQAoPxP
mf7mP0DH534Ge8aCxZNwWTDZ
=0qyW
-----END PGP SIGNATURE-----