MAX DATE EXPRESSION

N

nazzoli

I have a table that has multiple dates in one line of data. I need to find
the max date of those. Where and how would I do this?

Here is what my table looks like

acct # date 1 date 2 date 3 date 4 date 5

how would I create the max date field to allow me to view the max date?
Thanks.
 
K

KARL DEWEY

You really should have your tables structured differently. The two queries
will find the maximum date of each account.
nazzoli_1 ---
SELECT nazzoli.[acct #], nazzoli.[date 1]
FROM nazzoli
UNION ALL SELECT nazzoli.[acct #], nazzoli.[date 2]
FROM nazzoli
UNION ALL SELECT nazzoli.[acct #], nazzoli.[date 3]
FROM nazzoli
UNION ALL SELECT nazzoli.[acct #], nazzoli.[date 4]
FROM nazzoli
UNION ALL SELECT nazzoli.[acct #], nazzoli.[date 5]
FROM nazzoli;

SELECT nazzoli_1.[acct #], Max(nazzoli_1.[date 1]) AS [MaxOfdate 1]
FROM nazzoli_1
GROUP BY nazzoli_1.[acct #];

Your structure should look like this --
AccountActionID - Autonumber - primary key
Account - I would avoid using spaces and special characters
Action - text
Action_Date - DateTime
Etc -
 
Top