Grant Expense Database Design Help

[MNNovice]

Please forgive me for this is a long request. I am designing a DB to capture expenses related to Federal Grants and to generate appropriate reports.

Each grant starts with a Notice of Grant Award (NOGA) which has 7 parts.

1. Grant Number
2. Grant Title
3. Amendment Number
4. Revision Number
5. Fund Information
   1. Apportion Year (AY), could be a single year or multiple year.
   2. Grant Amount associated with each Apportion Year.
      Grant amount has 3 parts:
      1. Total amount
      2. Federal amount (FA)
      3. Local Amount (LA)
   Percentage of FA & LA varies, 80%/20%, 95%/5%, 90%/10% or any other combination
6. Revenue Budget
   1. Broken down by a string of accounts that is comprised of Account No., Fund No., Class No., Dept ID, Project No.
   2. Current Budget Amount
   3. Change ( + / - ) Budget Amount
   4. Amended Budget Amount
7. Expense Budget
   1. Same as 6 a. but also includes a field for Project Manager

NOTE: Budget amount does not show the breakdown of percentage for Federal and Local money. In other words it shows 100% of the budget.

Types of expenses

1. Payroll expenses (26 pay period)
2. Cost Allocation
3. Marketing Expenses
4. Preventive Maintenance
5. Inventory
6. Accounts Payables (with PO)
7. Accounts Payables (without PO)
8. Adjustments

Expense items vary and will need a separate table to accommodate future type of expenses.

Other Information
A grant may have more than one project
A project may be funded by more than one grant
A project manager may be managing more than one grant and / or more than one project

What is needed?

1. Report showing expenses for each grant
2. Report showing expenses for each project
3. Report to request fund from authority for expenses incurred. This is critical. Somewhere I must store data to include the following information:
   1. Payroll expenses
   2. Marketing expenses
   3. Accounts Payables …etc.

And then a macro will generate a form to request funding for these expenses that will include, Grant No, Fund No., Class, Dept ID, Project, Account etc and the amount expended for each line.

Here is an example

Grant No.    Fund No     Program No     Payroll    Marketing     Total
       Account No   Dept No   Project No        A/P         Inv
    1   6540   871   999   099   65701    800               200   1000
    1   6540   871   999   099   63350           50    50          100
    2   4705   863   998   033   63740   1500   500               2000
    3   4210   862   968   025   65701    300          50   150    500

Let’s assume for all these Grants the Fed to Local ratio is 80 / 20

A form requesting fund for 80% will need to be generated from these expenses. Let's call it a DRAW Report

Grant No.   Total  Previous     New
                    Balance   Balance
    1        880     5,000     4,120
    2      1,600    15,000    13,400
    3        400     3,000     2,600
Total      2,880    23,000    20,120

A report that will show all expenses for each project for each grant in 80 / 20 ratio. For example, for the first expense the detailed expense report will show Payroll was $1,000, (Fed 800 / Local 200), Inventory was $250 (Fed 200, / Local 50). Let’s call this Detailed Expense Report.


What I have done so far.

1. Created separate tables to record, Accounts, Funds, Program, Department, Projects, Project Managers
2. Created a table to record each NOGA that includes Grant Number, Title etc.
3. Created 3 sub forms to record, 1) Fund Information (Apportion year, Fed amount, local amount and total amount), 2) Revenue budget, and 3) Expense Budget
4. Created a form to record each NOGA that contains these 3 sub forms

What I Need Help with

1. Establishing the relationships to make these tables work
2. How do I make the Amendment No & Revision No as such that the latest version of the grant becomes available for all calculations and the previous versions stays at the back without being overwritten?
3. How do I make the percentages an option when entering the fund information (item 5Bi, above)?
4. How do I record various types of expenses? Do I not need a separate table for this?

Any help is much appreciated. Thanks.

[NeoPa]

When I get home I will look at the database and see if I can get to grips with the problem as a start.

[MNNovice]

NeoPa:

Many thanks.

[NeoPa]

I don't know why I have two different versions of the database.

I need to know which one to proceed with. Please let me know ASAP.

[NeoPa]

I have attached a picture of the windows used to control the relationships between tables. The second is shown after clicking Join Type... on the first one.

1. Enforce referential Integrity - Ensures the main table is not able to take values which don't have a matching record in the lookup table.
2. Cascade Update Related Fields - If you change the key of one of the lookup table records, all records in the main table that refer to that lookup record, are changed automatically to reflect the new value.
3. Cascade Delete Related Fields - If you delete one of the lookup table records, all records in the main table that refer to that lookup record, are also deleted.

With Referential integrity set, a Type 2 link (as you have) makes sense only if you envisage tblNOGA records with unset values for the lookup field. They would be Nulls. If this is expected then this setup is good. Otherwise (There will always be a value as well as a lookup record for every tblNOGA record), a Type 1 is probably a better option.

[MNNovice]

NeoPa:

Thanks. M

[NeoPa]

As a general rule, I would always recommend using Type 2 rather than Type 3 Joins.

It's just clearer to my way of thinking. Especially never mix them, where some are Type 2s while others are Type 3s. Checking details is so much harder as things are not done consistently.

From what I've seen so far though, this is reasonably well done, with consideration of the issues evident. A good start.

[MNNovice]

NeoPa:

Thanks for the tips I will try to follow. Thanks.