Rearrange Table Records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Al - 03 Oct 2007 16:40 GMT

Hello,

Hope someone can give advice on how to do this. I have inherited a database
with a large table in it that has 5 fields; field 1 is text, fields 2,3,4 and
5 are integer values. How do I rearrange this to form a new table that has
only two fields in it; the existing field 1 and a new second field which has
all the individual values previously held in fields 2,3,4 and 5?

So from this:-

Field1 Field2 Field3 Field4 Field5
A 2 22 44 57
B 45 12 3 8

To this:-

Field1 Field2
A 2
A 22
A 44
A 57
B 45
B 12 etc

Thanks

Reply to this Message

John Spencer - 03 Oct 2007 16:53 GMT

UNION query is the answer to getting the data organized as you wish. Then
if you want to permanently save the changed data you can use an append or
make table query.

SELECT Field1, Field2
FROM TheTable
UNION ALL
SELECT Field1, Field3
FROM TheTable
UNION ALL
SELECT Field1, Field4
FROM TheTable
UNION ALL
SELECT Field1, Field5
FROM TheTable

Union queries cannot be built using the Design View , but must be built in
the SQL view.
You can build the first segment of the query in design view,
switch to SQL view,
add UNION ALL (or just UNION if you want to remove duplicate records),
copy the first segment,
paste it after the UNION ALL and modify it (changer field2 to field3) etc.

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Hello,
>
[quoted text clipped - 24 lines]
>
> Thanks

Reply to this Message