Create Another View of an Existing Table Using Queries

[MarrowGenx]

I have a table that I would like in another format and I believe that it can be done with a query, I just dont know how to do it. Here is the information that I have:

UniqueID ObjectName1 ObjectName2 ObjectName3
1 ABC ATR BPI
2 ITO TRJ IPB
3 PPP WER CFR

Now I would like to convert this table's information into the following format:

UniqueID ObjectName
1 ABC
1 ATR
1 BPI
2 ITO
2 TRJ
2 IPB
3 PPP
3 WER
3 CFR

Now I would not have too much of a problem with this if I knew that the number of ObjectName's in the original table (top one) were constant (i.e. always ObjectName1 & ObjectName2 & ObjectName3). In this query, I need the number of ObjectName's in the original table (top one) to not be a issue when attempting to get the results (bottom one).

Please let me know if you have any suggestions. I appreciate your time.

Marrow

 

[dhookom]

You can use a UNION query:
SELECT UniqueID, ObjectName1 as ObjectName
FROM table
UNION ALL
SELECT UniqueID, ObjectName2
FROM table
UNION ALL
SELECT UniqueID, ObjectName3
FROM table;

You may need to build the SQL of this query in code that opens the first table and loops through its fields to build the sql to apply to your union query.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[RickSpr]

Implicit in what Duane is saying in his last line is that you can't write an SQL query which references an unknown or variable number of columns ("I need the number of [columns]...to not be a issue...").

The original table is not normalized. The result set from the query you want, if it could be written, would be the properly normalized form of the original table. Is that the point--are you trying to normalize a table produced externally, say as a spreadsheet?

If so, I'd recommend just doing the whole process in VBA code, reading the original table and outputting the normalized table. Then the rest of your database should use the normalized table.

If the original table is actually one you have design control over, you need to normalize it permanently, instead of trying to do an ad hoc normalization with a query.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein