Pivot Table Does Not Show Data

[kr]

I have a pivot table in Excel based on data I have in Access. (It's easier to
see.)
The data is information that is categorized by different times of day.
In the PT,I have the times in the Row and Grouped by hour. Then a count in
values.

My issue - if I there is no data for a particular time of day, the Pivot
will not put a '0', it will just not show any information for that time. How
can I tell the Pivot if there is no info for this hour, show 0? I googled and
found info about Data Validation - if thats the answer, how do I use it?

Example
Hour
12 ---3
1 ----4
2-----5
3-----10
4-----11
*If there were no calls at 3, it would not show 3 at all. it would skip over
that row. How do I make it show '0' if when there is nothing there?

 

[GSnyder]

Usually in this circumstance, I'll force all of the times of day to show up
by appending "dummy" records to my table in Access. So you can have a table
with 24 records in it - one for each hour of the day and just append those
dummy records to your table. Then you'll be sure that each hour will show
up!

For a little more elegant (and challenging!) solution, you could also do it
by using a Union query in Access and Unioning the dummy record table into
your original table on the fly.

Now the problem becomes that you have an extra record for each hour. You
can fix that in your other formulas or analysis by just always subtracting 1
from the result. Alternatively, you could have a field in your database
(let's call it RowCount) with a 1 in it for each record that you have. For
each of your "Dummy" records, you'll have RowCount = 0. Then, when you
pivot, you can SUM RowCount instead of COUNTing the records and voila!
you've got a good count with all of the times represented.

Happy calculating!

 

[kr]

Thanks. this is great I will give it a try.