Rabu, 11 April 2012

Re: [MS_AccessPros] Question regarding Data Organization

 

Crystal, Thank you. Mark

---------- Original Message ----------
From: Crystal <strive4peace2008@yahoo.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] Question regarding Data Organization
Date: Tue, 10 Apr 2012 19:11:01 -0700 (PDT)

Hi Mark,

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

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.&#65533; Expense Funding Table:&#65533; 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

* (:&#65533; 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.&#65533; I am a novice and definitely the epitome of the saying "a little knowledge is a dangerous thing."

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

Here is my thought regarding the table structure:

1.&#65533; Employee Table:name, compensation, position, department
2.&#65533; Department Table: List of Departments
3.&#65533; Grant Table:&#65533; Start Date, End Date, amount, Grantor
4&#65533; Expense Table:&#65533; Expense, amount&#65533; Such as Rent&#65533; $1,000
5.&#65533; Position Funding Table:&#65533; On table for each position.&#65533; We have 58 staff positions, so 58 tables.&#65533; The table fields are Grant and amount.&#65533; So if Position 1, is funded by 3 grants, each grant is listed with the amount of funding.
6.&#65533; Expense Funding Table:&#65533; One table for each expense line item, with grant and amount fields.&#65533; 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.&#65533; Any suggestions would be greatly appreciated.

Thanks,
Mark

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



__________________________________________________________
53 Year Old Mom Looks 33
The Stunning Results of Her Wrinkle Trick Has Botox Doctors Worried
http://thirdpartyoffers.netzero.net/TGL3231/4f85dad4c3b203b47f1st04duc

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

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar