[AccessD] How to query to get result?

Bugger, sorry was using SQL Server at the time....Should be ok with the IIF
statements though, should give you the result you are after.

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

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