separate column of dates into multiple columns in chronologic orde

G

Ginnyzz

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?
 
D

Douglas J. Steele

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.
 
K

Klatuu

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.
 
G

Ginnyzz

thanks, I need to read up on normalizing data!

Douglas J. Steele said:
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.
Click to expand...
 
Top