- Joined
- Apr 8, 2008
- Messages
- 1
- Reaction score
- 0
I need a query that will extract data from a field and add them to individual columns, the same as a crosstab, but instead of using a value, count, sum etc I want to see the data same as you would in a spreadsheet.
C1
C2
C3
C4
TOP
0
3
4
0
MIDDLE
2
3
5
0
BOTTOM
0
1
2
0
FLOOR
0
0
0
0
TRANSFORM Count(ItemLocation.TagNumber) AS CountOfTagNumber
SELECT RACKING.RACKID
FROM RACKING LEFT JOIN ItemLocation ON RACKING.RACKID = ItemLocation.RackID
WHERE ((([RACKING]![RACK])=1))
GROUP BY RACKING.RACKID, RACKING.LocationID
ORDER BY RACKING.LocationID
PIVOT RACKING.COLUMNID;
In the SQL code above, data is returned counting the number of items in the columns TOP, MIDDLE, BOTTOM and FLOOR locations. I want to be able to create a similar query to view the actual items. What do I need to change in the above SQL code or can it not be done? The TagNumber field contains the data required.
C1
C2
C3
C4
TOP
0
3
4
0
MIDDLE
2
3
5
0
BOTTOM
0
1
2
0
FLOOR
0
0
0
0
TRANSFORM Count(ItemLocation.TagNumber) AS CountOfTagNumber
SELECT RACKING.RACKID
FROM RACKING LEFT JOIN ItemLocation ON RACKING.RACKID = ItemLocation.RackID
WHERE ((([RACKING]![RACK])=1))
GROUP BY RACKING.RACKID, RACKING.LocationID
ORDER BY RACKING.LocationID
PIVOT RACKING.COLUMNID;
In the SQL code above, data is returned counting the number of items in the columns TOP, MIDDLE, BOTTOM and FLOOR locations. I want to be able to create a similar query to view the actual items. What do I need to change in the above SQL code or can it not be done? The TagNumber field contains the data required.