[AccessD] How to query to get result?

Good idea but I don't believe Access jet SQL supports the CASE WHEN structure.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Paul Hartland
Sent: Tuesday, March 24, 2009 7:54 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] How to query to get result?

Kaup,

Very quick off top of my head, try the following:


SELECT PID,
CASE WHEN ProdMonth>ProdMonth1 AND ProdMonth>ProdMonth2 THEN ProdMonth ElSE
CASE WHEN ProdMonth1>ProdMonth AND ProdMonth1>ProdMOnth2 THEN ProdMonth1
ELSE ProdMonth2 END END AS LatestDate,
CASE WHEN ProdMonth>ProdMonth1 AND ProdMonth>ProdMonth2 THEN Type ElSE CASE
WHEN ProdMonth1>ProdMonth AND ProdMonth1>ProdMOnth2 THEN Type1 ELSE Type2
END END AS LatestType
FROM [yourtablename]
GROUP BY PID, ProdMonth, ProdMonth1, Prodmonth2, [Type], Type1, Type2



Paul Hartland



2009/3/24 Kaup, Chester <Chester_Kaup at kindermorgan.com>

> I have a table with a unique identifier column three date columns and a
> fluid type associated with each date. I need to extract from the table the
> most recent of the three dates for each record and its associated fluid
> type. I considered some kind of complex if statement but surely there is a
> better way. Below is a sample record.
>
> PID                     Status ProdMonth Type ProdMonth1 Type1 ProdMonth2
> Type2
> 42415016970000      PA   4/1/1963  Oil   6/1/2003   CO2    7/1/2007 Water
>
> Chester Kaup
> Engineering Technician
> Kinder Morgan CO2 Company, LLP
> Office (432) 688-3797
> FAX (432) 688-3799
>
>
> No trees were killed in the sending of this message. However a large number
> of electrons were terribly inconvenienced.
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



-- 
Paul Hartland
paul.hartland at googlemail.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com