What was missing from your description was the primary key for the
table1-Students. Can I assume it is StudentID?
Create a new query in design view, put [tblStudents] in there, put
[tblDonations] in there and put [tblExpenses] in there.
Now drag StudentID from tblStudent to StudentID in [tblDonations]. Do
the
same again to [tblExpenses].
Click on each join line, right-click, select Properties, and set the
joins
to "LEFT" joins (i.e., all [tblStudent] records and ANY matching from the
joined-to table. Do this with each join line.
This query should give you all students and any Donations and/or
Expenses.
Use that as the source for your report, grouping on Student, and possibly
on
Donation and on Expense.
Good luck!
Regards
Jeff Boyce
Microsoft Access MVP
--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.
Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.
You can thank the FTC of the USA for making this disclaimer
possible/necessary.
sheri said:
Table 1 Students - List all students (name, grad date, address, etc)
Table 2 Donations - Has all donations, some related to a particular
student
(by Key from table 1), others related to specific expenses (donation
key
entered into table 3) and still others not related to anything. (Date,
Donor,
Amount, Program, Description, Type, Student ID)
Table 3 Expenses - Has all checks written, some related to a particular
student (by Key from table 1), others related to specific Donations (by
Donation key from table 2)and still others not related to anything.
(Date,
Payee, Amount, Check no, Amount, Description, Donation Key, Student ID)
I'm looking to get a report showing all donations and all expenses
related
to a particular student:
ID: 40 Student: Sarah C Grad Date: 6/30/08
Donations: $1000 Jackson Foundation 1/1/09 Scholarship for Sarah C
$5000 Rancho Foundation 6/30/08 Scholarship for Sarah
C
Expenses: $250 Bookstore 2/1/09 College books
$2500 State College 9/1/08 College Tuition
Remaining Balance: $3250
This just seems like a no brainer but it's not coming easily that's for
sure.
While I could combine the two tables to get what I want, the issue is
that
there are many more unrelated donations that I thought it would be best
to
keep separate tables.
I have a query that pulls all donations that have a student ID and one
that
pulls all checks that have a student ID. I have a third that pulls
those
two
together, but I don't think this one is necessary for the report I
want.
I've tried relating and unrelating the student ID in these queries and
neither seems to work.
HELP!!
:
I'm still having a bit of difficulty envisioning the data &
relationships.
Could you describe your table structure a bit more?
Regards
Jeff Boyce
Microsoft Access MVP
At first yes, but I figured out that part. I'm okay with the query
being
what it is but why does the report show duplicates? I've tried
everything
I
know to get this to report properly and it seems so basic, yet it
still
doesn't work. What it wants to do is match every donation with
every
check
for each ID.
If there is something I'm missing please let me know.
:
Is there a chance that you are expecting to get 'single line'
results
in
your query?
Because if you join tables together, you'll get one row for each
combination
that matches your criteria. Sometimes, that means you see what you
may
describe as "duplicates" for some of the fields.
If you are only getting some of the fields "duplicated", but each
row
containing a unique combination of values, then consider using a
report
to
display/print out the results. You could use those so-called
duplicates
to
"group by", and only show each one once in the report.
Good luck!
Regards
Jeff Boyce
Microsoft Access MVP
Now I've written a separate query for both the allocated
donations
and
the
expenses that have ID's and a third to try to combine the data,
but
it's
just
not working. Even taking the two queries in a report doesn't
work.
It's
like they are too related. Seems simple but can't get it to
work.
I
just
need to know donations and expenses by ID in one report. If
there
is
another
way please let me know. Otherwise I have to have another table
with
the
info
combined and that brings up other questions.
:
Okay so I wrote a query to identify all allocated donations
(ones
with
an
ID)
and then I wrote another query using that query and adding the
expenses
table
but it still gives me multiple records no matter how the join is
performed.
:
That helps some.
In the query builder, there should be a line between the main
table
and
the
other tables. If you right click on the line to get a dialog
that
shows the
join type, try choosing the option to show all the records in
the
main
table
and only matching record is the other tables. This will not
give
you
exactly
what you want, but if we can get that far, we can work it from
there.
--
Dave Hargis, Microsoft Access MVP
:
The results are that every check is matched with every
deposit.
Does
that
help. I tried changing the Join to left in the SQL view and
it
said
it
wasn't supported.
:
Okay, so I'm using Access 2000 and I'm not a programer so
please
speak in
plain english. I have a table of all donations made and a
table
of
all
checks cut out of our program. The relationship between
the
tables
is an ID
if applicable and in some cases a donation key. Some, but
not
all,
donations
are related to checks and visa-versa and not all donations
or
checks have an
ID. I'm trying to run a report by ID that shows both the
donations
and
checks. I've designed a simple query to pull the
information
together but
the results are showing a lot of duplicate records and I'm
not
sure
why.
Thanks in advance!
