How to create table using result of previous query as column name ?

[Mangala Teli]

I have table as Table1
p_cd dt qty price amt
001 12/13/2009 10 20 200
002 12/14/2009 5 10 50
001 12/15/2009 10 20 200
001 12/19/2009 8 20 160
i want result as
p_cd 12/13/2009 12/14/2009 12/15/2009 12/19/2009 totqty price amt
001 10 - 10 8 28 20 560
002 - 5 - - 5 10 50

[Mangala Teli]

How to create table using result of previous query as column name ?

[ChipR]

Have you created tables or stepped through recordsets with VBA before? Also, why would you want to do this? You seem to be taking correctly normalized data and storing it in a way that is not dynamic.

[ADezii]

I do believe that a Crosstab Query will produce the results you seek:

TRANSFORM Sum(Table1.qty) AS SumOfqty
SELECT Table1.p_cd, Sum(Table1.qty) AS [Total Quantity],
Sum(Table1.price) AS [Total Price], 
Sum(Table1.amt) AS [Total Amount]
FROM Table1
GROUP BY Table1.p_cd
PIVOT Format([dt],"Short Date");

OUTPUT:

p_cd    Total Quantity    Total Price    Total Amount    12/13/2009    12/14/2009    12/15/2009    12/19/2009
001            28          $60.00           560              10                           10          8
002             5          $10.00            50                            5    

[NeoPa]

ADezii has provided code to show how the data can be formatted as requested. Chip makes a good point though. De-normalising your data is not generally recommended. If you need to though, it is a simple step to convert SQL similar to what ADezii's posted into a Make Table query. Remember we warned against such a step though.