[AccessD] How to query to get result?

I like your solution. Works great. Thanks everyone for your ideas.

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

The joys of non-normalised data :-)

One way is  with a series of  queries:
(Aircode, so may need some tweaking)

1.  Normalise it with a Union Query (qryNormalised)

Select PID, "0" as MthNum,ProdMonth as Mth,[Type] as PType from myTable
Union
Select PID, "1" as MthNum,ProdMonth1 as Mth,Type1 as PType from myTable
Union
Select PID, "2" as MthNum,ProdMonth2 as Mth,Type2 as PType from myTable

2.  Now create get the maximum date for each PID (qryMaxDates)

Select Distinct PID, Max(Mth) as MaxMth from qryNormalised

3. Now join 1 and 2 to get the Column and Type for that PID/Date pair:

Select PID,MthNum, MaxMth,PType from
qryMaxDates, qryNormalised 
where qryMaxDates.PID = qryNormalised.PID
and qryMaxDate.MaxMth = qryNormalised.Mth


--
Stuart

On 24 Mar 2009 at 7:24, Kaup, Chester wrote:

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



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com