A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dates in seperate columns but return latest date



 
 
Thread Tools Display Modes
  #1  
Old September 23rd, 2004, 10:39 PM
external usenet poster
 
Posts: n/a
Default 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  
Old September 24th, 2004, 03:56 AM
Duane Hookom
external usenet poster
 
Posts: n/a
Default

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  
Old September 24th, 2004, 03:59 PM
external usenet poster
 
Posts: n/a
Default

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  
Old September 24th, 2004, 04:18 PM
Duane Hookom
external usenet poster
 
Posts: n/a
Default

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  
Old September 24th, 2004, 05:26 PM
Douglas J. Steele
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 12:02 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.