A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

New User - Linking tables



 
 
Thread Tools Display Modes
  #21  
Old October 22nd, 2009, 12:06 AM posted to microsoft.public.access.tablesdbdesign
Sheri
external usenet poster
 
Posts: 93
Default New User - Linking tables

Jeff,

I agree with you completely. Seems like it should work but it doesn't.
I've done everything you've suggested, but it doesn't work.

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

  #22  
Old October 29th, 2009, 05:16 PM posted to microsoft.public.access.tablesdbdesign
Sheri
external usenet poster
 
Posts: 93
Default New User - Linking tables

Figured it out!!! Yea! Use subtable.


  #23  
Old October 29th, 2009, 05:18 PM posted to microsoft.public.access.tablesdbdesign
Sheri
external usenet poster
 
Posts: 93
Default New User - Linking tables

I mean sub-report not table.

"sheri" wrote:

Figured it out!!! Yea! Use subtable.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:22 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.