New User - Linking tables

S

sheri

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!
 
K

Klatuu

Post the SQL of the query that is returning the duplicates, please.
To do that, open the query in the query builder, select SQL view, and
copy/paste it into a reply and we will have a look.
 
S

sheri

Here you go - Yeh-haw:
SELECT [Expenses SPA DEV].ID, [LastName] & ", " & [FirstName] AS [Full
Name], [Expenses SPA DEV].PAYEE, [Expenses SPA DEV].[CHECK DATE], [Expenses
SPA DEV].[CHECK NO], [Expenses SPA DEV].AMOUNT AS [Expenses SPA DEV_AMOUNT],
[Expenses SPA DEV].DESCRIPTION, [All Donations].CONTRIBUTOR, [All
Donations].AMOUNT AS [All Donations_AMOUNT], [All Donations].DATE, [All
Donations].[GENERAL USE OF FUNDS]
FROM [SPA DEV Students] INNER JOIN ([All Donations] INNER JOIN [Expenses SPA
DEV] ON [All Donations].[Student ID] = [Expenses SPA DEV].ID) ON ([SPA DEV
Students].ID = [All Donations].[Student ID]) AND ([SPA DEV Students].ID =
[Expenses SPA DEV].ID);
 
K

Klatuu

A bit complex, so it is hard to tell without having it in front of me, but it
could be how you are joining the tables. Is there any reason not to use a
LEFT join rather than an INNER join?
--
Dave Hargis, Microsoft Access MVP


sheri said:
Here you go - Yeh-haw:
SELECT [Expenses SPA DEV].ID, [LastName] & ", " & [FirstName] AS [Full
Name], [Expenses SPA DEV].PAYEE, [Expenses SPA DEV].[CHECK DATE], [Expenses
SPA DEV].[CHECK NO], [Expenses SPA DEV].AMOUNT AS [Expenses SPA DEV_AMOUNT],
[Expenses SPA DEV].DESCRIPTION, [All Donations].CONTRIBUTOR, [All
Donations].AMOUNT AS [All Donations_AMOUNT], [All Donations].DATE, [All
Donations].[GENERAL USE OF FUNDS]
FROM [SPA DEV Students] INNER JOIN ([All Donations] INNER JOIN [Expenses SPA
DEV] ON [All Donations].[Student ID] = [Expenses SPA DEV].ID) ON ([SPA DEV
Students].ID = [All Donations].[Student ID]) AND ([SPA DEV Students].ID =
[Expenses SPA DEV].ID);


Klatuu said:
Post the SQL of the query that is returning the duplicates, please.
To do that, open the query in the query builder, select SQL view, and
copy/paste it into a reply and we will have a look.
 
S

sheri

Sorry don't even know what that means or how it gets there.

Klatuu said:
A bit complex, so it is hard to tell without having it in front of me, but it
could be how you are joining the tables. Is there any reason not to use a
LEFT join rather than an INNER join?
--
Dave Hargis, Microsoft Access MVP


sheri said:
Here you go - Yeh-haw:
SELECT [Expenses SPA DEV].ID, [LastName] & ", " & [FirstName] AS [Full
Name], [Expenses SPA DEV].PAYEE, [Expenses SPA DEV].[CHECK DATE], [Expenses
SPA DEV].[CHECK NO], [Expenses SPA DEV].AMOUNT AS [Expenses SPA DEV_AMOUNT],
[Expenses SPA DEV].DESCRIPTION, [All Donations].CONTRIBUTOR, [All
Donations].AMOUNT AS [All Donations_AMOUNT], [All Donations].DATE, [All
Donations].[GENERAL USE OF FUNDS]
FROM [SPA DEV Students] INNER JOIN ([All Donations] INNER JOIN [Expenses SPA
DEV] ON [All Donations].[Student ID] = [Expenses SPA DEV].ID) ON ([SPA DEV
Students].ID = [All Donations].[Student ID]) AND ([SPA DEV Students].ID =
[Expenses SPA DEV].ID);


Klatuu said:
Post the SQL of the query that is returning the duplicates, please.
To do that, open the query in the query builder, select SQL view, and
copy/paste it into a reply and we will have a look.
--
Dave Hargis, Microsoft Access MVP


:

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!
 
S

sheri

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.
 
K

Klatuu

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.
 
S

sheri

I don't have a "main table" per se. I tried changing one of the joins and
now it's saying "The SQL statement could not be executed because it contains
ambiguous outer joins. To force one of the joins to be performed first,
create a separate query that performs the first join and then include that
query in your SQL statement. (Error 3258)" So, I'll try doing that.
 
S

sheri

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.
 
S

sheri

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.
 
J

Jeff Boyce

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
 
S

sheri

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.
 
J

Jeff Boyce

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
 
S

sheri

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!!
 
J

Jeff Boyce

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.
 
S

sheri

Yes Student ID - I thought that was a given. Anyway, still not working. I'm
convinced that Access can't handle two unrelated tables in a report. It's
trying to hard to relate the data and other than the fact that they both have
a relationship with the Student table they really aren't related. This
really seems like it should be easy though, so maybe somehow I'll figure it
out.

Thanks for trying.

Jeff Boyce said:
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!!
 
J

Jeff Boyce

Perhaps a difference of definition...

I would state that your Expenses and Donations tables ARE related, via
StudentID.

But let me check ... are you using StudentID in each of those as a foreign
key, or are you using the Access Autonumber to create a new (and unique)
"StudentID"? If the latter, there's no way Access would know how to connect
the three tables, because Autonumber is unique TO-THE-TABLE.

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:
Yes Student ID - I thought that was a given. Anyway, still not working.
I'm
convinced that Access can't handle two unrelated tables in a report. It's
trying to hard to relate the data and other than the fact that they both
have
a relationship with the Student table they really aren't related. This
really seems like it should be easy though, so maybe somehow I'll figure
it
out.

Thanks for trying.

Jeff Boyce said:
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!
 
S

sheri

No the student ID is the Key from the Student table and I agree they are
related via the student ID but that doesn't seem to matter.

I have another report that has a one to many relationship that works just
fine (Donations and checks identified with the donation via the donation
key). But for what I need it is a many to many relationship - I may be
misusing this term - Many donations with many checks not specifically
identified with the donations via the donation key.

I think it should work but so far it hasn't (I've spent way too much time on
this too!). I have a work around but it requires duplication of effort. Is
there a way to have a form enter into two tables to avoid this?

Jeff Boyce said:
Perhaps a difference of definition...

I would state that your Expenses and Donations tables ARE related, via
StudentID.

But let me check ... are you using StudentID in each of those as a foreign
key, or are you using the Access Autonumber to create a new (and unique)
"StudentID"? If the latter, there's no way Access would know how to connect
the three tables, because Autonumber is unique TO-THE-TABLE.

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:
Yes Student ID - I thought that was a given. Anyway, still not working.
I'm
convinced that Access can't handle two unrelated tables in a report. It's
trying to hard to relate the data and other than the fact that they both
have
a relationship with the Student table they really aren't related. This
really seems like it should be easy though, so maybe somehow I'll figure
it
out.

Thanks for trying.

Jeff Boyce said:
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.

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!
 
J

Jeff Boyce

That sounds like a work-around ... and those have a way of coming back to
bite you!

Let me try paraphrasing your earlier description of your table structure, to
make sure I understand where this starts:

tblStudent
StudentID
FName
LName
GradDate
Address
etc.

tblDonation
DonationID
DonationDate
Donor
Amount
Program
Description
Type
StudentID (foreign key, refers to tblStudent record)
??Expense related?? (how do you show this?)
??Related to nothing?? (how do you show this?)

tblExpense
ExpenseID
StudentID (foreign key)
DonationID (? I don't understand how an expense is related to a
donation)
Payee
Amount
CheckNo
Description

If I had three tables like this, I'd add all three to a new query, join
tblStudent to tblDonation and to tblExpense on the StudentID field, then
change the join type to "LEFT" (i.e., directional) joins (all of tblStudent
records, no matter whether there are any records in either Donation or
Expense).

I guess I'm still not seeing the big picture here...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top