Database Design help - Newer user

nathanelz · External Since: Oct 13, 2009 Posts: 2

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

Steve · External Since: Aug 04, 2009 Posts: 47

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 DeleteThis @penn.com

"nathanelz" <nathanelz DeleteThis @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
>

StopThisAdvertising · External Since: Mar 26, 2005 Posts: 203

"Steve" <notmyemail DeleteThis @address.com> schreef in bericht
news:8K2dnaKmb91riEjXnZ2dnUVZ_g6dnZ2d@earthlink.com...
> 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 DeleteThis @penn.com
>
>

--
Get lost $teve. Go away... far away....
No-one wants you here... no-one needs you here...

OP look at http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'... we have passed 10.000 pageloads... it's
a shame !!)

For those who don't 'agree' with this mail , because $teve was 'helpfull' with his post...
We warned him a thousand times... Sad, but he is not willing to stop advertising...

He is just toying with these groups... advertising like hell... on and on... for years...
oh yes... and sometimes he answers questions... indeed...
and sometimes good souls here give him credit for that...

==> We are totally 'finished' with $teve now...
==> Killfile 'StopThisAdvertising' and you won't see these mails....

Arno R

seed · External Since: Oct 14, 2009 Posts: 1

ooo...helpfull info

--
seed
------------------------------------------------------------------------
seed's Profile: http://www.thecodecage.com/forumz/member.php?userid=1037
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=144005

nathanelz · External Since: Oct 13, 2009 Posts: 2

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 DeleteThis @penn.com
>
>
>
> "nathanelz" <nathanelz DeleteThis @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
> >
>
>
>

Jeff Boyce · External Since: Nov 04, 2004 Posts: 2342

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" <nathanelz DeleteThis @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 DeleteThis @penn.com
>>
>>
>>
>> "nathanelz" <nathanelz DeleteThis @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
>> >
>>
>>
>>

Steve · External Since: Aug 04, 2009 Posts: 47

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.

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

Steve

"nathanelz" <nathanelz.RemoveThis@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.RemoveThis@penn.com
>>
>>
>>
>> "nathanelz" <nathanelz.RemoveThis@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
>> >
>>
>>
>>

Duane Hookom · External Since: Feb 07, 2005 Posts: 1359

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.DeleteThis@penn.com
> >
> >
> >
> > "nathanelz" <nathanelz.DeleteThis@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
> > >
> >
> >
> >