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
|
|||
|
|||
separate column of dates into multiple columns in chronologic orde
I have imported an excel spreadsheet into an Access 2002 database table. (SP3)
I want to split one column of various dates..i.e., 2/10/2004, 6/15/2003, 8/30/2005 into 5 columns labeled Visit_1, Visit_2, Visit_3, etc. and have them in chronologic order. Example: Visit_1 Visit_2 Visit_3 6/15/2003 2/10/2004 8/30/2005 What is the best way to do this? |
#2
|
|||
|
|||
separate column of dates into multiple columns in chronologic orde
No, you don't. Having a repeating group like that is a sure sign that your
data hasn't been properly normalized. What you should have is another table that links to your first table, with one row for each visit. The old aphorism is "rows are cheap: columns are expensive" As to how to split, you could use the Split function to break the existing field into its component parts, and then use the CDate function to convert them to proper times. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Ginnyzz" wrote in message ... I have imported an excel spreadsheet into an Access 2002 database table. (SP3) I want to split one column of various dates..i.e., 2/10/2004, 6/15/2003, 8/30/2005 into 5 columns labeled Visit_1, Visit_2, Visit_3, etc. and have them in chronologic order. Example: Visit_1 Visit_2 Visit_3 6/15/2003 2/10/2004 8/30/2005 What is the best way to do this? |
#3
|
|||
|
|||
separate column of dates into multiple columns in chronologic orde
The best way to do this is not to do it this way. You should have a Visits
table tied to the customer, patient, or whatever is making visits using that entity's Primary Key. The approach you are taking will lead to nothing but headaches down the road. Let's say it is patients. You get one sick or hypocondriac patient, and your number of visits will be blown. Your first thought may be that you need a visit number. Not true. If you, like most of the rest of us, are trapped in linear time, then the earliest date would be visit 1. If it is possible to have multiple visits on the same day, then include the time of the visit. This, of course, does not apply to Billy Pilgrim. "Ginnyzz" wrote: I have imported an excel spreadsheet into an Access 2002 database table. (SP3) I want to split one column of various dates..i.e., 2/10/2004, 6/15/2003, 8/30/2005 into 5 columns labeled Visit_1, Visit_2, Visit_3, etc. and have them in chronologic order. Example: Visit_1 Visit_2 Visit_3 6/15/2003 2/10/2004 8/30/2005 What is the best way to do this? |
#4
|
|||
|
|||
separate column of dates into multiple columns in chronologic
thanks, I need to read up on normalizing data!
"Douglas J. Steele" wrote: No, you don't. Having a repeating group like that is a sure sign that your data hasn't been properly normalized. What you should have is another table that links to your first table, with one row for each visit. The old aphorism is "rows are cheap: columns are expensive" As to how to split, you could use the Split function to break the existing field into its component parts, and then use the CDate function to convert them to proper times. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Ginnyzz" wrote in message ... I have imported an excel spreadsheet into an Access 2002 database table. (SP3) I want to split one column of various dates..i.e., 2/10/2004, 6/15/2003, 8/30/2005 into 5 columns labeled Visit_1, Visit_2, Visit_3, etc. and have them in chronologic order. Example: Visit_1 Visit_2 Visit_3 6/15/2003 2/10/2004 8/30/2005 What is the best way to do this? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Compare multiple column of data and list out common and unique component in adj columns | kuansheng | Worksheet Functions | 13 | February 1st, 2006 10:49 PM |
Change a Column list into multiple rows & columns | angelface | Worksheet Functions | 3 | January 28th, 2006 01:23 AM |
Need to Improve Code Copying/Pasting Between Workbooks | David | General Discussion | 1 | January 6th, 2006 03:56 AM |
creating a bar graph | Johnfli | General Discussion | 0 | October 26th, 2005 08:16 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |