Text to Columns in Access Query...

[DRANDON]

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.

 

[Joe4]

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:

Account1, CPT1, CPT2, CPT3

you would have something like this:

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.

 

[welshgasman]

I would have to ask why exactly? as another method might be better, if you can explain the final result required.

 

[Joe4]

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.

 

[xenou]

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").