Manipulating data into multiple rows

[celestedeeva]

Hello all,

I have the following table:

UniqueID ID1 ID2 ID3 ID4
1 A B C
2 B C D E
3 A C
4 C D E

I would like to make the following table:

UniqueID ID
1 A
1 B
1 C
2 B
2 C
2 D
2 E
3 A
3 C
4 C
4 D
4 E

Thanks in advance.

Celeste.

 

[JoeAtWork]

Off the top of my head (untested).

Make 4 queries

qry_1
SELECT UniqueID, ID1 AS ID FROM table

qry_2
SELECT UniqueID, ID2 AS ID FROM table

qry_3
SELECT UniqueID, ID3 AS ID FROM table

qry_4
SELECT UniqueID, ID4 AS ID FROM table

Then write another that UNIONs the 4 above into one

SELECT * FROM qry_1
UNION
SELECT * FROM qry_2
UNION
SELECT * FROM qry_3
UNION
SELECT * FROM qry_4

 

[dhookom]

Do you actually want to "create" a table or can you just create a query? The SQL would be:

SELECT UniqueID, ID1 as ID
FROM [the following]
UNION ALL
SELECT UniqueID, ID2
FROM [the following]
UNION ALL
SELECT UniqueID, ID3
FROM [the following]
UNION ALL
SELECT UniqueID, ID4
FROM [the following];

You could use this union query as the source for a maketable query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[celestedeeva]

Thanks for this - I was doing something similar. I do in fact needto generate a table. Unfortunately I have about 40 ID fields and was hoping that I could do this upon import of said table.

So instead of importing the table table and then running a series of queries I am trying to "normalize" these fields into one common ID field upon import - perhaps a VB script on import is more suitable?

Thanks again for your imput and any further advice is much appreciated.

Celeste.

 

[dhookom]

I recommend importing it as it is. If the number of fields will vary, I would use vba to loop through the fields and either create the union query or run append queries.

If the number of fields is a constant, I would create and save the union query.

There isn't an 'import' functionality that will automatically normalize the data.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JerryKlmns]

I would suggest that you only have non empty ID#

SELECT UniqueID, ID2 AS ID FROM table WHERE ID2 Is Not Null;

for every ID#