Union Query

[Ste4en]

I am trying to convert some data which is in the format:
document no, number of sheets, Filename1, filename2, filename3

Where file 1,2 and 3 are the filenames of the sheets that form part of the document.

I would like to change it to:
document no, sheet1, filename1
document no, sheet2, filename2
document no, sheet3, filename3

Sometimes there is one filename or sheet sometimes there are 2 or 3.

I made a union query which is below. I would expect it to create 9 records, I would then delete the rows with no filename. I am not worried about the sheet numbering (which is often inconsistent) just the file names.

Please take a look at the union SQL query below.
Thanks
Steve

SELECT datafromtrans.*, DATAFROMTRANS.[File 01] AS Filename
FROM DATAFROMTRANS;

UNION SELECT DATAFROMTRANS.*, DATAFROMTRANS.[File 02] AS Filename
FROM DATAFROMTRANS;

UNION SELECT DATAFROMTRANS.*, DATAFROMTRANS.[File 03] AS Filename
FROM DATAFROMTRANS;