How to Disply Zero Value in a Crosstab Query

[magnumeng]

Hello all,

I am trying to build a data base for work and ran into a problem.

I have 10 different offices that have some of the same billets but not all that work in them.

Example:

Office	clerks	managers
accounting	2	2
shipping	2	0
admin	2	2
receiving	1	0

I have to display all the Offices on my report. Currently when I build my report if any billet has a ZERO value it will not display the office at all. How do I get my Query to display ZERO for the billets?

Chad

[HansV]

Welcome to Eileen's Lounge!

Create a new query in design view.
Add the table listing the (unique) offices to the query and also your crosstab query.
Join them on the Office field by dragging the field from the offices table to the corresponding field in the crosstab query.
Double-click the join line and select the option to include ALL records from the offices table and only related records form the crosstab query, then click OK.
Add the Office field from the offices table to the query grid.
Add a calculated field like this, substituting the correct names:

Clerks: Val(Nz([crosstabqueryname].[clerks],0))

Do the same for the managers field:

Managers: Val(Nz([crosstabqueryname].[managers],0))

This query should list all offices, with 0s for those that don't have clerks or managers.

[magnumeng]

HansV,

Thank you for your quick reply. But I am sure i am not doing it right. I put in the formula that you recommended but it still does not include ZERO Values...

TblMCCRoster-Contains all offices
qryfy11TOMO-contains all billets
qryMONumbers - Contains all the people filling TOMO billets
qryfy11TOME-contains all billets
qryMENumbers - Contains all the people filling TOME billets
qryfy11TONO-contains all billets
qryMONumbers - Contains all the people filling TONO billets
qryfy11TONE-contains all billets
qryMONumbers - Contains all the people filling TONE billets
qryCIVNumbers - Contains all the people filling Civ Billets (have not built the query for available billets)

all Officers have the first four(Qryfy11MOTO, qryMONumbers, Qryfy11METO, qryMENumbers).
The rest of the queries do not have someone working in one or more of the Offices. Which is making my report and/or query not show all my offices.

I am working on scrubbing database down so I can attach it here for better help.

Chad

[magnumeng]

Here is a scrubbed down version of what I am working with.

HansV,

I tried using the formula that you provided with no luck in QRYROLLUP.


Chad
Darn Computer are only as smart as the person hitting the keys. Man am I hurting...

[JohnH]

In the Design of qryRollup, double click each of the lines joining tblMCCRoster with each of the other queries, and change the Join type as shown below.
Repeat for all the joins.

JoinType.gif

[magnumeng]

HansV and JohnH,

Thank you very much... JohnH you solution solved my problem that I has hunted me for months.

Chad
Darn Computer are only as smart as the person hitting the keys. Man am I hurting...

[JohnH]

Double-click the join line and select the option to include ALL records from the offices table and only related records form the crosstab query, then click OK.

Hans had actually told you to change the join type in his post as well.