How to transfer the redundant data table

[Serghei]

There is a table with redundant data as columns.:

Table 1. Equipment
-------------------------------------------------
AU# | serial#1 | serial#2 | serial#3 | serial#4
1 001 002 003 004
..
..
..
..
12 1 2 3 4
....etc
-------------------------------------------------
this is how I receive the table.

I need the results look like
---------------
AU# | serial#
1 001
1 002
1 003
1 004
......
12 1
12 2
12 3
12 4
etc

The quickest solution I see is to break Table 1, into four
different tables and then join them based on AU#. I find
it time-consuming, not efficient, but it gives the
results. I was working on self-joins, but did not get the
results(perhaps did something wrong to the quiery.
Do you think it could be some ways to use SQL, ACCESS, VBA
to get the desired results?

Thank you very much,
Sergei

 

[John Vinson]

Table 1. Equipment

What you need here is what's called a "Normalizing Union Query". You
need the SQL window to create it but it's not hard to do using copy
and paste; it would be something like

SELECT [AU#], [Serial#1] AS [Serial#]
FROM [Equipment] WHERE [Serial#1] IS NOT NULL
UNION
SELECT [AU#], [Serial#2] AS [Serial#]
FROM [Equipment] WHERE [Serial#2] IS NOT NULL
UNION
SELECT [AU#], [Serial#3] AS [Serial#]
FROM [Equipment] WHERE [Serial#3] IS NOT NULL
UNION
SELECT [AU#], [Serial#4] AS [Serial#]
FROM [Equipment] WHERE [Serial#4] IS NOT NULL


Save this Query and base an Append query upon it.

 

[Serghei]

Thank you John!

-----Original Message-----

Table 1. Equipment

What you need here is what's called a "Normalizing Union Query". You
need the SQL window to create it but it's not hard to do using copy
and paste; it would be something like

SELECT [AU#], [Serial#1] AS [Serial#]
FROM [Equipment] WHERE [Serial#1] IS NOT NULL
UNION
SELECT [AU#], [Serial#2] AS [Serial#]
FROM [Equipment] WHERE [Serial#2] IS NOT NULL
UNION
SELECT [AU#], [Serial#3] AS [Serial#]
FROM [Equipment] WHERE [Serial#3] IS NOT NULL
UNION
SELECT [AU#], [Serial#4] AS [Serial#]
FROM [Equipment] WHERE [Serial#4] IS NOT NULL


Save this Query and base an Append query upon it.


.

 

[Serghei]

John, what about the table having the same format but 50
serial numbers.
Is it the only way with "Normalizing Union
Query"
Thank you,

Sergei

-----Original Message-----
Thank you John!

-----Original Message-----

Table 1. Equipment

What you need here is what's called a "Normalizing Union Query". You
need the SQL window to create it but it's not hard to do using copy
and paste; it would be something like

SELECT [AU#], [Serial#1] AS [Serial#]
FROM [Equipment] WHERE [Serial#1] IS NOT NULL
UNION
SELECT [AU#], [Serial#2] AS [Serial#]
FROM [Equipment] WHERE [Serial#2] IS NOT NULL
UNION
SELECT [AU#], [Serial#3] AS [Serial#]
FROM [Equipment] WHERE [Serial#3] IS NOT NULL
UNION
SELECT [AU#], [Serial#4] AS [Serial#]
FROM [Equipment] WHERE [Serial#4] IS NOT NULL


Save this Query and base an Append query upon it.


.

.

 

[John Vinson]

John, what about the table having the same format but 50
serial numbers.
Is it the only way with "Normalizing Union
Query"
Thank you,

Yep - just fifty SELECT clauses one after the other. You may be able
to use Perl or UltraEdit or code to construct the SQL if it gets too
tedious to copy and paste it all.

You *might* get a Query Too Complex error; if so, you'll need to split
the job in two, e.g. getting Serial#1 to Serial#25 and then Serial#26
to Serial#50.

As tedious as this seems, IMHO it's simpler than any of the
alternatives.