I'm certainly not saying two tables make it complex just not simple. There
is not a one to many relationship between the two tables and that makes it
more complex, at least to me. There is a many to many relationship in that
there are many money's in and many money's out related by an ID. So for each
ID there is a one to many relationship to money's in and money's out. But
I've attempted using a single report and it wants to return repeating data
matching each money's in with each money's out. I've gotten it almost there
using subreports but the in the post I mentioned before the running sum gives
me a different number when not visible verses when visible (correct sum).
Have you run into this issue?
It may be that I don't know what you are suggesting because it really does
seem like there should be a very simple answer.
Without a subreport this is what happens:
So in more detail - We get donations. Some for general use (unrestricted)
and some are designated with specific use (temporarily restricted). Some
donations are specifically designated for a student (temporarily restricted
with a specific Student ID).
The donation table relates to the student table via the student ID. If a
student is not associated with a donation (i.e. general use funds) then the
student ID is left blank. I could set a null value but that really doesn't
seem to be the issue here. I do not relate donations to the expense table in
the donation table purposely because more than one check could be associated
with a deposit. This is done in the expense table because typically one
check will not relate to more than one donation.
In the normal course of business we write checks to cover expenses. Some
checks related back to specific donations (designated by the donation ID),
while other are for general program expenses (no donation ID). Some checks
relate specifically to students (designated by the Student ID). These may or
may not relate to a specific donation.
Example1: We receive a donation of $1000 for a party. This is temporarily
restricted due to the designation for a party but it doesn't relate to a
student. We will have related checks when we pay for decor or food, etc
which will will designate with the donationID.
Example2: We receive a donation of $500 for a student scholarship to be
used for college expenses or living expense - Tracy Smith. We will designate
this as restricted and identify it with the student ID. Tracy is going to
school and needs money for books. We write a check for $200 to the book
store and designate it with the student ID. Since it is very likely that
Tracy received various scholarships we probably will not related the check to
the donationID but we may. Lets say we then write her another check for $200
for living expenses. What I'm wanting to see in a report is:
Tracy Smith Student ID 20
1/1/09 $500 Scholarship for college and living expenses
6/30/09 $200 1100 Book Store Books for college
7/15/09 $200 1101 Tracy Smith Living expenses
Balance $100
What it is giving me is this:
Tracy Smith Student ID 20
1/1/09 $500 Scholarship for college and living expenses
6/30/09 $200 1100 Book Store Books for college
1/1/09 $500 Scholarship for college and living expenses
7/15/09 $200 1101 Tracy Smith Living expenses
Balance $600
And if I had another donation for Tracy it would list the other donation
with all the checks again.
This is the best I can do to explain what is happening.
Sheri
Marshall Barton said:
How does having two tables make it complex?
Using a query that joins two tables as a report's record
source is a very common practice. Just create a new query
and add both tables. Drag the linking field from one table
to the linking field in the other table to join the tables.
Then drag the needed fields from both tables to the query's
field list.
Use Sorting and Grouping (View menu) in the report to get
group header and footer sections for the one side table
fields and put the many side fields in the detail section.
I really think that my earlier suggestion about doing away
with the subreport and adding another group on the simple
expression =Amount >= 0 will separate the (detail) In and
Out records so it all looks like you have a subreport, but
without the complications you are struggling with.
--
Marsh
MVP [MS Access]
Thanks for the response but my data is from two tables so unfortunately
simplicity doesn't work at least not that I've found. I've managed to get
some of what I need but have other issues Please see my other post named
Running Sum changes when not visible in the report section.
Marshall Barton said:
sheri wrote:
Ok so the Nz work to eliminate one issue but I have another.
What I'm attempting to accomplish is essentially a "statement" money in and
money out with a balance for the account. I have tables, queries and reports
for each. It took me FOREVER to figure out how to get as far as I am which
is Money In as the main report with a subreport for Money Out. If Money In
is Null then the Money Out doesn't show at all. Which lead me to these
posts.
The total in the subreport [MoneyOut] is =Sum([Amount])
The total in the main report [MoneyIn] is =Sum([Amount])
The Balance in the main report is now =Nz(-Child29.Report.[Sum Of
AMOUNT]+Sum([AMOUNT]),0+Sum([AMOUNT]))
Child29 is MoneyOut
The reports are linked through an account ID
If there are no Money In records then the linking field
Account ID has no value and can not link to any Money Out
records.
I suspect that you might be able to do this more easily by
using just the main report and grouping in a expression
like:
=Amount >= 0
to separate the Money In from the Money Out. This way the
group footer can display the subtotals by using:
=Sum(Amount)
and the report footer can display the grand total by using
the same text box expression.
.
