How do I add a calculated field to a report?

[Summing and Grouping data in a Report]

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?

 

[Duane Hookom]

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
[email protected]> wrote in message
news[email protected]...

 

[Summing and Grouping data in a Report]

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.

 

[Duane Hookom]

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"

 

[Summing and Grouping data in a Report]

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"

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.

 

[Duane Hookom]

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"

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
[email protected]> 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?

 

[Summing and Grouping data in a Report]

Actually, I have created a query. I use it as a source to run my report. I
have selected all the necessary fields in my query. I have opened the query
and gone to 'design view'. I added a column 'field' but do not know how to
populate the grid:
'Field' - What Field Name - 'TotalTran'?>
'Table' - Which Table?
'Total' - Defaults to 'Group by'
'Sort' - Leave Blank?
'Show' - I checked the box
'Criteria'
'or'

Where do I put TotalTrans: Nz([Cash],0) + Nz([Scout Account],0) + Nz([Troop
Paid])?

Thanks!

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"

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
[email protected]> 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?

 

[Duane Hookom]

Field: TotalTrans: Nz([Cash],0) + Nz([Scout Account],0) + Nz([Troop Paid])
Table:
Total: Sum
Sort:
Criteria:
"Summing and Grouping data in a Report"

Actually, I have created a query. I use it as a source to run my report.
I
have selected all the necessary fields in my query. I have opened the
query
and gone to 'design view'. I added a column 'field' but do not know how
to
populate the grid:
'Field' - What Field Name - 'TotalTran'?>
'Table' - Which Table?
'Total' - Defaults to 'Group by'
'Sort' - Leave Blank?
'Show' - I checked the box
'Criteria'
'or'

Where do I put TotalTrans: Nz([Cash],0) + Nz([Scout Account],0) +
Nz([Troop
Paid])?

Thanks!

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
[email protected]> 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?