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


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.

--
Duane Hookom
Microsoft Access MVP


"nathanelz" wrote:

> 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
'Original Source: The Code Cage Forums http://www.thecodecage.com/forumz/access-tables/144005-database-design-help-newer-user.html#post528033
> > > 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
> > >
> >
> >
> >