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?

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?

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?

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?