Criteria in reports

[KHogwood-Thompson]

I have a query that extracts information to produce a Balance Sheet. The
query looks at nominal codes and values year to date for each category in a
balance sheet. The category field is called "CATEGORY 3" and contains two
values "COST" and "DEPRN". I want a report that shows the Nominal code called
"DESC" and shows the related "COST" and "DEPRN" values for each, in the
following format:

DESC COST DEPRN TOTAL
------ ------ -------- -------
Land & Build xx xx xx
Plant & Mach xx xx xx
Cars xx xx xx

 

[Allen Browne]

Create a crosstab query, where you use the fields this way:
[DESC] Row Heading (group by)
[Category 3] Column Heading (group by)
[Amount] Value (sum, or perhaps First if there can be one only)

In report design view, you can easily add a Total box to the right. Its
Control Source will be:
=Nz([Cost],0) + Nz([Deprn],0)

For the totals at the bottom, add text boxes to the Report Footer section,
with Control Source like this:
=Sum([Cost])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[KHogwood-Thompson]

Thanks for this, I now know what a crosstab query is used for !!

One other question I have is how do I display the DEPRN values on the report
as positive figures rather than negative? I have tried changing the control
source to =-[DEPRN] but that does not seem to work and I get #Error displayed
when I run the report.
--
K Hogwood-Thompson

Create a crosstab query, where you use the fields this way:
[DESC] Row Heading (group by)
[Category 3] Column Heading (group by)
[Amount] Value (sum, or perhaps First if there can be one only)

In report design view, you can easily add a Total box to the right. Its
Control Source will be:
=Nz([Cost],0) + Nz([Deprn],0)

For the totals at the bottom, add text boxes to the Report Footer section,
with Control Source like this:
=Sum([Cost])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I have a query that extracts information to produce a Balance Sheet. The
query looks at nominal codes and values year to date for each category in
a
balance sheet. The category field is called "CATEGORY 3" and contains two
values "COST" and "DEPRN". I want a report that shows the Nominal code
called
"DESC" and shows the related "COST" and "DEPRN" values for each, in the
following format:

DESC COST DEPRN TOTAL
------ ------ -------- -------
Land & Build xx xx xx
Plant & Mach xx xx xx
Cars xx xx xx
---------------------------------------------------------------
Total xx xx xx

How can this be done in a report? Or do I have to write another/amend the
query?

 

[Allen Browne]

In report design view, change the Name property of the text box as well.

Access gets confused if the control has the same Name as a field, but it is
bound to something else.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news[email protected]...

 

[KHogwood-Thompson]

How strange, now it works fine - thanks very much!
--
K Hogwood-Thompson

In report design view, change the Name property of the text box as well.

Access gets confused if the control has the same Name as a field, but it is
bound to something else.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Thanks for this, I now know what a crosstab query is used for !!

One other question I have is how do I display the DEPRN values on the
report
as positive figures rather than negative? I have tried changing the
control
source to =-[DEPRN] but that does not seem to work and I get #Error
displayed
when I run the report.