Need query to pull data from several fields into 1 field

[edluke]

I have a table set up like this:
[EmpNo] [Site1] [%1] [Site2] [%2] [Site3] [%3]...
123 56 50 29 45 11 5
456 47 30 56 30 29 40

I need create a query that pulls data from all the site fields into 1 field and all the % fields into on field
[EmpNo] [Site] [%]
123 56 50
123 29 45
123 11 5
456 47 30
456 56 30
456 29 40

My only thought was to run a seperate query for each Site/% combination and then append them to a table on the fly. Seems like alot of overhead though.

thanx for any suggestions

 

[dalchri]

What you are after is a UNION query:
[tt]
SELECT EmpNo, Site1 AS Site, [%1] AS [%]
FROM YourTable
UNION ALL
SELECT EmpNo, Site2 AS Site, [%2] AS [%]
FROM YourTable
UNION ALL
SELECT EmpNo, Site3 AS Site, [%3] AS [%]
FROM YourTable
...
[/tt]
This query effectively appends the data from multiple queries into one query.

 

[essnrv]

select cstr(EmpNo)+cstr(site)+cstr(%) As AliasName
from yourtablename

Hope this may help you