[AccessD] Re: Greatest Date

Normalized is the best way.

BUT...

SELECT Title,
         IF(not isnull(SDate6),SDate6,
                 IF(NOT ISNULL(SDate5), SDate5,
                         IF(not isnull(SDate4),SDate4,
                                 IF(NOT ISNULL(SDate3), SDate3,
                                         IF(not isnull(SDate2),SDate2,
                                                 IF(NOT ISNULL(SDate1), 
SDate1,"Never Shown")
                                         )
                                 )
                         )
                 )
         ) AS LastDateShown
FROM YourTableName
ORDER BY Title

Should work.

Robert


At 12:01 PM 1/6/2005 -0600, you wrote:
>Date: Wed, 5 Jan 2005 10:03:22 -0800 (PST)
>From: Lonnie Johnson <prodevmg at yahoo.com>
>Subject: Re: [AccessD] Greatest Date
>To: Access Developers discussion and problem solving
>         <accessd at databaseadvisors.com>
>Message-ID: <20050105180322.74728.qmail at web20428.mail.yahoo.com>
>Content-Type: text/plain; charset=us-ascii
>
>Move those dates into a separate table so that you have a one to many 
>relationship and can better manipulate the data.
>
>Gowey Mike W <Mike.W.Gowey at doc.state.or.us> wrote:
>
>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.
>
>Any help would be greatly appreciated.
>
>Thanks in advance
>
>Mike Gowey MCP, A+, LME, NET+
>Team Leader - East Region
>Information Systems Unit