Text to Columns in Access Query...

DRANDON

Active Member
Joined
Jun 30, 2006
Messages
268
Hello everyone. This may not be possible, but here goes.. In an Access table I have an account number in one column and an assigned CPT Code adjacent. One account number can have multiple CPT Codes (all different). I need to find a way to have only one account number (no duplicates) in the first column and if that account number has two different CPTs then have one in the second column and the other in the next column over.

So to start, all account numbers are in the first column with duplicates if that account has more than one CPT Code. I need to find a way to stretch the CPT's out to the right rather than listed in the same column. I hope this makes sense.

Thank you.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
The issue with that is you need to pre-define all the Access columns (fields) in a Table before you can populate them.
Since you do not know how many fields you will need, that may not be an easy task.

But actually, the design you are talking about in Access is actually not normalized (and would be considered "bad design"), and would make simple tasks much harder.

In a normalized table in Access, you would have one record (row) for each CPT code (not multiple fields for it).

So instead of something like this:
Code:
Account1, CPT1, CPT2, CPT3
you would have something like this:
Code:
Account 1   CPT1
Account 1   CPT2
Account 1   CPT3

I would recommend either using VBA with Dataset to split out the data, or split it out in Excel and re-import it into Access.
 
Upvote 0
I would have to ask why exactly? as another method might be better, if you can explain the final result required.
 
Upvote 0
The reason why is if you do not design your tables in a normalized fashion, it can make what should be simple tasks very complicated.

I had a situation very similar to yours where I experienced this. Let's say that you have 20 CPT fields going across each row. Now, suppose that your boss asks you to return all records that have a certain CPT code. In that case, you would need to search across 20 different fields!!! That is 20 different criteria connected with ORs! What a nightmare.

However, in the normalized model I proposed, you only need to search one single field.

It is also more scalable. In a well-designed database, you should seldom have to edit your table structures. In the original model, let's say that you had 10 CPT fields originally. Somewhere down the road, 10 isn't enough and you need to add more. So, you would then need to update the table structure, then all queries, forms, and reports that show those fields. Once again, a nightmare! However, with the normalized model, you do not. It is simply adding more records to the database and not having to alter your other objects.
 
Upvote 0
Note that there is no "text to columns" feature in MSAccess. This could probably be done in SQL easily enough but would need know specifically how to distinguish account numbers and CPT codes (for instance, "account number is first 10 digits and CPT code is last three digits" or "account number is everything before a hyphen and CPT code is everything after a hyphen").
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top