group totals in a report

[Guest]

I have a report that includes the example table below:

I have a table set up as follows:

group Name: ID
Payroll expenses John Smith 1
Non Payroll expenses John Smith 2

The problem is that I would like the totals field to reflect the following:

Total Payroll Expenses
Total Non Payroll Expenses

Currently it is listing totals and if I change it to Total Payroll Expenses,
the same title appears under the non-payroll group.

Is their a way to label the payroll and non-payroll totals separatley.

I hope I have been clear in explaining this.

 

[Allen Browne]

1. Open the report in design view.

2. Right-click the text box bound to the group, and choose Properties.
Access opens the Properties box.

3. Change the Control Source of the text box (Data tab of Properties box)
from:
group
to:
="Total " & [group]

4. Change the Name (Other tab of Properties box) to (say):
txtTotalGroup

The actual name at step 4 is not important, as long as it is different from
the field name. (Access gets confused if a control has the same Name as a
field, but is bound to something different.)

 

[Guest]

THANKS!! that worked great. I have another question:
on My report i would like the group header to display Employee name for the
first group of data and Supplies and Expenses for the second group. However,
employee name is not in the table the field name for this data is
expenseaccountdesc. Will a caption expression allow me to display these two
groups the way I would like. If so, please advise. If not, what is the best
way to address this.

--
DB

1. Open the report in design view.

2. Right-click the text box bound to the group, and choose Properties.
Access opens the Properties box.

3. Change the Control Source of the text box (Data tab of Properties box)
from:
group
to:
="Total " & [group]

4. Change the Name (Other tab of Properties box) to (say):
txtTotalGroup

The actual name at step 4 is not important, as long as it is different from
the field name. (Access gets confused if a control has the same Name as a
field, but is bound to something different.)

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

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

I have a report that includes the example table below:

I have a table set up as follows:

group Name: ID
Payroll expenses John Smith 1
Non Payroll expenses John Smith 2

The problem is that I would like the totals field to reflect the
following:

Total Payroll Expenses
Total Non Payroll Expenses

Currently it is listing totals and if I change it to Total Payroll
Expenses,
the same title appears under the non-payroll group.

Is their a way to label the payroll and non-payroll totals separatley.

I hope I have been clear in explaining this.

 

[Allen Browne]

The new issue is that the field you need is not in the table that the report
draws its records from?

Create a query that contains both tables.
Double-click the line joining the 2 tables in the upper pane in query
design.
Access pops up a dialog offering 3 choices.
Choose the one that brings all records from your original report table.
Save the query, and close.
Open the report in design view, and set its RecordSource property (Data tab
of Properties box) to this new query.

 

[Guest]

Query help:

I need to write a report that breaks down the totals by month for a specific
category. for example



I have three tables, the first:

expense account expenseaccount desc group annualbudget
1 program director Payroll 10,000


Mbhpreftbl

expenseaccount ID
1 6
1 1
1 10 75800


Mastertbl


ID


the first table and the Mbhpreftbl table are linked by espenseaccount
the mbhpreftbl is linked to the master table by ID

I need to sum the individual totals by month for a specific category.

Please help

 

[Allen Browne]

In query design view, depress the Total button on the Toolbar (upper sigma
icon). Access adds a Total row to the query design grid.

You can now group by the field(s) you want to group by (such as the
category), and sum the field(s) you wish to total.

For further help, search for "Total query" in the Access help.

 

[Guest]

THANK YOU for all your help, you have been extremley helpful.....just 2 more
questions.

first, I tried to change the page margins, in page setup up on my report.
However, it will not change from 2.5. Am I missing something, is their
anothe way to change the left margin.

second, I need to calculate the grand some from report b to report A. my
expression does not seem to be working

=sum (report)!(BMC1)(grandtotal)+ (report)!(BMC2)

please advise

 

[Allen Browne]

A1: Margins
I assume you have changed the margin, but when you open the report later it
has reverted back to 2.5cm. If so, see:
Lost Printer Settings When Name AutoCorrect Is Enabled
at:
http://support.microsoft.com/?id=240826

A2: Totals
You cannot pass a value directly from one report to a completely different
one. (You can pass the total from a subreport back to the main report if
that is what you want to do.)

 

[Guest]

YOU ARE THE MAN!!!!!!
--
DB

A1: Margins
I assume you have changed the margin, but when you open the report later it
has reverted back to 2.5cm. If so, see:
Lost Printer Settings When Name AutoCorrect Is Enabled
at:
http://support.microsoft.com/?id=240826

A2: Totals
You cannot pass a value directly from one report to a completely different
one. (You can pass the total from a subreport back to the main report if
that is what you want to do.)