Table rows to columns

[Rhino373]

I want to take a table that looks like this



Account Date Amount
0000 01/11 5.00
0000 01/11 5.00
0000 02/11 10.00
1111 02/11 2.00
2222 03/11 6.00

and make it into a table that looks like this

Account 1/11 2/11 3/11
0000 10.00 10.00
1111 2.00
2222 3.00
(assume the 2.00 entry is under 02/11 and the 3.00 entry is under 03/11)

I understand it's just a pivot table but I need to match it up with another query and keep it in this format whereas a pivot table is really just a view. I can't match it up with another query before because when when I try to display it as a pivot table it tells me there is too much data being returned. I am using Access 2003

Anyway to create a table where the column names are based on table data then take the information from that table?

[TheShabz]

I'm pretty sure a crosstab query would work for you. http://www.databasedev.co.uk/crosstab_queries.html

[chrisdavis]

Hi and thank you. Well I don't have a "value" field. I need the actual values of "WC", "AB" in the new cell and not just a count.

[chrisdavis]

Hi Rhino. I'm not quite understanding what I am looking for in your post??

You said another table?

[fcp]

Union Query try that!

[boblarson]

Union Query try that!

Crosstab, NOT Union Query. A Union query would not even be logical to set up for this.