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