tr*******@hotmail.com (Tricon) wrote in message news:<de************************@posting.google.co m>...
Is there a way to create a colum from values in a row?
For example, Let's say I have the following:
12345 Bigfoot 1 $1.00
12345 Bigfoot 2 $2.00
12345 Bigfoot 3 $3.00
Can I turn it into this:
ID NAME 1 2 3
12345 Bigfoot $1.00 $2.00 $3.00
Hi Tricon,
One way is to create a query using the "Cross-tab Query Wizard".
1) Select the ID and Name as "row headings".
2) Select the column containing the values 1,2,3... as the column
heading (I have called this column "Type").
3) The last column left is your dollar column (Which I have called
"Amount"). At the next step, apply the "Sum" function to this column
and it is done.
The underlying SQL for this looks like:
TRANSFORM Sum(tblBigFoot.Amount) AS [The Value]
SELECT tblBigFoot.ID, tblBigFoot.Name, Sum(tblBigFoot.Amount) AS
[Total Of Amount]
FROM tblBigFoot
GROUP BY tblBigFoot.ID, tblBigFoot.Name
PIVOT tblBigFoot.Type;
HTH
Regards
Stuart
