Zero Entries

[Guest]

Hi,

I have a number of count queries that total the number of items with a
specific worktype (eg Builderswork, Mechanical, Electrical). The count
queries are split by quarter ( of a year ). I then have a grand total query
that sums these queries to give me a grand total for each quarter. This works
fine except for 'Buildersworks'. The problem is that there are no records
with a 'Builderswork' worktype in quarter 1 or quarter 3, therefore the grand
total sum does not give a total, it appears blank. I need the query to take
these quarters as having 0 records and add them to the total.

Any help would be great,

Phil

 

[MGFoster]

Hi,

I have a number of count queries that total the number of items with a
specific worktype (eg Builderswork, Mechanical, Electrical). The count
queries are split by quarter ( of a year ). I then have a grand total query
that sums these queries to give me a grand total for each quarter. This works
fine except for 'Buildersworks'. The problem is that there are no records
with a 'Builderswork' worktype in quarter 1 or quarter 3, therefore the grand
total sum does not give a total, it appears blank. I need the query to take
these quarters as having 0 records and add them to the total.

Post your queries.

 

[Guest]

SELECT [Castles Worktype Totals Crosstab].Quarter, [Castles Worktype Totals
Crosstab].Builderswork, [Castles Worktype Totals Crosstab].Electrical,
[Castles Worktype Totals Crosstab].Main_Contractor_issue, [Castles Worktype
Totals Crosstab].Mechanical,
[Builderswork]+[Electrical]+[Main_Contractor_issue]+[Mechanical] AS [Grand
Total]
FROM [Castles Worktype Totals Crosstab];

Cheers,

Phil

 

[MGFoster]

Use the Nz() function in the calculation:

Nz(Builderswork,0)+Nz(Electrical,0)+Nz(Main_Contractor_issue,0)+Nz(Mechanical,0)
AS [Grand Total]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

SELECT [Castles Worktype Totals Crosstab].Quarter, [Castles Worktype Totals
Crosstab].Builderswork, [Castles Worktype Totals Crosstab].Electrical,
[Castles Worktype Totals Crosstab].Main_Contractor_issue, [Castles Worktype
Totals Crosstab].Mechanical,
[Builderswork]+[Electrical]+[Main_Contractor_issue]+[Mechanical] AS [Grand
Total]
FROM [Castles Worktype Totals Crosstab];

Cheers,

Phil

: