Database Design help - Newer user

[nathanelz]

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

 

[nathanelz]

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

 

[Jeff Boyce]

The comments you see about "lookups" refer to using the lookup datatype in a
table definition.

Access tables store data, Access forms display data. Use the forms!

(and in forms, comboboxes do a great job of giving a user a way to "lookup"
something. You feed the combobox using a query against a table that holds
valid values ... and that table is considered a "lookup table". Not a
lookup field, a table.)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Duane Hookom]

You populate foreign keys by making selections in combo boxes on forms bound
to your tables/queries. Lookups are good and necessary, just not defined as
lookup fields in tables.