Reversed Crosstab Query?

[Supermurray]

G’day all

I was wondering if there was a way to setup a Reversed Crosstab query . I am running a query which is pulling data from an import table. But the one thing is the data that I import is already setup like a Crosstab. Ex:

First Name, Last Name, Employee ID, Value 1, Value 2, Value 3,
Joe, Smith, 38689, 4, 2, 8,
Bob, Smith, 38957, 7, 3, 2,
Matt, Jones, 38745, 4, 7, 7,
Jill, Brown, 38467, 6, 4, 3,
Joan, Miller , 38058, 8, 9, 6,

I tried to setup multiple queries for each value then tried to pull it all into one query, but then I get all records multiplied 5 times.

Basicly I need the following results:
First Name, Last Name, Employee ID, Value, Number,
Joe, Smith, 38689, 1, 4,
Joe, Smith, 38689, 2, 2,
Joe, Smith, 38689, 3, 8,
Bob, Smith, 38957, 1, 7,
Bob, Smith, 38957, 2, 3,
Bob, Smith, 38957, 3, 2,
Matt, Jones, 38745, 1, 4,
Matt, Jones, 38745, 2, 7,
Matt, Jones, 38745, 3, 7,
Jill, Brown, 38467, 1, 6,
Jill, Brown, 38467, 2, 4,
Jill, Brown, 38467, 3, 3,

Anyone thing that will help, I will be very grateful,
Thanks in advance

Mur

 

[pbaldy]

Try a UNION query, which should return what you want.

 

[Supermurray]

I'm not 100% sure what you mean, I assume you mean in the SQL Query.

Here is the SQL Version:
SELECT SupID.SupName, Employee.Emp_Last_Name, Employee.Emp_First_Name, Base3.Date, Base3.StartTime AS Start, Base3.EndT, Base3.Oracle_ID, ([AUX3]+[AUX4]+[AUX5]+[AUX7]+[AUX8]) AS TAuxC, Base3.AUX3, Base3.AUX4, Base3.AUX5, Base3.AUX7, Base3.AUX8
FROM SupID INNER JOIN (Base3 INNER JOIN Employee ON Base3.Oracle_ID = Employee.Oracle_ID) ON SupID.SupID = Employee.Lead
WHERE (((([AUX3]+[AUX4]+[AUX5]+[AUX7]+[AUX8]))>0.9));

I have never actually done an UNION query before

 

[pbaldy]

Well, in your original example, the solution would look like:

SELECT First Name, Last Name, Employee ID, Value 1
FROM TableName
UNION ALL
SELECT First Name, Last Name, Employee ID, Value 2
FROM TableName
UNION ALL
SELECT First Name, Last Name, Employee ID, Value 3
FROM TableName

If your field names really have spaces, you'd have to bracket them.

 

[Ryko]

What pbaldy said.

Also, I wouldn't want to use names like Value or Number as my query output field names, as such names have/might have a specific meaning to MS Access.

 

[Jon K]

the data that I import is already setup like a Crosstab. Ex:

First Name, Last Name, Employee ID, Value 1, Value 2, Value 3,
Joe, Smith, 38689, 4, 2, 8,
..........................

Basicly I need the following results:
First Name, Last Name, Employee ID, Value, Number,
Joe, Smith, 38689, 1, 4,
Joe, Smith, 38689, 2, 2,
Joe, Smith, 38689, 3, 8,
.........................

Based on your data, I think the tricky part is the requirement of numbering the values as 1,2,3 in the results.


Paul - you're fast. You posted while I was editing my saying of having to use VBA to number the values.
.

 

[pbaldy]

I missed that Jon, but it can still be done with the union (I assume it's not a incremental number but which value field it was in the original table):

SELECT First Name, Last Name, Employee ID, 1 AS Value, Value1 AS Number
FROM TableName
UNION ALL
SELECT First Name, Last Name, Employee ID, 2 AS Value, Value2 AS Number
FROM TableName
UNION ALL
SELECT First Name, Last Name, Employee ID, 3 AS Value, Value3 AS Number
FROM TableName