Begging Help with Relationships for Report

[Maria Tique]

Scenario:

One employee can have many grants.

One employee can have many gifts

One employee can have many subcontracts.

I have 4 tables: Employees, Grants, Gifts, Subcontracts. (Please see attachment)

Each Grant, Gift and Subcontract have a unique account #.

-All Grants start with an account# starting in 1.

-All Gifts start with an account# starting in 2.

-All Subcontrats start with an account# starting in 3.

Problem and Question:

1. If I change the employees id, first name, or last name in the employee table, the grants, gifts, and subcontract table does not update the new employees name.

How can I get it to do this throughout the grants, gifts, and subcontract tables?

2. I have never created a report that pulls data from [color:red]4 tables. Pretty please help me to create [color:red]ONE grand report that is sorted by an employees last name. Below the last name of each employee, it will display all the account #’s for grants, gift, and subcontracts that employee has. Then gives a subtotal for that employee. I will need a grand total to show the grand total $ for all employees.

Help???

Example:

10 John Doe

Account #101 Grant from Fed Govt. 50,000

Account #201 Gift from Ford Corp 10,000

Account #202 Gift from McDonls 15,000

Account #301 Subcontrat Mrs. Green 5,000

Subtotal for John Doe $80,000

Grant_Gift_Subcon_DB.zip

[aaa]

Hi, Maria!

I edited your attachment for first question.

About second i will think.

Grant_Gift_Subcon_DB.zip

[aaa]

One of way for report is importing all of Grants, Gifts and Subcontracts records to one table and in this table make report. If you will have problem on it attach your file with little more records i will try to create report.

Edited June 26, 2006 by Guest

[Maria Tique]

Thank you so much for your help.

I have added more records per your request. (please see attachment). I also created a table and named it "Combination" which will hold all the grants, gift, and subcontract data. I am not sure if this is correct.

Would you be able to check it out and make any necessary changes to make the report successful.

If i make changes to example: grant table, then it should update the report with the new changes.

Thank you again for your time

Grant_Gift_Subcon_revised.zip

[Maria Tique]

Thank you a million.

[Maria Tique]

Hello aaa,

Were you able to check out the new zip file with new records for the report.