Dates in seperate columns but return latest date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 6 columns with Contact information and 6 date
columns. (ie. Contact One, Contact One Date, etc.)
How do I return the latest Contact and Date.

An example: (These are all seperate columns)

1st Contact - Bill Walsh 1st Contact Date - 1/11/2004 2nd
Contact - Joe Theisman 2nd Contact Date - 1/18/2004 3rd
Contact - Joe Montana 3rd Contact Date - 1/27/2004

How do I have it return Joe Montana 1/27/2004 in a query?

Thanks,
 
Normalize your data into records rather than repeating fields. Is this a
possibility? I wouldn't go any further without making changes.
 
Would you make a new table? The table that I have holds
these 6 columns and has the primary key on the Account
Number. I was trying to avoid having multiple Account
Numbers to go along with each contact. Could you explain
why you would do it this way?

Thank you for your advice! I appreciate it!
 
I would suggest:
tblAccounts
============
AcctID primary key
' other fields

tblAcctContacts
============
ContID autonumber primary key
AcctID long integer link to tblAccounts.AcctID
ContDate date contact date
ContFName text
ContLName
ContNotes memo
' other fields...

You can then get the most recent contact date for each account with a totals
query that groups by AcctID and finds the Max of ContDate.
 
A simple solution, if this is a one-time requirement, would be to create a
Union query that normalizes the data, and then use that normalized Union
query for further processing.

SELECT AcctID, 1 AS ContID,
ContactDate1 AS ContDate, Contact1FName AS ContFName...
FROM MyTable
UNION
SELECT AcctID, 2 AS ContID,
ContactDate2 AS ContDate, Contact2FName AS ContFName...
FROM MyTable
UNION
SELECT AcctID, 3 AS ContID,
ContactDate3 AS ContDate, Contact3FName AS ContFName...
FROM MyTable

etc.
 
Back
Top