Need help formatting Data.

[Roman]

I have data that looks like the example below:
fistname, last name, multiple rows, each with different data
RON SMITH A
RON SMITH B
RON SMITH C
RON SMITH D
RON SMITH H


My merge program reads data like the sample below:
First name. last name, one row, but multiple cells, each with different data.

RON SMITH A B C D H

Can someone tell me the best way to convert my existing data into the format
above?

Any help is much appreciated.

Thanks
R

 

[KARL DEWEY]

Try a crosstab query like this --
TRANSFORM First([MI]) AS FirstOfMI
SELECT FirstName, LastName
FROM YourTable
PIVOT MI;

 

[Dale Fye]

If Karl's answer doesn't do what you want, then chances are that what you
are already getting looks like:

RON SMITH A B C D H

And you want it to look like:

If that is the case, then what you need is frequently called a normalizing
query. You do this by creating a union query to that looks like:

SELECT FirstName, LastName, Field1 as [NewFieldName]
FROM yourTable WHERE Field1 IS NOT NULL
UNION ALL
SELECT FirstName, LastName, Field2
FROM yourTable WHERE Field2 IS NOT NULL
UNION ALL
SELECT FirstName, LastName, Field3
FROM yourTable WHERE Field3 IS NOT NULL
UNION ALL
SELECT FirstName, LastName, Field4
FROM yourTable WHERE Field4 IS NOT NULL
UNION ALL
SELECT FirstName, LastName, Field5
FROM yourTable WHERE Field5 IS NOT NULL

You will need to replace Field1-Field5 with the names of the fields where
the data A, B, C, D, H are located.

HTH
Dale