query to display columns as rows

  • Thread starter Thread starter wardusenet
  • Start date Start date
W

wardusenet

Hello,

I have the following (unnormalized, i believe) table:

tblProducts
fldProductID
fldProductName
fldDocFileNameEnglish
fldDocFileNameFrench
fldDocFileNameSpanish
... (for about 10 languages)

Now i have a couple of records similar to this:
EngineA, UserManualA_EN.pdf, UserManualA_FR.pdf, UserManualA_SP.pdf,
....
EngineB, UserManualB_EN.pdf, <blank>, UserManualB_SP.pdf, ...
EngineC, <blank>, <blank>, UserManualC_SP.pdf, ...
......

Now I would like to create a query that gives me two columns as
follows:
EngineA UserManualA_EN.pdf
EngineA UserManualA_FR.pdf
EngineA UserManualA_SP.pdf
EngineB UserManualB_EN.pdf
EngineB UserManualB_SP.pdf
EngineC UsermanualC_SP.pdf

But how (is this even possible?)
thanks
Ward
 
You should be able to use a union query...

SELECT fldProductID,fldProductName, fldDocFileNameEnglish as DocName,
"English" as Language
FROM tblProducts
UNION ALL

SELECT fldProductID,fldProductName, fldDocFileNameFrench, "French"
FROM tblProducts
UNION ALL

SELECT fldProductID,fldProductName, fldDocFileNameSpanish, "Spanish"
FROM tblProducts
--etc--
 
Hello Duane,

Thanks, just what I needed! I got it working now.

(I just didn't get what the " "English" as Language " in the first
SELECT statement is supposed to do, so I threw it out)

Ward
 
Oh... now I get it,
the " "English" as Language " adds an extra column showing the language
(which is indeed necessary for my purpose).

Stupid me :-)
 
Back
Top