Question Pivot Table Formatting Help

[Sandpiper]

Hi
If anyone can help, i'd be grateful.
I'm trying to format a pivot table in an access query, in the same way in which excel automatically creates the table....but really struggling. Think i've been trying for too long.

I have the following fields

Item, Date, Invoice Qty, Total Amount, Material GP

Items are my row headings, Dates are my column headings and Inv Qty, Total Amt and Materal GP provide the detail.

In Access, The sum of the detail fields are showing as Column headings
In Excel, the sum of the detail felds are showing as Row headings which is what i'm after. Can anyone please tell me how to move them, as i've tried all sorts, and nothing so far has worked.


Little Example (what I get in Access)

Jan Feb Total
Inv Qty Total Amt Inv Qty Total Amt Inv Qty Total Amt
Item 1 5 3 2 7 7 10
Itme 2 3 6 4 2 7 8


(what I want, and will get in Excel)

Jan Feb Total
Item 1 Inv Qty 5 2 7
Total Amt 3 7 10
Item 2 Inv Qty 3 4 7
Total Amt 6 2 8

I hope that makes sense.
Thanks

 

[Alisa]

Why not use the crosstab query wizard?

 

[Sandpiper]

Thanks for the response, but a cross tab, only allows me one value option and one Column heading.

Unless I'm doing something wrong.

I need 4 Value options. I only showed 2 in my example, but in reality there are 4.

Am I getting this completely wrong?
thanks

 

[Alisa]

Thanks for the response, but a cross tab, only allows me one value option and one Column heading.

Unless I'm doing something wrong.

I need 4 Value options. I only showed 2 in my example, but in reality there are 4.

Am I getting this completely wrong?
thanks

Yes, with a crosstab, you can only pick one thing for the value and one thing for the column heading. You can pick a bunch of things for row heading though. If you want a bunch of values, you are going to have to concatenate them and set that as the value, like ValueA & ValueB & ValueC etc. I don't think that is really what you are trying to do though. I would suggest using the wizard so you can get a feel for how it works - do the basic case first. Then you can try to make it more complicated.

 

[Sandpiper]

Oh my goodness, I think i'm out of my depth here.
Is it easy to concatenate my values? I understand how to concatenate text values, but not how this would work with this data.
Sorry to be a pain, but can you explain how Iset up the data and whic fields I then select

thanks

 

[Alisa]

Just how I said: ValueA & ValueB & ValueC
So if you had a field named myDate and a field named myAmount, you would write myDate & ", " & myAmount. You might need to format the values too.

 

[Sandpiper]

Thanks for your responses, but I don't think using the crosstab is going to work fo what I need this for.

Can anyone offer any other suggestions for formatting the pivot table?

Much appreciated

Thanks
Jo

 

[Alisa]

A crosstab in Access = a pivot in Excel, there isn't some other way to do it in Access. Why do you think a crosstab won't work for what you are trying to do? Maybe if you post your crosstab SQL so far, someone can help you refine it so that it will work.

 

[Stacey]

Sorry to keep going back to the crosstab, but I use a cross tab query and use the Group By to produce other values. For example, I pick the field to use for Row, Column, Value on the wizard, but then modify the query. I drop the additional fields into the query, choose Row Heading as the Crosstab choice, then pick Sum, Count, etc from the Total field. This has worked well for me.