DE-Concatenate but maintain relationship and record structure

[CompleteNewb]

This is worded very awkwardly, but I have examples below.

I've been at this for a while, and have half-succeeded with several
attempts, but nothing farther.

I have a column with many rows of values, some of which are values separated
by commas in the same cell. A second column has values related to the first
column, but some of these are also multiple values separated by commas.

I need to separate all cells that have multiple values separated by commas
into separate rows, while still maintaining the relationship in the other
column.

For example, I currently have this:

Dodge,Plymouth Trucks,Cars,Scooters
Buick Cars
GM,Ford Trucks,Cars

What I need is this:

Dodge Trucks
Dodge Cars
Dodge Scooters
Plymouth Trucks
Plymouth Cars
Plymouth Scooters
Buick Cars
GM Trucks
GM Cars
Ford Trucks
Ford Cars

So you see, I need to de-concatenate each column, but still have all of the
values in both still related to each other. And, as you see above, there
are variable numbers of values separated by commas (some cells only have one
value, some have 2, some 3, etc.).

I can do this in either Access or Excel or both; I have done most of my
experimenting using Excel's nice and easy Text to Columns utility, then
importing into Access, but in building queries in Access or formulas in
Excel I always either lose some data in one column or can't tie the values
in both to each other to achieve the above desired output.

Can anyone provide me with a function or series of steps I need to do to
accomplish this?

Any help would be greatly appreciated, and thanks for reading.

 

[Guest]

When you ask a question like this it would really help if you provided a
table and field names.... Let's assume tblMakesWheels with fields Makes and
Wheels.

One method would be to create two tables each with a single text field:
tblMakes.Make and tblWheels.Wheels. Place each unique single value from your
existing data into the new tables. Then create a query with this SQL:

SELECT tblMakes.Make, tblWheels.Wheels
FROM tblMakes, tblMakesWheels, tblWheels
WHERE (((tblMakesWheels.Make) Like "*" & [tblMakes].[Make] & "*") AND
((tblMakesWheels.Wheels) Like "*" & [tblWheels].[Wheels] & "*"));