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
|
|||
|
|||
Creating Relationships between tables
I've searched through these forums and read alot of discussions regarding not
using a lookup in a table. Instead, one should put that information in another table and then use a combo box (using the wizard) to create that field. I'm getting confused trying to figure out a few things with this method and I'm hoping for some direction. My database will be used to capture enforcement information regarding a particular crime in my area. This crime involved establishments, the criminals and certain information related to the criminals. Rather than lay out all of the specifics, if I can figure out how to relate two of the tables together, I should be fine. So, I would assume I need a table for the basic information: Tbl Incident Date of Offense Time of Offense Day of Week Establishment Name Subject Name Since I will be dealing with several establishments, I'm assuming I'd want this in a seperate table. I'd also want to include an address and a specific area for this: Tbl Establishment Establishment Name Establishment Address Establish Area I know I should be connecting the Tbl Establishment with the Tbl Incident and there should be like titles in both of the tables on which to match. I've looked at databases created by those with alot of knowledge in Access and I notice there's usually an ID field in each table (EstablishmentNameID - for instance). This is where the confusion is coming in: Should EstablishmentNameID be the primary key in this table as opposed to ID with autonumber? If it's not the primary key, I get an error message that there's no unique index between the two. Second point of confusion, assume the Establishment table merely contained the name of the establishment. By using the wizard, I put a combo box on the form to capture just the name and then tell it to store the establishment name in the Incident table (under Establishment Name) and that works fine. However, we have many of the same establishment names with different addresses and I need the address and areas included. I'm assuming the combo box is the wrong choice with this, as it only allows me to store the information from 1 field in my Incident table. I apologize for the lengthy post and I appreciate any assistance I can get. Thanks in advance. |
#2
|
|||
|
|||
Creating Relationships between tables
Lee Ann
I think you missed the suggestion ... You don't use the wizard to create the lookup in a field IN THE TABLE. You can use a wizard to create a control IN A FORM. That's the preferred approach. 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 pseudocode 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. "Lee Ann" wrote in message ... I've searched through these forums and read alot of discussions regarding not using a lookup in a table. Instead, one should put that information in another table and then use a combo box (using the wizard) to create that field. I'm getting confused trying to figure out a few things with this method and I'm hoping for some direction. My database will be used to capture enforcement information regarding a particular crime in my area. This crime involved establishments, the criminals and certain information related to the criminals. Rather than lay out all of the specifics, if I can figure out how to relate two of the tables together, I should be fine. So, I would assume I need a table for the basic information: Tbl Incident Date of Offense Time of Offense Day of Week Establishment Name Subject Name Since I will be dealing with several establishments, I'm assuming I'd want this in a seperate table. I'd also want to include an address and a specific area for this: Tbl Establishment Establishment Name Establishment Address Establish Area I know I should be connecting the Tbl Establishment with the Tbl Incident and there should be like titles in both of the tables on which to match. I've looked at databases created by those with alot of knowledge in Access and I notice there's usually an ID field in each table (EstablishmentNameID - for instance). This is where the confusion is coming in: Should EstablishmentNameID be the primary key in this table as opposed to ID with autonumber? If it's not the primary key, I get an error message that there's no unique index between the two. Second point of confusion, assume the Establishment table merely contained the name of the establishment. By using the wizard, I put a combo box on the form to capture just the name and then tell it to store the establishment name in the Incident table (under Establishment Name) and that works fine. However, we have many of the same establishment names with different addresses and I need the address and areas included. I'm assuming the combo box is the wrong choice with this, as it only allows me to store the information from 1 field in my Incident table. I apologize for the lengthy post and I appreciate any assistance I can get. Thanks in advance. |
#4
|
|||
|
|||
Creating Relationships between tables
Date of Offense - Time of Offense - Day of Week
Use single DateTime field and derive the parts as needed. Since I will be dealing with several establishments, I'm assuming I'd want this in a seperate table. No, one table for incidents. Use EstablishmentNameID as an Autonumber primary key field in the TblEstablishment and EstablishmentNameID - Number Long Integer - as foreign key in the incident table. Create a one-to-many relationship between TblEstablishment and incident, selecting Referential Integerity and Cascade Update. Use a combo on your incident form to pick the Establishment from TblEstablishment. -- Build a little, test a little. "Lee Ann" wrote: I've searched through these forums and read alot of discussions regarding not using a lookup in a table. Instead, one should put that information in another table and then use a combo box (using the wizard) to create that field. I'm getting confused trying to figure out a few things with this method and I'm hoping for some direction. My database will be used to capture enforcement information regarding a particular crime in my area. This crime involved establishments, the criminals and certain information related to the criminals. Rather than lay out all of the specifics, if I can figure out how to relate two of the tables together, I should be fine. So, I would assume I need a table for the basic information: Tbl Incident Date of Offense Time of Offense Day of Week Establishment Name Subject Name Since I will be dealing with several establishments, I'm assuming I'd want this in a seperate table. I'd also want to include an address and a specific area for this: Tbl Establishment Establishment Name Establishment Address Establish Area I know I should be connecting the Tbl Establishment with the Tbl Incident and there should be like titles in both of the tables on which to match. I've looked at databases created by those with alot of knowledge in Access and I notice there's usually an ID field in each table (EstablishmentNameID - for instance). This is where the confusion is coming in: Should EstablishmentNameID be the primary key in this table as opposed to ID with autonumber? If it's not the primary key, I get an error message that there's no unique index between the two. Second point of confusion, assume the Establishment table merely contained the name of the establishment. By using the wizard, I put a combo box on the form to capture just the name and then tell it to store the establishment name in the Incident table (under Establishment Name) and that works fine. However, we have many of the same establishment names with different addresses and I need the address and areas included. I'm assuming the combo box is the wrong choice with this, as it only allows me to store the information from 1 field in my Incident table. I apologize for the lengthy post and I appreciate any assistance I can get. Thanks in advance. |
#5
|
|||
|
|||
Creating Relationships between tables
I've followed the direction from Steve and Karl. Just a few follow-up
questions - I've set up tables according to Steve. When trying to link similar fields between the tables (EstablishmentID), it doesn't allow me to do it unless I set up the foreign key (if that's the correct word) as Number/Long Integer, as suggested by Karl. Is this always the way it's supposed to be done? If I don't use the number/LI, but yet have the same data type in both tables, it tells me it can't match because the data types are not the same. Thanks for the assistance - it seems the more I learn, the more questions there are. "KARL DEWEY" wrote: Date of Offense - Time of Offense - Day of Week Use single DateTime field and derive the parts as needed. Since I will be dealing with several establishments, I'm assuming I'd want this in a seperate table. No, one table for incidents. Use EstablishmentNameID as an Autonumber primary key field in the TblEstablishment and EstablishmentNameID - Number Long Integer - as foreign key in the incident table. Create a one-to-many relationship between TblEstablishment and incident, selecting Referential Integerity and Cascade Update. Use a combo on your incident form to pick the Establishment from TblEstablishment. -- Build a little, test a little. "Lee Ann" wrote: I've searched through these forums and read alot of discussions regarding not using a lookup in a table. Instead, one should put that information in another table and then use a combo box (using the wizard) to create that field. I'm getting confused trying to figure out a few things with this method and I'm hoping for some direction. My database will be used to capture enforcement information regarding a particular crime in my area. This crime involved establishments, the criminals and certain information related to the criminals. Rather than lay out all of the specifics, if I can figure out how to relate two of the tables together, I should be fine. So, I would assume I need a table for the basic information: Tbl Incident Date of Offense Time of Offense Day of Week Establishment Name Subject Name Since I will be dealing with several establishments, I'm assuming I'd want this in a seperate table. I'd also want to include an address and a specific area for this: Tbl Establishment Establishment Name Establishment Address Establish Area I know I should be connecting the Tbl Establishment with the Tbl Incident and there should be like titles in both of the tables on which to match. I've looked at databases created by those with alot of knowledge in Access and I notice there's usually an ID field in each table (EstablishmentNameID - for instance). This is where the confusion is coming in: Should EstablishmentNameID be the primary key in this table as opposed to ID with autonumber? If it's not the primary key, I get an error message that there's no unique index between the two. Second point of confusion, assume the Establishment table merely contained the name of the establishment. By using the wizard, I put a combo box on the form to capture just the name and then tell it to store the establishment name in the Incident table (under Establishment Name) and that works fine. However, we have many of the same establishment names with different addresses and I need the address and areas included. I'm assuming the combo box is the wrong choice with this, as it only allows me to store the information from 1 field in my Incident table. I apologize for the lengthy post and I appreciate any assistance I can get. Thanks in advance. |
#6
|
|||
|
|||
Creating Relationships between tables
Always use Autonumber for the primary key (table name + ID) and Number -
Long Integer for all foreign keys. When you create a new foreign key, notice that Access assigns 0 as the default value. I like to reset the default value to blank. When createing tables, I always put the primary key (table name + ID) as the first field. Foreign keys are then created in the field list from 2 on. Also 99.99% of the time I name the foreign key the same as its corresponding primary key. Doing this you can look at the design of a table and immediately pick out the primary key, all foreign keys and the tables that all foreign keys are related to (from the name!). Steve "Lee Ann" wrote in message ... I've followed the direction from Steve and Karl. Just a few follow-up questions - I've set up tables according to Steve. When trying to link similar fields between the tables (EstablishmentID), it doesn't allow me to do it unless I set up the foreign key (if that's the correct word) as Number/Long Integer, as suggested by Karl. Is this always the way it's supposed to be done? If I don't use the number/LI, but yet have the same data type in both tables, it tells me it can't match because the data types are not the same. Thanks for the assistance - it seems the more I learn, the more questions there are. "KARL DEWEY" wrote: Date of Offense - Time of Offense - Day of Week Use single DateTime field and derive the parts as needed. Since I will be dealing with several establishments, I'm assuming I'd want this in a seperate table. No, one table for incidents. Use EstablishmentNameID as an Autonumber primary key field in the TblEstablishment and EstablishmentNameID - Number Long Integer - as foreign key in the incident table. Create a one-to-many relationship between TblEstablishment and incident, selecting Referential Integerity and Cascade Update. Use a combo on your incident form to pick the Establishment from TblEstablishment. -- Build a little, test a little. "Lee Ann" wrote: I've searched through these forums and read alot of discussions regarding not using a lookup in a table. Instead, one should put that information in another table and then use a combo box (using the wizard) to create that field. I'm getting confused trying to figure out a few things with this method and I'm hoping for some direction. My database will be used to capture enforcement information regarding a particular crime in my area. This crime involved establishments, the criminals and certain information related to the criminals. Rather than lay out all of the specifics, if I can figure out how to relate two of the tables together, I should be fine. So, I would assume I need a table for the basic information: Tbl Incident Date of Offense Time of Offense Day of Week Establishment Name Subject Name Since I will be dealing with several establishments, I'm assuming I'd want this in a seperate table. I'd also want to include an address and a specific area for this: Tbl Establishment Establishment Name Establishment Address Establish Area I know I should be connecting the Tbl Establishment with the Tbl Incident and there should be like titles in both of the tables on which to match. I've looked at databases created by those with alot of knowledge in Access and I notice there's usually an ID field in each table (EstablishmentNameID - for instance). This is where the confusion is coming in: Should EstablishmentNameID be the primary key in this table as opposed to ID with autonumber? If it's not the primary key, I get an error message that there's no unique index between the two. Second point of confusion, assume the Establishment table merely contained the name of the establishment. By using the wizard, I put a combo box on the form to capture just the name and then tell it to store the establishment name in the Incident table (under Establishment Name) and that works fine. However, we have many of the same establishment names with different addresses and I need the address and areas included. I'm assuming the combo box is the wrong choice with this, as it only allows me to store the information from 1 field in my Incident table. I apologize for the lengthy post and I appreciate any assistance I can get. Thanks in advance. |
#7
|
|||
|
|||
Creating Relationships between tables
Lee Ann
At the risk of (re-)igniting "religious wars", I'll point out that arbitrary primary keys (e.g., Autonumber) work well when there is no reasonable "natural" key. An example of a reasonable natural key can be found in another newsgroup thread concerning using Stock Symbols as "natural" primary keys. 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 pseudocode 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. "Lee Ann" wrote in message ... I've followed the direction from Steve and Karl. Just a few follow-up questions - I've set up tables according to Steve. When trying to link similar fields between the tables (EstablishmentID), it doesn't allow me to do it unless I set up the foreign key (if that's the correct word) as Number/Long Integer, as suggested by Karl. Is this always the way it's supposed to be done? If I don't use the number/LI, but yet have the same data type in both tables, it tells me it can't match because the data types are not the same. Thanks for the assistance - it seems the more I learn, the more questions there are. "KARL DEWEY" wrote: Date of Offense - Time of Offense - Day of Week Use single DateTime field and derive the parts as needed. Since I will be dealing with several establishments, I'm assuming I'd want this in a seperate table. No, one table for incidents. Use EstablishmentNameID as an Autonumber primary key field in the TblEstablishment and EstablishmentNameID - Number Long Integer - as foreign key in the incident table. Create a one-to-many relationship between TblEstablishment and incident, selecting Referential Integerity and Cascade Update. Use a combo on your incident form to pick the Establishment from TblEstablishment. -- Build a little, test a little. "Lee Ann" wrote: I've searched through these forums and read alot of discussions regarding not using a lookup in a table. Instead, one should put that information in another table and then use a combo box (using the wizard) to create that field. I'm getting confused trying to figure out a few things with this method and I'm hoping for some direction. My database will be used to capture enforcement information regarding a particular crime in my area. This crime involved establishments, the criminals and certain information related to the criminals. Rather than lay out all of the specifics, if I can figure out how to relate two of the tables together, I should be fine. So, I would assume I need a table for the basic information: Tbl Incident Date of Offense Time of Offense Day of Week Establishment Name Subject Name Since I will be dealing with several establishments, I'm assuming I'd want this in a seperate table. I'd also want to include an address and a specific area for this: Tbl Establishment Establishment Name Establishment Address Establish Area I know I should be connecting the Tbl Establishment with the Tbl Incident and there should be like titles in both of the tables on which to match. I've looked at databases created by those with alot of knowledge in Access and I notice there's usually an ID field in each table (EstablishmentNameID - for instance). This is where the confusion is coming in: Should EstablishmentNameID be the primary key in this table as opposed to ID with autonumber? If it's not the primary key, I get an error message that there's no unique index between the two. Second point of confusion, assume the Establishment table merely contained the name of the establishment. By using the wizard, I put a combo box on the form to capture just the name and then tell it to store the establishment name in the Incident table (under Establishment Name) and that works fine. However, we have many of the same establishment names with different addresses and I need the address and areas included. I'm assuming the combo box is the wrong choice with this, as it only allows me to store the information from 1 field in my Incident table. I apologize for the lengthy post and I appreciate any assistance I can get. Thanks in advance. |
#8
|
|||
|
|||
Creating Relationships between tables
I too will risk igniting conflict by saying natural keys make perfect sense
in some situations. An autonumber is a good choice much of the time, but I wouldn't get locked into thinking it always needs to be used. An autonumber is a type of Long Integer field. Linking fields must be of the same data type (LI to LI, text to text, Double to Double, etc.), which is why Long Integer must be used to link to autonumber. Number field is a sort of umbrella term, but the specifics (Integer, Long Integer, Currency, Single, Double, etc.) must be the same. You can't link an integer to currency, as the latter may contain decimals and the former cannot. Choose a naming convention that works for you. I too tend to use the table name plus ID for the PK field. For instance, in tblEmployee, the PK is EmployeeID. In the linked table I usually use the first letter or two of the table name, then the linking field name. For instance, tblAward will have AwardID as the PK, and A_EmployeeID as the linking field, also known as a foreign key by some, but others hotly contest calling it a foreign key. The point of using a variant of the field name is that I find it easier to tell them apart in SQL and other code. This is my choice, but you may decide to make a different choice. It helps to be consistent, especially if you are working with others on the same project, but also for your own benefit when you revisit a project after a year or two. Lee Ann wrote: I've followed the direction from Steve and Karl. Just a few follow-up questions - I've set up tables according to Steve. When trying to link similar fields between the tables (EstablishmentID), it doesn't allow me to do it unless I set up the foreign key (if that's the correct word) as Number/Long Integer, as suggested by Karl. Is this always the way it's supposed to be done? If I don't use the number/LI, but yet have the same data type in both tables, it tells me it can't match because the data types are not the same. Thanks for the assistance - it seems the more I learn, the more questions there are. Date of Offense - Time of Offense - Day of Week Use single DateTime field and derive the parts as needed. [quoted text clipped - 63 lines] I apologize for the lengthy post and I appreciate any assistance I can get. Thanks in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201001/1 |
#9
|
|||
|
|||
Creating Relationships between tables
Thanks for all the replies. It's a little clearer now.
"BruceM via AccessMonster.com" wrote: I too will risk igniting conflict by saying natural keys make perfect sense in some situations. An autonumber is a good choice much of the time, but I wouldn't get locked into thinking it always needs to be used. An autonumber is a type of Long Integer field. Linking fields must be of the same data type (LI to LI, text to text, Double to Double, etc.), which is why Long Integer must be used to link to autonumber. Number field is a sort of umbrella term, but the specifics (Integer, Long Integer, Currency, Single, Double, etc.) must be the same. You can't link an integer to currency, as the latter may contain decimals and the former cannot. Choose a naming convention that works for you. I too tend to use the table name plus ID for the PK field. For instance, in tblEmployee, the PK is EmployeeID. In the linked table I usually use the first letter or two of the table name, then the linking field name. For instance, tblAward will have AwardID as the PK, and A_EmployeeID as the linking field, also known as a foreign key by some, but others hotly contest calling it a foreign key. The point of using a variant of the field name is that I find it easier to tell them apart in SQL and other code. This is my choice, but you may decide to make a different choice. It helps to be consistent, especially if you are working with others on the same project, but also for your own benefit when you revisit a project after a year or two. Lee Ann wrote: I've followed the direction from Steve and Karl. Just a few follow-up questions - I've set up tables according to Steve. When trying to link similar fields between the tables (EstablishmentID), it doesn't allow me to do it unless I set up the foreign key (if that's the correct word) as Number/Long Integer, as suggested by Karl. Is this always the way it's supposed to be done? If I don't use the number/LI, but yet have the same data type in both tables, it tells me it can't match because the data types are not the same. Thanks for the assistance - it seems the more I learn, the more questions there are. Date of Offense - Time of Offense - Day of Week Use single DateTime field and derive the parts as needed. [quoted text clipped - 63 lines] I apologize for the lengthy post and I appreciate any assistance I can get. Thanks in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201001/1 . |
Thread Tools | |
Display Modes | |
|
|