If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dates in seperate columns but return latest date
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, |
#2
|
|||
|
|||
Normalize your data into records rather than repeating fields. Is this a
possibility? I wouldn't go any further without making changes. -- Duane Hookom MS Access MVP wrote in message ... 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, |
#3
|
|||
|
|||
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! -----Original Message----- Normalize your data into records rather than repeating fields. Is this a possibility? I wouldn't go any further without making changes. -- Duane Hookom MS Access MVP wrote in message ... 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, . |
#4
|
|||
|
|||
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. -- Duane Hookom MS Access MVP -- wrote in message ... 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! -----Original Message----- Normalize your data into records rather than repeating fields. Is this a possibility? I wouldn't go any further without making changes. -- Duane Hookom MS Access MVP wrote in message ... 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, . |
#5
|
|||
|
|||
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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Duane Hookom" wrote in message ... 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. -- Duane Hookom MS Access MVP -- wrote in message ... 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! -----Original Message----- Normalize your data into records rather than repeating fields. Is this a possibility? I wouldn't go any further without making changes. -- Duane Hookom MS Access MVP wrote in message ... 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, . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
QDE (Quick Date Entry) | Norman Harker | Worksheet Functions | 37 | September 5th, 2004 01:24 AM |
QDE (Quick Date Entry) | Norman Harker | General Discussion | 3 | September 3rd, 2004 08:00 AM |
more dates!!! | brigid | Running & Setting Up Queries | 6 | May 26th, 2004 10:59 AM |
Checking for a date falling between two columns of dates (an array, maybe??) | pollywog1961 | Worksheet Functions | 4 | October 25th, 2003 03:19 PM |