[AccessD] Greatest Date

Ooops... Sorry for duplicating Gustavs suggestion, I replied before I
read his post...

mark

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 05 January 2005 19:01
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Greatest Date


Hi Mike

Not discussing the missing normalization of this structure ... you can
use an embedded union query:

<SQL>

  SELECT 
    ID, 
    Title,
    Max(PDate) AS PDateLast
  FROM
    [SELECT 
      ID, 
      Title,
      PDate1 as PDate
    FROM 
      tblMovie
    UNION ALL
    SELECT 
      ID, 
      Title,
      PDate2 as PDate
    FROM 
      tblMovie
    UNION ALL
    SELECT 
      ID, 
      Title,
      PDate3 as PDate
    FROM 
      tblMovie
    UNION ALL
    SELECT 
      ID, 
      Title,
      PDate4 as PDate
    FROM 
      tblMovie
    UNION ALL
    SELECT 
      ID, 
      Title,
      PDate5 as PDate
    FROM 
      tblMovie
    UNION ALL
    SELECT 
      ID, 
      Title,
      PDate6 as PDate
    FROM 
      tblMovie;] AS T
  GROUP BY
    ID,
    Title;

</SQL>

/gustav

>>> Mike.W.Gowey at doc.state.or.us 05-01-2005 18:57:13 >>>

I have a database that has a table that contains the title of a movie
and than has 6 date fields for entering a date that the movie was shown.
I would like to be able to extract the title of the movie and only show
the date field with the greatest date.

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

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.827 / Virus Database: 564 - Release Date: 03/01/2005
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.827 / Virus Database: 564 - Release Date: 03/01/2005