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.
"nathanelz" <(E-Mail Removed)> wrote in message
news:0728B1DD-BCBB-4E31-9E29-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>>
>>
>>
>> "nathanelz" <(E-Mail Removed)> wrote in message
>> news:5F07CAB6-1B2C-4C67-A974-(E-Mail Removed)...
>> > 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
>> >
>>
>>
>>
Similar Threads
