Matching Data in Mutiple Columns 1

[HansHamm]

I am working on an ADP project and having difficulties getting a SQL statement to work...I have 175 employees assigned to 375 stores..
I have 2 tables; 1 table has a column with store numbers in it.
The 2nd table has multiple columns with employee info. Each employee has between 1-4 stores. Each store being in a separate column. What I need to do is have a query where a new table is created with each store number in one column and the employee name assigned to that store in the next column.
This one has me irritated...It's got to be simpler than I am making it...

THANKS!!!
Hans

 

[PHV]

Each employee has between 1-4 stores. Each store being in a separate column
Have a look at Union query:
SELECT Store1 As StoreNum, EmplyeeName FROM tblEmployee WHERE Store1 Is Not Null
UNION SELECT Store2, EmplyeeName FROM tblEmployee WHERE Store2 Is Not Null
UNION SELECT Store3, EmplyeeName FROM tblEmployee WHERE Store3 Is Not Null
UNION SELECT Store4, EmplyeeName FROM tblEmployee WHERE Store4 Is Not Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HansHamm]

PH....Thanks alot for the help!!!