you're welcome, Mark ;) happy to help
[thanks, Duane :)]
Warm Regards,
Crystal
*
(: have an awesome day :)
*
________________________________
From: "fischermh@netzero.com" <Fischermh@netzero.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, April 11, 2012 1:24 PM
Subject: Re: [MS_AccessPros] Question regarding Data Organization
Crystal, Thank you. Mark
---------- Original Message ----------
From: Crystal
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]
Tidak ada komentar:
Posting Komentar