------ Register to get rid of these "In Post" ads! ------


It is highly recommended to noy use lookups to populate the foreign keys in
the tables!!!! You can find a discourse on this at www.mvps.org/access.
'Original Source: The Code Cage Forums http://www.thecodecage.com/forumz/access-tables/144005-database-design-help-newer-user.html#post526516

The simple way to populate the foreign keys in the tables is to use a
combobox or listbox.

Steve


"nathanelz" <nathanelz@discussions.microsoft.com> wrote in message
news:0728B1DD-BCBB-4E31-9E29-36644C869DDA@microsoft.com...
> Wow, this is really helpful Steve. Thanks for taking the time to look
> into
> this for me. I still don't quite understand the best way to populate the
> foreign keys in the tables that were mentioned. It seems like everyone is
> against the lookups. Any thoughts?
> Nathan
>
>
> "Steve" wrote:
>
>> Hi Nathan,
>>
>> Consider using the following tables ...........
>>
>> TblState
>> StateID
>> State
>>
>> TblDistrict
>> DistrictID
>> StateID
>> DistrictName
>>
>> TblCategory
>> CategoryID
>> Category
>>
>> TblEmploymentType
>> EmploymentTypeID
>> EmploymentType
>>
>> TblDistrictEmployee
>> DistrictEmployeeID
>> DistrictID
>> CategoryID
>> EmploymentTypeID
>> CountByCategory
>>
>> TblCostPool
>> CostPoolID
>> CostPool (CostPool1, CostPool2, etc)
>>
>> TblCostPoolByCategory
>> CostPoolByCategoryID
>> CategoryID
>> CostPoolID
>> QtrClosingDate
>>
>> TblExpenseType
>> ExpenseTypeID
>> ExpenseType
>>
>> TblTotalExpense
>> TotalExpenseID
>> DistrictID
>> CategoryID
>> ExpenseTypeID
>> QtrClosingDate
>> TotalExpense
>>
>> It's not clear what CostPool1 and CostPool2 are and how they relate to
>> total
>> expenses. If you provide more information, the tables can be further
>> refined.
>>
>> Steve
>> santus@penn.com
>>
>>
>>
>> "nathanelz" <nathanelz@discussions.microsoft.com> wrote in message
>> news:5F07CAB6-1B2C-4C67-A974-38E804C68312@microsoft.com...
>> > Hi,
>> > I'm kind of new to the forum... I posted several days ago. I'm not
>> > sure if older posts are read so I'm posting again and I've been able to
>> > figure out a few things since my last post.
>> > I'm trying to figure out the best way to do a database design.
>> > This
>> > is
>> > what I have so far and I'm trying to figure out how to link some of the
>> > tables. Here is what I now have at this point:
>> >
>> > DistrictTable:
>> > SchoolDistrictID - Primary Key
>> > State
>> > DistrictName (only specific districts can belong to a particular state)
>> >
>> > CategoryTable:
>> > CategoryAutoNumber - Primary Key
>> > SchoolDistrictID - Foreign Key
>> > Employment Type (Full time, part time, contract)
>> > QTR
>> > Cost Pool (cost pool 1, cost pool 2)
>> > Category (counselor, teacher, administrator, etc)
>> > Value (how many people work in the particular category)
>> >
>> > ExpenseTable:
>> > ExpenseAutoNumber - Primary Key
>> > DistrictID
>> > QTR
>> > ExpenseType (salary, benefits, etc)
>> > DollarValue (how much was spent)
>> >
>> > Can you please help me understand how to link the Expense table and the
>> > Category table. Please keep in mind that I have limited experience
>> > with
>> > Access. I figured that the School DistrictID - Foreign Key would help
>> > to
>> > link the District table and Category table. Also, I've been trying to
>> > read
>> > up on lookup fields and I'm so confused about how to handle these. The
>> > data
>> > will only be imported from excel files that we receive, not forms. I
>> > realize
>> > that the consensus is not to use lookup fields. How could I do this?
>> > Also,
>> > are the QTR fields that I have in the different tables necessary? I
>> > want
>> > the
>> > data to somehow be linked by qtr, so that I can figure out total
>> > expenses
>> > per
>> > category per QTR (just interested how to link the QTRs not necessarily
>> > the
>> > SQL behind the query, at this point). Any help would be greatly
>> > appreciated.
>> > Thanks a bunch.
>> > Nathan
>> >
>>
>>
>>