By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,489 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,489 IT Pros & Developers. It's quick & easy.

Grant Expense Database Design Help

100+
P: 418
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
Expand|Select|Wrap|Line Numbers
  1. Grant No.    Fund No     Program No     Payroll    Marketing     Total
  2.        Account No   Dept No   Project No        A/P         Inv
  3.     1   6540   871   999   099   65701    800               200   1000
  4.     1   6540   871   999   099   63350           50    50          100
  5.     2   4705   863   998   033   63740   1500   500               2000
  6.     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
Expand|Select|Wrap|Line Numbers
  1. Grant No.   Total  Previous     New
  2.                     Balance   Balance
  3.     1        880     5,000     4,120
  4.     2      1,600    15,000    13,400
  5.     3        400     3,000     2,600
  6. 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.
Apr 14 '09 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,709
When I get home I will look at the database and see if I can get to grips with the problem as a start.
Apr 15 '09 #2

100+
P: 418
NeoPa:

Many thanks.
Apr 15 '09 #3

NeoPa
Expert Mod 15k+
P: 31,709
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.
Apr 15 '09 #4

NeoPa
Expert Mod 15k+
P: 31,709
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.
Attached Images
File Type: jpg NOGALink.jpg (17.0 KB, 262 views)
Apr 15 '09 #5

100+
P: 418
NeoPa:

Thanks. M
Apr 15 '09 #6

NeoPa
Expert Mod 15k+
P: 31,709
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.
Apr 15 '09 #7

100+
P: 418
NeoPa:

Thanks for the tips I will try to follow. Thanks.
Apr 16 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.