Relationships and lookup tables.
in [Getting Started]
|
Prev: IF HELP
Next: Database template
From: Ben Bamford on 3 Jul 2008 16:57 As a novice developer (and to this use of "communities"), I am hoping to gain guidance on the subject of this thread. I think I've understood "CC"'s question because it seems the same as mine and my experimentation with the answers has not produced a correct result. I have a Parent Table "Employee" with PK "EmpID" and 3 other fields in that table. I have a Related Table "EmployeeInfo" with PK "EmpID" and 1 other field ("Salary")in that table. I related "tblEmployee" to "tblEmployeeInfo", enforcing referential integrety, and cascade update and cascade delete. I entered 4 employee records in "tbl Employee" (all fields). The "EmpID" values in "tblEmployee" DO NOT appear in the "EmpID" field of "tblEmployeeInfo". Why not? Opening "tblEmployee" and expanding to see the subdatasheet of "tblEmployeeInfo", I see only the "Salary" field, not the "EmpID" field. If I enter a salary value in the subdatasheet, I do see the "EmpID" values in tblEmployeeInfo". I just do not get why I do not automatically see values in both tables when I enter them in the Parent table. I will continue to seek other resources to answer this, but this seems SO BASIC and I'm having such a hard time with it. -- B. Bamford "CC" wrote: > OK, I thought I had this straight after going through all the online > tutorials, but now I'm actually implementing, and I'm confused about > relationships and lookup fields. I'm doing an Orders database. I have: > > TblCustomers > TblOrders > TblOrderDetails > > OK, so these are all related. So in the Orders Table, there is a column for > Customers, to show which customer made that order, right? So I created a > CustomerID column in the Orders Table and one to many relationship to the > Customers Table for that. But I'm NOT supposed to set the properties of that > column to Lookup the Customers table? I'm supposed to let the data for that > field be entered by a Form? > > I guess the confusion is that I don't feel like the tables are really linked > unless I see that drop-down list in the table. But is that the wrong way to > think of it? I went through the MS online tutorials, and all their sample > databases are full of lookup fields. Is that wrong? >
From: Damon Heron on 4 Jul 2008 14:27 I don't see a thread for CC, but I think I can answer your question. If you have no data in the tblEmployeeInfo, then why would you expect to see a relation to the employeeID? You noted that when you enter salary in the tblEmpInfo, then the EmpID shows up. So there is your relation. Next, are you entering data from a form or directly to the table? If a form then you probably should have the mainform's record source as the tblEmployee, and add a subform with the source as tblEmployeeInfo. The link between the two is the EmpID. Finally, when you look at tables and related tables using the little plus sign, it doesn't show the EmpID, because the record you click on is associated with the EmpID. If the Info table is blank for that record, it still shows the blank record. Damon "Ben Bamford" <BenBamford(a)discussions.microsoft.com> wrote in message news:1706B910-217A-4B2D-8838-C9C140F2C302(a)microsoft.com... > As a novice developer (and to this use of "communities"), I am hoping to > gain > guidance on the subject of this thread. I think I've understood "CC"'s > question because it seems the same as mine and my experimentation with the > answers has not produced a correct result. > > I have a Parent Table "Employee" with PK "EmpID" and 3 other fields in > that > table. > I have a Related Table "EmployeeInfo" with PK "EmpID" and 1 other field > ("Salary")in that table. > I related "tblEmployee" to "tblEmployeeInfo", enforcing referential > integrety, and cascade update and cascade delete. > I entered 4 employee records in "tbl Employee" (all fields). > The "EmpID" values in "tblEmployee" DO NOT appear in the "EmpID" field of > "tblEmployeeInfo". Why not? > > Opening "tblEmployee" and expanding to see the subdatasheet of > "tblEmployeeInfo", I see only the "Salary" field, not the "EmpID" field. > If > I enter a salary value in the subdatasheet, I do see the "EmpID" values in > tblEmployeeInfo". > > I just do not get why I do not automatically see values in both tables > when > I enter them in the Parent table. I will continue to seek other resources > to > answer this, but this seems SO BASIC and I'm having such a hard time with > it. > > -- > B. Bamford > > > "CC" wrote: > >> OK, I thought I had this straight after going through all the online >> tutorials, but now I'm actually implementing, and I'm confused about >> relationships and lookup fields. I'm doing an Orders database. I have: >> >> TblCustomers >> TblOrders >> TblOrderDetails >> >> OK, so these are all related. So in the Orders Table, there is a column >> for >> Customers, to show which customer made that order, right? So I created a >> CustomerID column in the Orders Table and one to many relationship to the >> Customers Table for that. But I'm NOT supposed to set the properties of >> that >> column to Lookup the Customers table? I'm supposed to let the data for >> that >> field be entered by a Form? >> >> I guess the confusion is that I don't feel like the tables are really >> linked >> unless I see that drop-down list in the table. But is that the wrong way >> to >> think of it? I went through the MS online tutorials, and all their sample >> databases are full of lookup fields. Is that wrong? >>
From: Ben Bamford on 4 Jul 2008 16:58 Damon, Thanks for the response. The problem may lie in my assumption that if you use id fields (such as "EmpID" in both parent and child tables), then when you enter a value in "EmpID" in the parent table, you should expect to see that same value in the "EmpID" field in the related table. That is not the case and it seems as though it should be. When I create a form based on "tblEmployee" with a subform based on "tblEmployeeInfo", linked on the common "EmpID" field, then this does occur. When I navigate to each record in the main form, I see AT MINIMUM the corresponding "EmpID" value in the subform, even if I have not filled in other field values for that record. The corresponding "EmpID" values do not appear for each record when both tables are opened and examined. This is the source of my frustration. The "EmpID" field is a "text" datatype. If I skip a couple of records when I open "tblEmployee" and expand to view the subdatasheet of record 5 and enter a value for the "Salary" field (in "tblEmployeeInfo"), close both tables, then reopen both, this is what I see: I see that in "tblEmployeeInfo", three records with values for the "Salary" field. There are "EmpID" values of 0001, 0002 and 0005 in that table, ie there are no "placeholder values" of 0003 and 0004 showing (but those are present in the "tblEmployees"). It seems to me that, logically, there should be the same set of "EmpID" values in both tables, regardless of whether or not additoinal fields for those IDs are completed. What's the problem with my logic? Thanks. -- B. Bamford "Damon Heron" wrote: > I don't see a thread for CC, but I think I can answer your question. > If you have no data in the tblEmployeeInfo, then why would you expect to see > a relation to the employeeID? > You noted that when you enter salary in the tblEmpInfo, then the EmpID shows > up. So there is your relation. > Next, are you entering data from a form or directly to the table? If a form > then you probably should have the mainform's record source as the > tblEmployee, and add a subform with the source as tblEmployeeInfo. The link > between the two is the EmpID. > > Finally, when you look at tables and related tables using the little plus > sign, it doesn't show the EmpID, because the record you click on is > associated with the EmpID. If the Info table is blank for that record, it > still shows the blank record. > > Damon > > "Ben Bamford" <BenBamford(a)discussions.microsoft.com> wrote in message > news:1706B910-217A-4B2D-8838-C9C140F2C302(a)microsoft.com... > > As a novice developer (and to this use of "communities"), I am hoping to > > gain > > guidance on the subject of this thread. I think I've understood "CC"'s > > question because it seems the same as mine and my experimentation with the > > answers has not produced a correct result. > > > > I have a Parent Table "Employee" with PK "EmpID" and 3 other fields in > > that > > table. > > I have a Related Table "EmployeeInfo" with PK "EmpID" and 1 other field > > ("Salary")in that table. > > I related "tblEmployee" to "tblEmployeeInfo", enforcing referential > > integrety, and cascade update and cascade delete. > > I entered 4 employee records in "tbl Employee" (all fields). > > The "EmpID" values in "tblEmployee" DO NOT appear in the "EmpID" field of > > "tblEmployeeInfo". Why not? > > > > Opening "tblEmployee" and expanding to see the subdatasheet of > > "tblEmployeeInfo", I see only the "Salary" field, not the "EmpID" field. > > If > > I enter a salary value in the subdatasheet, I do see the "EmpID" values in > > tblEmployeeInfo". > > > > I just do not get why I do not automatically see values in both tables > > when > > I enter them in the Parent table. I will continue to seek other resources > > to > > answer this, but this seems SO BASIC and I'm having such a hard time with > > it. > > > > -- > > B. Bamford > > > > > > "CC" wrote: > > > >> OK, I thought I had this straight after going through all the online > >> tutorials, but now I'm actually implementing, and I'm confused about > >> relationships and lookup fields. I'm doing an Orders database. I have: > >> > >> TblCustomers > >> TblOrders > >> TblOrderDetails > >> > >> OK, so these are all related. So in the Orders Table, there is a column > >> for > >> Customers, to show which customer made that order, right? So I created a > >> CustomerID column in the Orders Table and one to many relationship to the > >> Customers Table for that. But I'm NOT supposed to set the properties of > >> that > >> column to Lookup the Customers table? I'm supposed to let the data for > >> that > >> field be entered by a Form? > >> > >> I guess the confusion is that I don't feel like the tables are really > >> linked > >> unless I see that drop-down list in the table. But is that the wrong way > >> to > >> think of it? I went through the MS online tutorials, and all their sample > >> databases are full of lookup fields. Is that wrong? > >> > > >
From: Damon Heron on 4 Jul 2008 19:33 Okay, lets take a step back. Relationships between tables can be one to one, one to many, or many to many. Most common are one to many. That means, for instance, that a customer table (the one side) could be linked to an Orders table (the many side) -- one customer may have many orders. In your example, you have employee table linked to employee info, which is salary. Can an employee have more than one salary? If not, then why is it in a separate table? Here is another way to design the tables: EmployeeTable EmpID EmpName Address Phone JobID (foreign key to JobTable) SalaryID (foreign key to SalaryTable) etc. JobTable JobID (Primary key) JobTitle Description etc.... SalaryTable SalaryID (Primary key) SalaryAmt In this case, if you have 10 employees, with differing jobs, you can assign SalaryIDs to each. Now, suppose there is a pay raise. With your original system, you would have to go thru each employee and adjust their salary individually. With this format, you only have to open the salary table and change the amount once. Similarly, if any employee switches jobs in the company, you just need to change his jobID. Perhaps you could benefit from looking at some examples of db models. Check out this website: http://www.databaseanswers.org/data_models/index.htm Oh, and the reason the empID is showing on your subform is because you are working on the main record. If you don't entry the subform and add a salary, then there is nothing to save, hence it is blank. Damon "Ben Bamford" <BenBamford(a)discussions.microsoft.com> wrote in message news:E935DE8A-9935-4C2A-9AF2-685A732A4CB6(a)microsoft.com... > Damon, > > Thanks for the response. The problem may lie in my assumption that if you > use id fields (such as "EmpID" in both parent and child tables), then when > you enter a value in "EmpID" in the parent table, you should expect to see > that same value in the "EmpID" field in the related table. That is not > the > case and it seems as though it should be. > > When I create a form based on "tblEmployee" with a subform based on > "tblEmployeeInfo", linked on the common "EmpID" field, then this does > occur. > When I navigate to each record in the main form, I see AT MINIMUM the > corresponding "EmpID" value in the subform, even if I have not filled in > other field values for that record. > > The corresponding "EmpID" values do not appear for each record when both > tables are opened and examined. This is the source of my frustration. > > The "EmpID" field is a "text" datatype. > > If I skip a couple of records when I open "tblEmployee" and expand to view > the subdatasheet of record 5 and enter a value for the "Salary" field (in > "tblEmployeeInfo"), close both tables, then reopen both, this is what I > see: > > I see that in "tblEmployeeInfo", three records with values for the > "Salary" > field. There are "EmpID" values of 0001, 0002 and 0005 in that table, ie > there are no "placeholder values" of 0003 and 0004 showing (but those are > present in the "tblEmployees"). It seems to me that, logically, there > should > be the same set of "EmpID" values in both tables, regardless of whether or > not additoinal fields for those IDs are completed. > > What's the problem with my logic? Thanks. > -- > B. Bamford > > > "Damon Heron" wrote: > >> I don't see a thread for CC, but I think I can answer your question. >> If you have no data in the tblEmployeeInfo, then why would you expect to >> see >> a relation to the employeeID? >> You noted that when you enter salary in the tblEmpInfo, then the EmpID >> shows >> up. So there is your relation. >> Next, are you entering data from a form or directly to the table? If a >> form >> then you probably should have the mainform's record source as the >> tblEmployee, and add a subform with the source as tblEmployeeInfo. The >> link >> between the two is the EmpID. >> >> Finally, when you look at tables and related tables using the little plus >> sign, it doesn't show the EmpID, because the record you click on is >> associated with the EmpID. If the Info table is blank for that record, >> it >> still shows the blank record. >> >> Damon >> >> "Ben Bamford" <BenBamford(a)discussions.microsoft.com> wrote in message >> news:1706B910-217A-4B2D-8838-C9C140F2C302(a)microsoft.com... >> > As a novice developer (and to this use of "communities"), I am hoping >> > to >> > gain >> > guidance on the subject of this thread. I think I've understood "CC"'s >> > question because it seems the same as mine and my experimentation with >> > the >> > answers has not produced a correct result. >> > >> > I have a Parent Table "Employee" with PK "EmpID" and 3 other fields in >> > that >> > table. >> > I have a Related Table "EmployeeInfo" with PK "EmpID" and 1 other field >> > ("Salary")in that table. >> > I related "tblEmployee" to "tblEmployeeInfo", enforcing referential >> > integrety, and cascade update and cascade delete. >> > I entered 4 employee records in "tbl Employee" (all fields). >> > The "EmpID" values in "tblEmployee" DO NOT appear in the "EmpID" field >> > of >> > "tblEmployeeInfo". Why not? >> > >> > Opening "tblEmployee" and expanding to see the subdatasheet of >> > "tblEmployeeInfo", I see only the "Salary" field, not the "EmpID" >> > field. >> > If >> > I enter a salary value in the subdatasheet, I do see the "EmpID" values >> > in >> > tblEmployeeInfo". >> > >> > I just do not get why I do not automatically see values in both tables >> > when >> > I enter them in the Parent table. I will continue to seek other >> > resources >> > to >> > answer this, but this seems SO BASIC and I'm having such a hard time >> > with >> > it. >> > >> > -- >> > B. Bamford >> > >> > >> > "CC" wrote: >> > >> >> OK, I thought I had this straight after going through all the online >> >> tutorials, but now I'm actually implementing, and I'm confused about >> >> relationships and lookup fields. I'm doing an Orders database. I have: >> >> >> >> TblCustomers >> >> TblOrders >> >> TblOrderDetails >> >> >> >> OK, so these are all related. So in the Orders Table, there is a >> >> column >> >> for >> >> Customers, to show which customer made that order, right? So I created >> >> a >> >> CustomerID column in the Orders Table and one to many relationship to >> >> the >> >> Customers Table for that. But I'm NOT supposed to set the properties >> >> of >> >> that >> >> column to Lookup the Customers table? I'm supposed to let the data for >> >> that >> >> field be entered by a Form? >> >> >> >> I guess the confusion is that I don't feel like the tables are really >> >> linked >> >> unless I see that drop-down list in the table. But is that the wrong >> >> way >> >> to >> >> think of it? I went through the MS online tutorials, and all their >> >> sample >> >> databases are full of lookup fields. Is that wrong? >> >> >> >> >>
From: Ben on 5 Jul 2008 08:52
I understand your point, however, there might be occasions when such 1:1 relationships occur for privacy reasons (splitting "private" information into a separate table) and providing a link to the "public" table. In any event, whether 1:1 or 1:M, my key frustration seems to be that I do not see ALL of the same "EmpID" values in both "tblEmployees" and "tblEmployeeInfo" (whether or not fields other than "EmpID" have any values entered -- in the "tblEmployeeInfo"). Maybe it's this idea I have that there should at least be a "placeholder" in evidence in the subordinate table to ensure that one can visually verify the match between EmpID values in the tblEmployees and EmpID values in the tblEmpInformation. As to your comment about the main form and subform, I get exactly what I expect when I navigate through the records in the main form (I see ALL EmpID values in both tables, even if additional field values have not been entered into the subform or subordinate table). So, I am still left with my issue, but I do thank you for the resource link. "Damon Heron" wrote: > Okay, lets take a step back. Relationships between tables can be one to > one, one to many, or many to many. Most common are one to many. That > means, for instance, that a customer table (the one side) could be linked to > an Orders table (the many side) -- one customer may have many orders. > In your example, you have employee table linked to employee info, which is > salary. Can an employee have more than one salary? > If not, then why is it in a separate table? > Here is another way to design the tables: > > EmployeeTable > EmpID > EmpName > Address > Phone > JobID (foreign key to JobTable) > SalaryID (foreign key to SalaryTable) > etc. > > JobTable > JobID (Primary key) > JobTitle > Description > etc.... > > SalaryTable > SalaryID (Primary key) > SalaryAmt > > In this case, if you have 10 employees, with differing jobs, you can assign > SalaryIDs to each. Now, suppose there is a pay raise. With your original > system, you would have to go thru each employee and adjust their salary > individually. With this format, you only have to open the salary table and > change the amount once. Similarly, if any employee switches jobs in the > company, you just need to change his jobID. > > Perhaps you could benefit from looking at some examples of db models. Check > out this website: > http://www.databaseanswers.org/data_models/index.htm > > Oh, and the reason the empID is showing on your subform is because you are > working on the main record. If you don't entry the subform and add a > salary, then there is nothing to save, hence it is blank. > > Damon > > > "Ben Bamford" <BenBamford(a)discussions.microsoft.com> wrote in message > news:E935DE8A-9935-4C2A-9AF2-685A732A4CB6(a)microsoft.com... > > Damon, > > > > Thanks for the response. The problem may lie in my assumption that if you > > use id fields (such as "EmpID" in both parent and child tables), then when > > you enter a value in "EmpID" in the parent table, you should expect to see > > that same value in the "EmpID" field in the related table. That is not > > the > > case and it seems as though it should be. > > > > When I create a form based on "tblEmployee" with a subform based on > > "tblEmployeeInfo", linked on the common "EmpID" field, then this does > > occur. > > When I navigate to each record in the main form, I see AT MINIMUM the > > corresponding "EmpID" value in the subform, even if I have not filled in > > other field values for that record. > > > > The corresponding "EmpID" values do not appear for each record when both > > tables are opened and examined. This is the source of my frustration. > > > > The "EmpID" field is a "text" datatype. > > > > If I skip a couple of records when I open "tblEmployee" and expand to view > > the subdatasheet of record 5 and enter a value for the "Salary" field (in > > "tblEmployeeInfo"), close both tables, then reopen both, this is what I > > see: > > > > I see that in "tblEmployeeInfo", three records with values for the > > "Salary" > > field. There are "EmpID" values of 0001, 0002 and 0005 in that table, ie > > there are no "placeholder values" of 0003 and 0004 showing (but those are > > present in the "tblEmployees"). It seems to me that, logically, there > > should > > be the same set of "EmpID" values in both tables, regardless of whether or > > not additoinal fields for those IDs are completed. > > > > What's the problem with my logic? Thanks. > > -- > > B. Bamford > > > > > > "Damon Heron" wrote: > > > >> I don't see a thread for CC, but I think I can answer your question. > >> If you have no data in the tblEmployeeInfo, then why would you expect to > >> see > >> a relation to the employeeID? > >> You noted that when you enter salary in the tblEmpInfo, then the EmpID > >> shows > >> up. So there is your relation. > >> Next, are you entering data from a form or directly to the table? If a > >> form > >> then you probably should have the mainform's record source as the > >> tblEmployee, and add a subform with the source as tblEmployeeInfo. The > >> link > >> between the two is the EmpID. > >> > >> Finally, when you look at tables and related tables using the little plus > >> sign, it doesn't show the EmpID, because the record you click on is > >> associated with the EmpID. If the Info table is blank for that record, > >> it > >> still shows the blank record. > >> > >> Damon > >> > >> "Ben Bamford" <BenBamford(a)discussions.microsoft.com> wrote in message > >> news:1706B910-217A-4B2D-8838-C9C140F2C302(a)microsoft.com... > >> > As a novice developer (and to this use of "communities"), I am hoping > >> > to > >> > gain > >> > guidance on the subject of this thread. I think I've understood "CC"'s > >> > question because it seems the same as mine and my experimentation with > >> > the > >> > answers has not produced a correct result. > >> > > >> > I have a Parent Table "Employee" with PK "EmpID" and 3 other fields in > >> > that > >> > table. > >> > I have a Related Table "EmployeeInfo" with PK "EmpID" and 1 other field > >> > ("Salary")in that table. > >> > I related "tblEmployee" to "tblEmployeeInfo", enforcing referential > >> > integrety, and cascade update and cascade delete. > >> > I entered 4 employee records in "tbl Employee" (all fields). > >> > The "EmpID" values in "tblEmployee" DO NOT appear in the "EmpID" field > >> > of > >> > "tblEmployeeInfo". Why not? > >> > > >> > Opening "tblEmployee" and expanding to see the subdatasheet of > >> > "tblEmployeeInfo", I see only the "Salary" field, not the "EmpID" > >> > field. > >> > If > >> > I enter a salary value in the subdatasheet, I do see the "EmpID" values > >> > in > >> > tblEmployeeInfo". > >> > > >> > I just do not get why I do not automatically see values in both tables > >> > when > >> > I enter them in the Parent table. I will continue to seek other > >> > resources > >> > to > >> > answer this, but this seems SO BASIC and I'm having such a hard time > >> > with > >> > it. > >> > > >> > -- > >> > B. Bamford > >> > > >> > > >> > "CC" wrote: > >> > > >> >> OK, I thought I had this straight after going through all the online > >> >> tutorials, but now I'm actually implementing, and I'm confused about > >> >> relationships and lookup fields. I'm doing an Orders database. I have: > >> >> > >> >> TblCustomers > >> >> TblOrders > >> >> TblOrderDetails > >> >> > >> >> OK, so these are all related. So in the Orders Table, there is a > >> >> column > >> >> for > >> >> Customers, to show which customer made that order, right? So I created > >> >> a > >> >> CustomerID column in the Orders Table and one to many relationship to > >> >> the > >> >> Customers Table for that. But I'm NOT supposed to set the properties > >> >> of > >> >> that > >> >> column to Lookup the Customers table? I'm supposed to let the data for > >> >> that > >> >> field be entered by a Form? > >> >> > >> >> I guess the confusion is that I don't feel like the tables are really > >> >> linked > >> >> unless I see that drop-down list in the table. But is that the wrong > >> >> way > >> >> to > >> >> think of it? I went through the MS online tutorials, and all their > >> >> sample > >> >> databases are full of lookup fields. Is that wrong? > >> >> > >> > >> > >> > > > |