Selasa, 10 April 2012

Re: [MS_AccessPros] Question regarding Data Organization

 

Hi Mark,

>5.  Position Funding Table:  On table for each position.  We have 58 staff positions, so 58 tables.  "

No! do not repeat data structures.

Positions
- PositID, autonumber -- PK
- Posit, text -- position description
- etc

PositGrants
-PosGrantID, autonumber -- PK
- PositID, long -- FK to Positions
- GrantID, long -- FK to Grants
- PosAmt, currency

> "6.  Expense Funding Table:  One table for each expense line item."

No -- see my answer to #5

PK = Primary Key
FK = foreign Key

Warm Regards,
Crystal

Microsoft MVP, Access
remote training and programming

www.AccessMVP.com/strive4peace

www.YouTube.com/LearnAccessByCrystal
www.YouTube.com/LearnByCrystal

* (:  have an awesome day :) *

________________________________
From: MarkF
Subject: [MS_AccessPros] Question regarding Data Organization

I have begun to build an Access 2007 database to track non-profit grant funding.  I am a novice and definitely the epitome of the saying "a little knowledge is a dangerous thing."

Basically, we have many grants.  Each grants funds a specified list of expenses.  We need to make sure that an expense is not funded by two different grants.  We also need to know what expenses are not funded.  For example grant A may fund 100% of Employee 1' compensation and 50% of employee 2's compensation. 

Here is my thought regarding the table structure:

1.  Employee Table:name, compensation, position, department
2.  Department Table: List of Departments
3.  Grant Table:  Start Date, End Date, amount, Grantor
4  Expense Table:  Expense, amount  Such as Rent  $1,000
5.  Position Funding Table:  On table for each position.  We have 58 staff positions, so 58 tables.  The table fields are Grant and amount.  So if Position 1, is funded by 3 grants, each grant is listed with the amount of funding.
6.  Expense Funding Table:  One table for each expense line item, with grant and amount fields.  For example, a rent table with 2 grants paying 50% each.

I am thinking there may be an better way without have a table for each expense.  Any suggestions would be greatly appreciated.

Thanks,
Mark

[Non-text portions of this message have been removed]

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar