Pivot Table truncates values

[RiderJon]

Hello all,

Please help. I know this isn't the right page to post my question. But I did try posting in MS Forms without a single respose.

Hello,

I am trying to create a pivot chart with "Months as the x-axis, and count at the y-axis. (new to chart stuff - so bear with me)

I have the pivot table set-up fine except that it truncates the x-axis if data are not present.

1)
Meaning if the count of ppl for January is 0, the x-axis begins with Feburary. Any way I can force it to start from January?


I think the problem lies with my pivot table.

Right now it is doing:
Project1 Project2
Februrary 1 5
April 4 7


I want:
Project1 Project2
January
Februrary 1 5
March
April 4 7

The reason is that when drawing a pivot chart based on it, the x-axis starts off with February rather than January.

2)
Also, how do you change the "SOURCE DATA" of a pivot table i.e. instead of reading from "tbl1" make it read from "tbl2". (In my case, tbl1 and tbl2 have the same fields.


Thank you.





RiderJon
"I might have created ctrl+alt+del,
But Bill made it famous" - Dr. Dave

 

[Steve101]

Use the Column Headings property associated with the Query definition; If you set this to something like January;February;March;April.... it will ensure that months with no data will be presented in the columns, and that the columns will appear in the correct date based order.

Your column names in this property must however be exactly the same values as they are in the data or the data columns will not appear.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[RiderJon]

Steve,

Thank you for the feedback. Can you please provide me with more details? am new to using this function in ACCESS.

How do I get to the "column headings" property?

RiderJon
"I might have created ctrl+alt+del,
But Bill made it famous" - Dr. Dave

 

[Steve101]

RiderJon,

Open the query in the query designer, then select View, Properties from the menu. Make sure that you are viewing the Query properties; this is labelled at the top of the properies dialogue box which appears. If you are viewing Field List properties or Field Properties, then click anywhere in the grey area next to (but not in!) a table and you should see the field list properties.

One of the FieldList properties you will see if you have a CrossTab query type, is Column Headings (its the second property in the list). Its to the right of this property in the white space provided, that you enter the list of column headings.

Hope this clarifies it,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[RiderJon]

Steve,

Actually, I was looking for it in the chart window. I do not have a cross-tab query.

Anyway, if you right click on the columns you want, there is a "field settings". If you go there, there's a check box for displaying all values. But you did give me with some direction. Thank you

RiderJon
"I might have created ctrl+alt+del,
But Bill made it famous" - Dr. Dave