Apparently you don't think you have created a query?
1) Click on the new query button in the database window
2) Select Simple Query Wizard
3) Select [Name Table] and all the fields
4) Select Bank Activity table and the three currency fields
5) Click Next
6) Select Detail and click next
7) give you query a name and select the option to go to the design view.
8) to create a total of amounts, add a new colum in the grid like:
TotalTrans: Nz([Cash],0) + Nz([Scout Account],0) + Nz([Troop Paid])
Save this query to use as the record source for your report.
--
Duane Hookom
MS Access MVP
"Summing and Grouping data in a Report"
message
Thank you for your reply. I am confident that if you had a better
student,
your solution would work. I am not smart enough in Access to even know
where
to type in your solution.
The 'ID' field is the primary key of the 'Name Table'. It is linked to
the
'ID' field in the 'Bank Activity Table'. I already have established a
relationship between the two tables. In fact, I already am running
several
reports using data from both tables. My 'main' problem is, I do not
know
SQL. The reports I am running were developed using the 'wizards'.
I will completely understand if you want to bail on this one. Maybe I
should take an Access class at CompUSA or something.
:
Assuming the ID field is the primary key of the "Name Table" and
foreign
key
in the "Bank Activity Table", create a query with both tables and join
the
two ID fields. View->Totals in the query and add the appropriate
fields
from
the "Name Table". Create a new column
TotalTrans: Nz([Cash],0) + Nz([Scout Account],0) + Nz([Troop Paid])
Totals: Sum
In your report, set the first level of sorting and grouping on:
=[Last Name] & [First Name]
If you want to see Activity level detail, you need to remove the
View->Totals.
--
Duane Hookom
MS Access MVP
"Summing and Grouping data in a Report"
message
Thank you for your reply!
I would be happy to but I am not sure how to send you any data.
I have the following fields in the 'Bank Activity Table':
ID
Date
Activity
Bank
Withdrawal Check Number
Withdrawal Amount
Deposit Check Number
Cash - Currency Field
Scout Account - Currency Field
Troop Paid - Currency Field
Disposition
Description
I want to be able to create a new, calculated, field that sums
'Cash',
'Scout Account' and 'Troop Paid'. I will use this new field for
reporting
purposes.
I my other table my fields are:
ID
First Name
Last Name
Status
Classification
Association
In addition, I would like to group the totals I generate above by
'Last
Name' so that my report has one line item per Scout even if they
have
made
several payments.
:
You should be able to generate your values in a totals query. It
isn't
clear
whether you have 3 currency fields for Cash, Scout, and Troop or do
you
have
one currency field with a field for the source.
Some sample data from you would help us "do our good turn daily".
--
Duane Hookom (proud father of an Eagle Scout)
MS Access MVP
"Summing and Grouping data in a Report" <Summing and Grouping data
in
a
(e-mail address removed)> wrote in message
I am a Boy Scout Volunteer. I am treasurer of our Troop. I have
developed
a
database to track Troop finances. I have created two tables;
'Name
Table'
and 'Bank Activity Table'. The 'Name Table' has the Name and
Status
of
each
Scout. The 'Bank Activity table' has the financial transactions
associated
with each Scout.
When scouts pay the Troop for activities they have three options;
'Cash',
'Scout Account', or 'Troop Paid'. In order to maintain anonymity
for
those
Scouts who are funded by the Troop, I would like to generate a
report
with
a
calculated field 'Total Paid'. This value would be the sum of
'Cash',
'Scout
Account' and 'Troop Paid'. I would also like to group all
payments
for
each
Scout into a single value. (We frequently have outings where
several
payments are made for each Scout.
Is this possible?
