|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 14th, 2009, 12:06 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
Database Design help - Newer user
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 
|
|
#3
October 14th, 2009, 07:54 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database Design help - Newer user
"Steve" schreef in bericht m... 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 -- 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
|
|
#4
October 15th, 2009, 04:20 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database Design help - Newer user
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 "nathanelz" wrote in message ... 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
|
|
#5
October 15th, 2009, 07:24 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database Design help - Newer user
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" wrote in message ... 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 "nathanelz" wrote in message ... 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
|
|
#6
October 15th, 2009, 07:28 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database Design help - Newer user
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" wrote in message ... 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 "nathanelz" wrote in message ... 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
|
|
#7
October 16th, 2009, 08:08 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Database Design help - Newer user
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 "nathanelz" wrote in message ... 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
