reports off a query

[Michael and Fayann]

Simple scenario.

Developed a program to count currency (money). Two different tables, one
table for coins and paper money and another table for checks. A
relationship exists between the two (1 to many). Made a query using all
fields from both tables with a criteria being record number. Made a form
form from the first table of coins and paper and a subform for checks
received. Calculations work fine in the form.

The problem:

I want to develop a report off of the query. All of the currency works fine
but the checks written don't. If I have four checks, I get eight reports
with one check listed on each report and I get two reports printed for each
person. I have all of the information in the page header, nothing in any
other area. How do I fix the problem of getting the checks to list on the
report?

Thanks.

 

[Larry Linson]

Firstly, I don't understand your table structure... I just don't understand
the one table for money and another for checks, joined one-to-many?
Secondly, you're going to have to give more detail on your report, too. I
fear there just isn't enough information here for anyone to give you a
useful answer.

Larry Linson
Microsoft Access MVP

 

[Michael and Fayann]

The table structure is that one table has all of the currency such as
pennies, nichels, etc. The reason for the second table is that the checks
are listed individually with name and the amount of the check. Are you
thinking that the checks could be included on the first table?

The relationship is because I have an organizationID (the group that turned
the money in) and when I print the report, I want the checks and the
currency to be together.

Thanks for your help.

Mike

 

[Duane Hookom]

I think what Larry might be hinting at is putting all the money in one
table. When he stated he didn't understand your table structure, this was a
request for you to reply with your table and field names.

 

[Larry Linson]

Yes... and having the currency and check tables "joined one-to-many" was a
puzzler, as well. I would expect each of them to be joined, perhaps, to some
other table, but not to each other.

Larry Linson
Microsoft Access MVP

 

[tina]

i'm picturing something like this: you receive money bags
from various organizations. you have a table with one
record containing the organization's name and the number
of each type of coin and currency received in each money
bag. then you have a second table to record the multiple
checks received in each organization's money bag. about
right?

if so, and you've linked the one-to-many tables in a
query, then try a report like this:

put all the controls bound to "fields containing data
about the checks" in the report detail section. the
controls bound to "organization and coin" fields can go in
a group header; or you can put them in the detail section
and change the HideDuplicates property to Yes (so that
data isn't repeated over and over with each check listed).

hth

 

[Michael and Fayann]

I guess what I am thinking I need to do is to start over, have three tables,
1 table for general information such as the organzation name and date the
money was collected, a 2nd table with the currency of coins and paper and a
3rd table for checks and then have the 2nd and 3rd table linked to the 1st
table? Correct?

Mike

 

[Michael and Fayann]

I guess what I am thinking I need to do is to start over, have three tables,
1 table for general information such as the organzation name and date the
money was collected, a 2nd table with the currency of coins and paper and a
3rd table for checks and then have the 2nd and 3rd table linked to the 1st
table? Correct?

Mike

 

[Larry Linson]

It's not clear to me why you need separate tables for the money and checks.
That's not to say you _don't_ need that for some specific reason, but
without more information about what you are trying to accomplish, we
couldn't really say.

Larry Linson
Microsoft Access MVP

 

[Michael and Fayann]

What started out as a simple projct has turned in to a you know what! I'll
get back to you tomorrow evening after I've had a chance to digest this. I
guess I not giving you the information you need. Sorry!! I'll see if I can
figure out what you need and get back to you.

Mike

 

[Larry Linson]

What started out as a simple projct
has turned in to a you know what!

Perhaps not, we just need a bit more information.

I'll get back to you tomorrow
evening after I've had a chance to
digest this. I guess I not giving you
the information you need. Sorry!!
I'll see if I can figure out what you
need and get back to you.

What we'd need is details on what you have and what you need to accomplish.
For example, "I want to prepare monthly reports for mutliple organizations
of the amounts of money donated to them. The money is picked up in cash
from donation jars at local businesses by several volunteers at various
times during the month, and also received at the headquarters by checks that
people mail. There's other information about the organization that needs to
be on the report, and donations need to be shown in totals received by check
and totals received by cash." That's an example for a very simple case --
yours may be more complex.

Larry Linson
Microsoft Access MVP

 

[Michael and Fayann]

OK here we go Larry,

My school does various fundraisers throughout the course of the school year.
I am the principal for both the high school and the elementary school. Each
school does their own set of fundraisers. The high school may have 10
different organizations that raise money for their group and the elementary
school may have 4 groups. I don't need to give monthly totals to groups or
anything like that. I use an accounting package to track how much each
group has in their account. I just wanted to develop a program that will
help my secretary count the money a group may bring in as we get a lot of
change in.

I developed a table called tblMoneyCount with the following fields in it:
OrganizationID - AutoNumber
Organization - Text (This would be Spanish Club, Yearbook, etc.)
GeneralOrganization - Text (This would be Elementary or High School)
Date - Date
Pennies - Number
Nichels- Number
(All the way down through Hundreds)

I have another table called tblChecks to enter individual checks received.
The following fields exist in it:
CheckID - AutoNumber
OrganizationID - Number
CheckName - Text (This is to enter who wrote the check)
Amount - Currency

I formed a relationship of 1 to many with OrganizationID

I then formed a query called qryCurrencyCount with all of the fields from
the tblMoneyCount and the fields of CheckName and Amount from the tblChecks.
I have a criteria under OrganizationID of Like[Enter Record Number] so that
I can print that specific record.

I then created a form called frmMoneyCount taken from the tblMoneyCount. I
have used all of the fields from the table.

I then inserted a subform called frmcheckssubform so that I could list the
checks brought in.

I have total calculations form coin currency, paper currency and checks and
then a total for all.

Now the problem... When I want to generate a report off of the query and
enter the [Record Number], all of the coin currency and paper currency is
listed and I have calculated fields in them to give me the totals. Part of
the query is from the tblChecks calling for the CheckName and the Amount.
When I do a print screen on this, all of the currency lists correctly but
only one name and the corresponding check is listed. I go to page 2 and it
is an exact repeat of page 1. I go to page 3 and the next person who wrote
a check is listed along with their corresponding check, page 4 is an exact
duplicate of page 3, and so on and so forth.

That is the problem. I don't know how to fix this. Sorry we've been
beating this around so much, I thought I could get the answer without boring
you with all of the details.

Mike

 

[Duane Hookom]

I would recommend a more normalized table structure.

tblDeposits
==================
DepositID autonumber Primary Key
DepositDate
DepositOrganizationID Link to tblOrganizations.OrganizationID

tblDepositDetail
===================
DepDetailID autonumber Primary Key
DepositID long integer link to tblDeposits.DepositID
Denomination (could be lookup to table containing Pennies, Nickels,...
twenties,...Check
DepositAmt total value of denomination


--
Duane Hookom
MS Access MVP

OK here we go Larry,

My school does various fundraisers throughout the course of the school year.
I am the principal for both the high school and the elementary school. Each
school does their own set of fundraisers. The high school may have 10
different organizations that raise money for their group and the elementary
school may have 4 groups. I don't need to give monthly totals to groups or
anything like that. I use an accounting package to track how much each
group has in their account. I just wanted to develop a program that will
help my secretary count the money a group may bring in as we get a lot of
change in.

I developed a table called tblMoneyCount with the following fields in it:
OrganizationID - AutoNumber
Organization - Text (This would be Spanish Club, Yearbook, etc.)
GeneralOrganization - Text (This would be Elementary or High School)
Date - Date
Pennies - Number
Nichels- Number
(All the way down through Hundreds)

I have another table called tblChecks to enter individual checks received.
The following fields exist in it:
CheckID - AutoNumber
OrganizationID - Number
CheckName - Text (This is to enter who wrote the check)
Amount - Currency

I formed a relationship of 1 to many with OrganizationID

I then formed a query called qryCurrencyCount with all of the fields from
the tblMoneyCount and the fields of CheckName and Amount from the tblChecks.
I have a criteria under OrganizationID of Like[Enter Record Number] so that
I can print that specific record.

I then created a form called frmMoneyCount taken from the tblMoneyCount. I
have used all of the fields from the table.

I then inserted a subform called frmcheckssubform so that I could list the
checks brought in.

I have total calculations form coin currency, paper currency and checks and
then a total for all.

Now the problem... When I want to generate a report off of the query and
enter the [Record Number], all of the coin currency and paper currency is
listed and I have calculated fields in them to give me the totals. Part of
the query is from the tblChecks calling for the CheckName and the Amount.
When I do a print screen on this, all of the currency lists correctly but
only one name and the corresponding check is listed. I go to page 2 and it
is an exact repeat of page 1. I go to page 3 and the next person who wrote
a check is listed along with their corresponding check, page 4 is an exact
duplicate of page 3, and so on and so forth.

That is the problem. I don't know how to fix this. Sorry we've been
beating this around so much, I thought I could get the answer without boring
you with all of the details.

Mike

Perhaps not, we just need a bit more information.


What we'd need is details on what you have and what you need to accomplish.
For example, "I want to prepare monthly reports for mutliple organizations
of the amounts of money donated to them. The money is picked up in cash
from donation jars at local businesses by several volunteers at various
times during the month, and also received at the headquarters by checks that
people mail. There's other information about the organization that needs to
be on the report, and donations need to be shown in totals received by check
and totals received by cash." That's an example for a very simple case --
yours may be more complex.

Larry Linson
Microsoft Access MVP

 

[Larry Linson]

Could you go to SQL view of the Query that is the basis for your report and
copy it into a post? I suspect that it is not the query, though, so much as
something in the design of the report that is causing strange pages.

Duane is correct that a more normalized table structure would be easier to
query, but maybe we can find the immediate problem, or maybe we will
recommend some restructuring.

Larry Linson
Microsoft Access MVP

OK here we go Larry,

My school does various fundraisers throughout the course of the school year.
I am the principal for both the high school and the elementary school. Each
school does their own set of fundraisers. The high school may have 10
different organizations that raise money for their group and the elementary
school may have 4 groups. I don't need to give monthly totals to groups or
anything like that. I use an accounting package to track how much each
group has in their account. I just wanted to develop a program that will
help my secretary count the money a group may bring in as we get a lot of
change in.

I developed a table called tblMoneyCount with the following fields in it:
OrganizationID - AutoNumber
Organization - Text (This would be Spanish Club, Yearbook, etc.)
GeneralOrganization - Text (This would be Elementary or High School)
Date - Date
Pennies - Number
Nichels- Number
(All the way down through Hundreds)

I have another table called tblChecks to enter individual checks received.
The following fields exist in it:
CheckID - AutoNumber
OrganizationID - Number
CheckName - Text (This is to enter who wrote the check)
Amount - Currency

I formed a relationship of 1 to many with OrganizationID

I then formed a query called qryCurrencyCount with all of the fields from
the tblMoneyCount and the fields of CheckName and Amount from the tblChecks.
I have a criteria under OrganizationID of Like[Enter Record Number] so that
I can print that specific record.

I then created a form called frmMoneyCount taken from the tblMoneyCount. I
have used all of the fields from the table.

I then inserted a subform called frmcheckssubform so that I could list the
checks brought in.

I have total calculations form coin currency, paper currency and checks and
then a total for all.

Now the problem... When I want to generate a report off of the query and
enter the [Record Number], all of the coin currency and paper currency is
listed and I have calculated fields in them to give me the totals. Part of
the query is from the tblChecks calling for the CheckName and the Amount.
When I do a print screen on this, all of the currency lists correctly but
only one name and the corresponding check is listed. I go to page 2 and it
is an exact repeat of page 1. I go to page 3 and the next person who wrote
a check is listed along with their corresponding check, page 4 is an exact
duplicate of page 3, and so on and so forth.

That is the problem. I don't know how to fix this. Sorry we've been
beating this around so much, I thought I could get the answer without boring
you with all of the details.

Mike

Perhaps not, we just need a bit more information.


What we'd need is details on what you have and what you need to accomplish.
For example, "I want to prepare monthly reports for mutliple organizations
of the amounts of money donated to them. The money is picked up in cash
from donation jars at local businesses by several volunteers at various
times during the month, and also received at the headquarters by checks that
people mail. There's other information about the organization that needs to
be on the report, and donations need to be shown in totals received by check
and totals received by cash." That's an example for a very simple case --
yours may be more complex.

Larry Linson
Microsoft Access MVP

 

[Michael and Fayann]

I'm not sure how to find the SQL for a query.

Mike

Could you go to SQL view of the Query that is the basis for your report and
copy it into a post? I suspect that it is not the query, though, so much as
something in the design of the report that is causing strange pages.

Duane is correct that a more normalized table structure would be easier to
query, but maybe we can find the immediate problem, or maybe we will
recommend some restructuring.

Larry Linson
Microsoft Access MVP

OK here we go Larry,

My school does various fundraisers throughout the course of the school year.
I am the principal for both the high school and the elementary school. Each
school does their own set of fundraisers. The high school may have 10
different organizations that raise money for their group and the elementary
school may have 4 groups. I don't need to give monthly totals to groups or
anything like that. I use an accounting package to track how much each
group has in their account. I just wanted to develop a program that will
help my secretary count the money a group may bring in as we get a lot of
change in.

I developed a table called tblMoneyCount with the following fields in it:
OrganizationID - AutoNumber
Organization - Text (This would be Spanish Club, Yearbook, etc.)
GeneralOrganization - Text (This would be Elementary or High School)
Date - Date
Pennies - Number
Nichels- Number
(All the way down through Hundreds)

I have another table called tblChecks to enter individual checks received.
The following fields exist in it:
CheckID - AutoNumber
OrganizationID - Number
CheckName - Text (This is to enter who wrote the check)
Amount - Currency

I formed a relationship of 1 to many with OrganizationID

I then formed a query called qryCurrencyCount with all of the fields from
the tblMoneyCount and the fields of CheckName and Amount from the tblChecks.
I have a criteria under OrganizationID of Like[Enter Record Number] so that
I can print that specific record.

I then created a form called frmMoneyCount taken from the tblMoneyCount. I
have used all of the fields from the table.

I then inserted a subform called frmcheckssubform so that I could list the
checks brought in.

I have total calculations form coin currency, paper currency and checks and
then a total for all.

Now the problem... When I want to generate a report off of the query and
enter the [Record Number], all of the coin currency and paper currency is
listed and I have calculated fields in them to give me the totals. Part of
the query is from the tblChecks calling for the CheckName and the Amount.
When I do a print screen on this, all of the currency lists correctly but
only one name and the corresponding check is listed. I go to page 2 and it
is an exact repeat of page 1. I go to page 3 and the next person who wrote
a check is listed along with their corresponding check, page 4 is an exact
duplicate of page 3, and so on and so forth.

That is the problem. I don't know how to fix this. Sorry we've been
beating this around so much, I thought I could get the answer without boring
you with all of the details.

Mike

What started out as a simple projct
has turned in to a you know what!

Perhaps not, we just need a bit more information.

I'll get back to you tomorrow
evening after I've had a chance to
digest this. I guess I not giving you
the information you need. Sorry!!
I'll see if I can figure out what you
need and get back to you.

What we'd need is details on what you have and what you need to accomplish.
For example, "I want to prepare monthly reports for mutliple organizations
of the amounts of money donated to them. The money is picked up in cash
from donation jars at local businesses by several volunteers at various
times during the month, and also received at the headquarters by

checks
that

people mail. There's other information about the organization that

needs
to

be on the report, and donations need to be shown in totals received by check
and totals received by cash." That's an example for a very simple case --
yours may be more complex.

Larry Linson
Microsoft Access MVP

 

[Duane Hookom]

Select View|SQL

--
Duane Hookom
MS Access MVP

I'm not sure how to find the SQL for a query.

Mike

Could you go to SQL view of the Query that is the basis for your report and
copy it into a post? I suspect that it is not the query, though, so much as
something in the design of the report that is causing strange pages.

Duane is correct that a more normalized table structure would be easier to
query, but maybe we can find the immediate problem, or maybe we will
recommend some restructuring.

Larry Linson
Microsoft Access MVP

OK here we go Larry,

My school does various fundraisers throughout the course of the school year.
I am the principal for both the high school and the elementary school. Each
school does their own set of fundraisers. The high school may have 10
different organizations that raise money for their group and the elementary
school may have 4 groups. I don't need to give monthly totals to

groups
or

anything like that. I use an accounting package to track how much each
group has in their account. I just wanted to develop a program that will
help my secretary count the money a group may bring in as we get a lot of
change in.

I developed a table called tblMoneyCount with the following fields in it:
OrganizationID - AutoNumber
Organization - Text (This would be Spanish Club, Yearbook, etc.)
GeneralOrganization - Text (This would be Elementary or High School)
Date - Date
Pennies - Number
Nichels- Number
(All the way down through Hundreds)

I have another table called tblChecks to enter individual checks received.
The following fields exist in it:
CheckID - AutoNumber
OrganizationID - Number
CheckName - Text (This is to enter who wrote the check)
Amount - Currency

I formed a relationship of 1 to many with OrganizationID

I then formed a query called qryCurrencyCount with all of the fields from
the tblMoneyCount and the fields of CheckName and Amount from the tblChecks.
I have a criteria under OrganizationID of Like[Enter Record Number] so that
I can print that specific record.

I then created a form called frmMoneyCount taken from the

tblMoneyCount.
I

have used all of the fields from the table.

I then inserted a subform called frmcheckssubform so that I could list the
checks brought in.

I have total calculations form coin currency, paper currency and

checks
and

then a total for all.

Now the problem... When I want to generate a report off of the query and
enter the [Record Number], all of the coin currency and paper currency is
listed and I have calculated fields in them to give me the totals.

Part
of

the query is from the tblChecks calling for the CheckName and the Amount.
When I do a print screen on this, all of the currency lists correctly but
only one name and the corresponding check is listed. I go to page 2

and
it

is an exact repeat of page 1. I go to page 3 and the next person who wrote
a check is listed along with their corresponding check, page 4 is an exact
duplicate of page 3, and so on and so forth.

That is the problem. I don't know how to fix this. Sorry we've been
beating this around so much, I thought I could get the answer without boring
you with all of the details.

Mike


What started out as a simple projct
has turned in to a you know what!

Perhaps not, we just need a bit more information.

I'll get back to you tomorrow
evening after I've had a chance to
digest this. I guess I not giving you
the information you need. Sorry!!
I'll see if I can figure out what you
need and get back to you.

What we'd need is details on what you have and what you need to
accomplish.
For example, "I want to prepare monthly reports for mutliple organizations
of the amounts of money donated to them. The money is picked up in cash
from donation jars at local businesses by several volunteers at various
times during the month, and also received at the headquarters by checks
that
people mail. There's other information about the organization that needs
to
be on the report, and donations need to be shown in totals received by
check
and totals received by cash." That's an example for a very simple case --
yours may be more complex.

Larry Linson
Microsoft Access MVP

 

[Guest]

I don't know if I new I would tell you but I need help too
do you know where I could find a picture of money in a bag
Thanks Michelle X

 

[Michael and Fayann]

Well Duane and Larry,

I think I figured 99% of this out. What I did was to go to Report Wizard
and included all of the fields off of the tblMoneyCount then selected
CheckName and CheckAmount from the tblChecks. I then set a criteria to
select the record number I wanted. When it created the report, the
following Header/Footers appeared:

Report Header (Organization, Record Number and Date)
OrganizationID Header (All of the Currency0
Detail (this is where the checks are listed)
Page Footer (Page number and Date)

Thanks for the help, Hopefully this will also help you. ONE LAST
QUESTION... HOW DO I GET THE CHECKS TO TOTAL? THE CONTROL SOURCE FOR THE
CHECKS IS AMOUNT.

Thanks,

Mike

Select View|SQL

--
Duane Hookom
MS Access MVP

I'm not sure how to find the SQL for a query.

Mike
report
and much
as

easier

to

query, but maybe we can find the immediate problem, or maybe we will
recommend some restructuring.

Larry Linson
Microsoft Access MVP

OK here we go Larry,

My school does various fundraisers throughout the course of the school
year.
I am the principal for both the high school and the elementary school.
Each
school does their own set of fundraisers. The high school may have 10
different organizations that raise money for their group and the
elementary
school may have 4 groups. I don't need to give monthly totals to groups
or
anything like that. I use an accounting package to track how much each
group has in their account. I just wanted to develop a program that will
help my secretary count the money a group may bring in as we get a

lot
of

change in.

I developed a table called tblMoneyCount with the following fields

in
it:

OrganizationID - AutoNumber
Organization - Text (This would be Spanish Club, Yearbook, etc.)
GeneralOrganization - Text (This would be Elementary or High School)
Date - Date
Pennies - Number
Nichels- Number
(All the way down through Hundreds)

I have another table called tblChecks to enter individual checks received.
The following fields exist in it:
CheckID - AutoNumber
OrganizationID - Number
CheckName - Text (This is to enter who wrote the check)
Amount - Currency

I formed a relationship of 1 to many with OrganizationID

I then formed a query called qryCurrencyCount with all of the fields from
the tblMoneyCount and the fields of CheckName and Amount from the
tblChecks.
I have a criteria under OrganizationID of Like[Enter Record Number] so
that
I can print that specific record.

I then created a form called frmMoneyCount taken from the tblMoneyCount.
I
have used all of the fields from the table.

I then inserted a subform called frmcheckssubform so that I could

list
the

checks brought in.

I have total calculations form coin currency, paper currency and checks
and
then a total for all.

Now the problem... When I want to generate a report off of the

query
and

enter the [Record Number], all of the coin currency and paper

currency
is

listed and I have calculated fields in them to give me the totals. Part
of
the query is from the tblChecks calling for the CheckName and the Amount.
When I do a print screen on this, all of the currency lists

correctly
but

only one name and the corresponding check is listed. I go to page 2 and
it
is an exact repeat of page 1. I go to page 3 and the next person who
wrote
a check is listed along with their corresponding check, page 4 is an exact
duplicate of page 3, and so on and so forth.

That is the problem. I don't know how to fix this. Sorry we've been
beating this around so much, I thought I could get the answer without
boring
you with all of the details.

Mike


What started out as a simple projct
has turned in to a you know what!

Perhaps not, we just need a bit more information.

I'll get back to you tomorrow
evening after I've had a chance to
digest this. I guess I not giving you
the information you need. Sorry!!
I'll see if I can figure out what you
need and get back to you.

What we'd need is details on what you have and what you need to
accomplish.
For example, "I want to prepare monthly reports for mutliple
organizations
of the amounts of money donated to them. The money is picked up

in
cash

from donation jars at local businesses by several volunteers at various
times during the month, and also received at the headquarters by checks
that
people mail. There's other information about the organization that needs
to
be on the report, and donations need to be shown in totals

received

by

 

[Duane Hookom]

You can total any numeric field in a group or report footer by using a text
box with a control source like:
=Sum([YourNumberField])

--
Duane Hookom
MS Access MVP

Well Duane and Larry,

I think I figured 99% of this out. What I did was to go to Report Wizard
and included all of the fields off of the tblMoneyCount then selected
CheckName and CheckAmount from the tblChecks. I then set a criteria to
select the record number I wanted. When it created the report, the
following Header/Footers appeared:

Report Header (Organization, Record Number and Date)
OrganizationID Header (All of the Currency0
Detail (this is where the checks are listed)
Page Footer (Page number and Date)

Thanks for the help, Hopefully this will also help you. ONE LAST
QUESTION... HOW DO I GET THE CHECKS TO TOTAL? THE CONTROL SOURCE FOR THE
CHECKS IS AMOUNT.

Thanks,

Mike

Select View|SQL

--
Duane Hookom
MS Access MVP


easier

have

10

different organizations that raise money for their group and the
elementary
school may have 4 groups. I don't need to give monthly totals to groups
or
anything like that. I use an accounting package to track how much each
group has in their account. I just wanted to develop a program that
will
help my secretary count the money a group may bring in as we get a lot
of
change in.

I developed a table called tblMoneyCount with the following fields in
it:
OrganizationID - AutoNumber
Organization - Text (This would be Spanish Club, Yearbook, etc.)
GeneralOrganization - Text (This would be Elementary or High School)
Date - Date
Pennies - Number
Nichels- Number
(All the way down through Hundreds)

I have another table called tblChecks to enter individual checks
received.
The following fields exist in it:
CheckID - AutoNumber
OrganizationID - Number
CheckName - Text (This is to enter who wrote the check)
Amount - Currency

I formed a relationship of 1 to many with OrganizationID

I then formed a query called qryCurrencyCount with all of the fields
from
the tblMoneyCount and the fields of CheckName and Amount from the
tblChecks.
I have a criteria under OrganizationID of Like[Enter Record

Number]

so

that
I can print that specific record.

I then created a form called frmMoneyCount taken from the tblMoneyCount.
I
have used all of the fields from the table.

I then inserted a subform called frmcheckssubform so that I could list
the
checks brought in.

I have total calculations form coin currency, paper currency and checks
and
then a total for all.

Now the problem... When I want to generate a report off of the query
and
enter the [Record Number], all of the coin currency and paper currency
is
listed and I have calculated fields in them to give me the totals. Part
of
the query is from the tblChecks calling for the CheckName and the
Amount.
When I do a print screen on this, all of the currency lists correctly
but
only one name and the corresponding check is listed. I go to page

2
and

it
is an exact repeat of page 1. I go to page 3 and the next person who
wrote
a check is listed along with their corresponding check, page 4 is an
exact
duplicate of page 3, and so on and so forth.

That is the problem. I don't know how to fix this. Sorry we've been
beating this around so much, I thought I could get the answer without
boring
you with all of the details.

Mike


What started out as a simple projct
has turned in to a you know what!

Perhaps not, we just need a bit more information.

I'll get back to you tomorrow
evening after I've had a chance to
digest this. I guess I not giving you
the information you need. Sorry!!
I'll see if I can figure out what you
need and get back to you.

What we'd need is details on what you have and what you need to
accomplish.
For example, "I want to prepare monthly reports for mutliple
organizations
of the amounts of money donated to them. The money is picked up in
cash
from donation jars at local businesses by several volunteers at
various
times during the month, and also received at the headquarters by
checks
that
people mail. There's other information about the organization that
needs
to
be on the report, and donations need to be shown in totals

received

by
check
and totals received by cash." That's an example for a very simple
case --
yours may be more complex.

Larry Linson
Microsoft Access MVP

 

[Michael and Fayann]

That worked. Thanks for all of your help. You stuck in there with me the
whole way. I really appreciate it.

Mike

You can total any numeric field in a group or report footer by using a text
box with a control source like:
=Sum([YourNumberField])

--
Duane Hookom
MS Access MVP

Well Duane and Larry,

I think I figured 99% of this out. What I did was to go to Report Wizard
and included all of the fields off of the tblMoneyCount then selected
CheckName and CheckAmount from the tblChecks. I then set a criteria to
select the record number I wanted. When it created the report, the
following Header/Footers appeared:

Report Header (Organization, Record Number and Date)
OrganizationID Header (All of the Currency0
Detail (this is where the checks are listed)
Page Footer (Page number and Date)

Thanks for the help, Hopefully this will also help you. ONE LAST
QUESTION... HOW DO I GET THE CHECKS TO TOTAL? THE CONTROL SOURCE FOR THE
CHECKS IS AMOUNT.

Thanks,

Mike

Select View|SQL

--
Duane Hookom
MS Access MVP


I'm not sure how to find the SQL for a query.

Mike
Could you go to SQL view of the Query that is the basis for your report
and
copy it into a post? I suspect that it is not the query, though,

so
much

as
something in the design of the report that is causing strange pages.

Duane is correct that a more normalized table structure would be easier
to
query, but maybe we can find the immediate problem, or maybe we will
recommend some restructuring.

Larry Linson
Microsoft Access MVP

OK here we go Larry,

My school does various fundraisers throughout the course of the school
year.
I am the principal for both the high school and the elementary school.
Each
school does their own set of fundraisers. The high school may

have

10
different organizations that raise money for their group and the
elementary
school may have 4 groups. I don't need to give monthly totals to
groups
or
anything like that. I use an accounting package to track how much
each
group has in their account. I just wanted to develop a program that
will
help my secretary count the money a group may bring in as we get

a
lot

of
change in.

I developed a table called tblMoneyCount with the following

fields
in

it:
OrganizationID - AutoNumber
Organization - Text (This would be Spanish Club, Yearbook, etc.)
GeneralOrganization - Text (This would be Elementary or High School)
Date - Date
Pennies - Number
Nichels- Number
(All the way down through Hundreds)

I have another table called tblChecks to enter individual checks
received.
The following fields exist in it:
CheckID - AutoNumber
OrganizationID - Number
CheckName - Text (This is to enter who wrote the check)
Amount - Currency

I formed a relationship of 1 to many with OrganizationID

I then formed a query called qryCurrencyCount with all of the fields
from
the tblMoneyCount and the fields of CheckName and Amount from the
tblChecks.
I have a criteria under OrganizationID of Like[Enter Record

Number]

so
that
I can print that specific record.

I then created a form called frmMoneyCount taken from the
tblMoneyCount.
I
have used all of the fields from the table.

I then inserted a subform called frmcheckssubform so that I

could
list

the
checks brought in.

I have total calculations form coin currency, paper currency and
checks
and
then a total for all.

Now the problem... When I want to generate a report off of the query
and
enter the [Record Number], all of the coin currency and paper currency
is
listed and I have calculated fields in them to give me the totals.
Part
of
the query is from the tblChecks calling for the CheckName and the
Amount.
When I do a print screen on this, all of the currency lists correctly
but
only one name and the corresponding check is listed. I go to

page

2 person
who

is

an up
in