Summing possibly blank Cross Tab in a query

[Steve]

I have 4 cross tab queries populating a master form as sub-forms. I
want to be able to total each sub form, then get a grand total on the
"master" form. Problem is that sometimes the cross tab data doesnt
exist and I then get an #error# on my sum
Any suggestions appreciated (if I have explained it clearly enough
Steve

 

[Douglas J. Steele]

You have to ensure that each of the crosstab queries has the same fields.
You can force fields to appear even if there's no data corresponding to that
field by explicitly listing the fields in the query's PIVOT statement:

PIVOT "Qtr " & DatePart("q", OrderDate) In ('Qtr 1', 'Qtr 2', 'Qtr 3', 'Qtr
4');

will ensure that there are fields for each of the four quarters, even if
there's no data for one (or more) of them.

 

[Steve]

You have to ensure that each of the crosstab queries has the same fields.
You can force fields to appear even if there's no data corresponding to that
field by explicitly listing the fields in the query's PIVOT statement:

PIVOT "Qtr " & DatePart("q", OrderDate) In ('Qtr 1', 'Qtr 2', 'Qtr 3', 'Qtr
4');

will ensure that there are fields for each of the four quarters, even if
there's no data for one (or more) of them.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)






- Show quoted text -

Thanks for the response and I have already set up that method for
consistency so I get the same headings.
My problem is that I can get a form for a particular period where
there are no transactions at all to report and rather than putting
zeros in the cross tab, it displays an empty cross tab and no
totallying is possible

 

[Douglas J. Steele]

You need to have a table of all possible dates that you can join to your
data table (using a LEFT JOIN) so that you get at least one row for each
period.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You have to ensure that each of the crosstab queries has the same fields.
You can force fields to appear even if there's no data corresponding to
that
field by explicitly listing the fields in the query's PIVOT statement:

PIVOT "Qtr " & DatePart("q", OrderDate) In ('Qtr 1', 'Qtr 2', 'Qtr 3',
'Qtr
4');

will ensure that there are fields for each of the four quarters, even if
there's no data for one (or more) of them.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)






- Show quoted text -

Thanks for the response and I have already set up that method for
consistency so I get the same headings.
My problem is that I can get a form for a particular period where
there are no transactions at all to report and rather than putting
zeros in the cross tab, it displays an empty cross tab and no
totallying is possible