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
|
|||
|
|||
multiple key fields in parent table and combo boxes for foreign ke
I have the following tables and keys and am seeking a way to easily populate
Table 2 with values from Table 1. Table 1: PK1, PK2, F1, F2, ... Table 2: FK1, FK2, PK3, F1, F2, ... PK1 and PK2 are primary key fields in Table 1. They are indexed with Duplicates OK. FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2 are foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and inconsequential non-key fields in each table. [Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table 1].PK2 has a 1:N relationship to [Table 2].FK2. I set the Lookup Display Control to Combo Box for the foreign key fields [Table 2].FK1 and [Table 2].FK2, and I would like each these combo boxes to separately display the values available in the parent table fields to which they are linked. This is not a problem for [Table 2].FK1 - I simply specify Table 1 as the Row Source and retain the default Bound Column value of 1. However, I am unable to get [Table 2].FK2 to behave the same way. If I set Bound Column and Column Count to 2 I see both columns in the combo box, and I can seemingly select something that populates the field without violating referential integrity, but a PK1 value is displayed and not a PK2 value. This makes no sense to me. My typical workaround is to create a query that yields PK2 values, and I set the Row Source for [Table 2].FK2 to this query. It doesn't seem right that I should have to do this, so I suspect that I am missing something quite obvious. |
#2
|
|||
|
|||
multiple key fields in parent table and combo boxes for foreign ke
I'm not sure I'm following your description (but maybe I'm being too literal
g)... A table would only have one PK, although you could create a multi-field primary key. A table with "duplicates OK" on its PK would be ... better off without a PK! A PK is supposed to uniquely identify a row, so there would NEVER be any duplicates. If you are describing the use of Lookup data types in your tables, please reconsider. This approach, while well-intentioned, can cause considerable confusion for those who try to understand it later (including yourself!). Perhaps I'm just getting lost with all the PK/F/... -- do you have a few 'rows' of data as an example, so we can get some idea what domain you're working in? Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... I have the following tables and keys and am seeking a way to easily populate Table 2 with values from Table 1. Table 1: PK1, PK2, F1, F2, ... Table 2: FK1, FK2, PK3, F1, F2, ... PK1 and PK2 are primary key fields in Table 1. They are indexed with Duplicates OK. FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2 are foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and inconsequential non-key fields in each table. [Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table 1].PK2 has a 1:N relationship to [Table 2].FK2. I set the Lookup Display Control to Combo Box for the foreign key fields [Table 2].FK1 and [Table 2].FK2, and I would like each these combo boxes to separately display the values available in the parent table fields to which they are linked. This is not a problem for [Table 2].FK1 - I simply specify Table 1 as the Row Source and retain the default Bound Column value of 1. However, I am unable to get [Table 2].FK2 to behave the same way. If I set Bound Column and Column Count to 2 I see both columns in the combo box, and I can seemingly select something that populates the field without violating referential integrity, but a PK1 value is displayed and not a PK2 value. This makes no sense to me. My typical workaround is to create a query that yields PK2 values, and I set the Row Source for [Table 2].FK2 to this query. It doesn't seem right that I should have to do this, so I suspect that I am missing something quite obvious. |
#3
|
|||
|
|||
multiple key fields in parent table and combo boxes for foreig
Jeff:
Sorry, I don't think I conveyed my question well enough. Let me try to elaborate. Firstly, I am of the "natural key field" school. I rarely use an autonumber field as a key. Instead, I select fields that naturally serve to uniquely identify rows. Looking at it simplistically, a table that identifies a person by name (table [Person]) would have two key fields, one for first name and a second for last name. Duplicates should certainly be allowed for each of these key fields individually, but taken together they are obviously unique (ignoring the fact that two people can have the same first and last names). As an example, assume I want a table that documents a person's job history ([Person Job History]). This table would have the same primary keys as the [Person] table above (separate fields for first name and last name), but would also have a third primary key field, probably for date of employment. [Person] FirstName (PK) LastName (PK) [Person Job History] FirstName (PK) LastName (PK) DateOfEmployment (PK) Employer A one-to-many relationship with referential integrity would exist between the key fields of [Person] and the matching key fields in [Person Job History]. In filling out this table with a form, I would want to be able to use a combo box that pulls down available values from the [Person] table for first name and last name, separately of course. My problem lies with the combo box for the LastName field in [Person Job History], specifically, the second column position of the LastName field in the [Person] table. The combo box for the FirstName field uses the first column of [Person] with no problem. If I set the BoundColumn and COlumnCount to 2 for the combo box for the LastName field I can successfully populate [Person Job History], but the values that appear in this field are first names and not last names. This is not at all desirable. My workaround is to create a query on [Person] that simply lists the last name in the first column, and to set this query as the Row Source for the combo box for LastName in [Person Job History]. This seems silly to have to do. I'm wondering if there is a different way to solve this problem. Thanks! "Jeff Boyce" wrote: I'm not sure I'm following your description (but maybe I'm being too literal g)... A table would only have one PK, although you could create a multi-field primary key. A table with "duplicates OK" on its PK would be ... better off without a PK! A PK is supposed to uniquely identify a row, so there would NEVER be any duplicates. If you are describing the use of Lookup data types in your tables, please reconsider. This approach, while well-intentioned, can cause considerable confusion for those who try to understand it later (including yourself!). Perhaps I'm just getting lost with all the PK/F/... -- do you have a few 'rows' of data as an example, so we can get some idea what domain you're working in? Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... I have the following tables and keys and am seeking a way to easily populate Table 2 with values from Table 1. Table 1: PK1, PK2, F1, F2, ... Table 2: FK1, FK2, PK3, F1, F2, ... PK1 and PK2 are primary key fields in Table 1. They are indexed with Duplicates OK. FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2 are foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and inconsequential non-key fields in each table. [Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table 1].PK2 has a 1:N relationship to [Table 2].FK2. I set the Lookup Display Control to Combo Box for the foreign key fields [Table 2].FK1 and [Table 2].FK2, and I would like each these combo boxes to separately display the values available in the parent table fields to which they are linked. This is not a problem for [Table 2].FK1 - I simply specify Table 1 as the Row Source and retain the default Bound Column value of 1. However, I am unable to get [Table 2].FK2 to behave the same way. If I set Bound Column and Column Count to 2 I see both columns in the combo box, and I can seemingly select something that populates the field without violating referential integrity, but a PK1 value is displayed and not a PK2 value. This makes no sense to me. My typical workaround is to create a query that yields PK2 values, and I set the Row Source for [Table 2].FK2 to this query. It doesn't seem right that I should have to do this, so I suspect that I am missing something quite obvious. |
#4
|
|||
|
|||
multiple key fields in parent table and combo boxes for foreig
The natural vs. arbitrary key discussion can devolve into religious wars, so
let's not go there! I'm curious why you would want to select LastName and FirstName separately for your Job History. Aren't you actually collecting information about the Person? What about using a query to concatenate these two and letting your users pick the correct person? You could still load those two separate values into the Job History table, but your users might find it easier. Note: I have several (some quite painful) experiences with two folks having the same name (and even living in the same house). You might be operating in a VERY limited domain in which this could never happen ... GOOD LUCK! If you ever do have two people with the same name, are you going to expand the number of fields you use to uniquely identify an individual? (this is one of the situations in which an "unnatural" key may have fewer problems in "behind the curtain" implementation -- of course, your users would never need to know...). Good luck Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... Jeff: Sorry, I don't think I conveyed my question well enough. Let me try to elaborate. Firstly, I am of the "natural key field" school. I rarely use an autonumber field as a key. Instead, I select fields that naturally serve to uniquely identify rows. Looking at it simplistically, a table that identifies a person by name (table [Person]) would have two key fields, one for first name and a second for last name. Duplicates should certainly be allowed for each of these key fields individually, but taken together they are obviously unique (ignoring the fact that two people can have the same first and last names). As an example, assume I want a table that documents a person's job history ([Person Job History]). This table would have the same primary keys as the [Person] table above (separate fields for first name and last name), but would also have a third primary key field, probably for date of employment. [Person] FirstName (PK) LastName (PK) [Person Job History] FirstName (PK) LastName (PK) DateOfEmployment (PK) Employer A one-to-many relationship with referential integrity would exist between the key fields of [Person] and the matching key fields in [Person Job History]. In filling out this table with a form, I would want to be able to use a combo box that pulls down available values from the [Person] table for first name and last name, separately of course. My problem lies with the combo box for the LastName field in [Person Job History], specifically, the second column position of the LastName field in the [Person] table. The combo box for the FirstName field uses the first column of [Person] with no problem. If I set the BoundColumn and COlumnCount to 2 for the combo box for the LastName field I can successfully populate [Person Job History], but the values that appear in this field are first names and not last names. This is not at all desirable. My workaround is to create a query on [Person] that simply lists the last name in the first column, and to set this query as the Row Source for the combo box for LastName in [Person Job History]. This seems silly to have to do. I'm wondering if there is a different way to solve this problem. Thanks! "Jeff Boyce" wrote: I'm not sure I'm following your description (but maybe I'm being too literal g)... A table would only have one PK, although you could create a multi-field primary key. A table with "duplicates OK" on its PK would be ... better off without a PK! A PK is supposed to uniquely identify a row, so there would NEVER be any duplicates. If you are describing the use of Lookup data types in your tables, please reconsider. This approach, while well-intentioned, can cause considerable confusion for those who try to understand it later (including yourself!). Perhaps I'm just getting lost with all the PK/F/... -- do you have a few 'rows' of data as an example, so we can get some idea what domain you're working in? Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... I have the following tables and keys and am seeking a way to easily populate Table 2 with values from Table 1. Table 1: PK1, PK2, F1, F2, ... Table 2: FK1, FK2, PK3, F1, F2, ... PK1 and PK2 are primary key fields in Table 1. They are indexed with Duplicates OK. FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2 are foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and inconsequential non-key fields in each table. [Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table 1].PK2 has a 1:N relationship to [Table 2].FK2. I set the Lookup Display Control to Combo Box for the foreign key fields [Table 2].FK1 and [Table 2].FK2, and I would like each these combo boxes to separately display the values available in the parent table fields to which they are linked. This is not a problem for [Table 2].FK1 - I simply specify Table 1 as the Row Source and retain the default Bound Column value of 1. However, I am unable to get [Table 2].FK2 to behave the same way. If I set Bound Column and Column Count to 2 I see both columns in the combo box, and I can seemingly select something that populates the field without violating referential integrity, but a PK1 value is displayed and not a PK2 value. This makes no sense to me. My typical workaround is to create a query that yields PK2 values, and I set the Row Source for [Table 2].FK2 to this query. It doesn't seem right that I should have to do this, so I suspect that I am missing something quite obvious. |
#5
|
|||
|
|||
multiple key fields in parent table and combo boxes for foreig
Sorry for not being clearer, but this is an entirely fictitious pair of
tables that I created only to illustrate the problem I have with the combo box functionality. My actual tables truly need the multiple key fields, but they would have only presented unnecessary confusion if I had described them instead. You suggest using a query to concatenate the first and last names for selection purposes, with VBA functionality behind it I assume to place the separate values in the table fields. This is a nice idea that would halve the effort on the user's part in doing data entry for these fields. This is not the problem that I thought I was trying to solve, but you have changed my way of looking at this issue and I may very well give it a try. However, I still hope there is a cleaner way of handling combo box lookups on fields that are not in the first column of the parent table. "Jeff Boyce" wrote: The natural vs. arbitrary key discussion can devolve into religious wars, so let's not go there! I'm curious why you would want to select LastName and FirstName separately for your Job History. Aren't you actually collecting information about the Person? What about using a query to concatenate these two and letting your users pick the correct person? You could still load those two separate values into the Job History table, but your users might find it easier. Note: I have several (some quite painful) experiences with two folks having the same name (and even living in the same house). You might be operating in a VERY limited domain in which this could never happen ... GOOD LUCK! If you ever do have two people with the same name, are you going to expand the number of fields you use to uniquely identify an individual? (this is one of the situations in which an "unnatural" key may have fewer problems in "behind the curtain" implementation -- of course, your users would never need to know...). Good luck Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... Jeff: Sorry, I don't think I conveyed my question well enough. Let me try to elaborate. Firstly, I am of the "natural key field" school. I rarely use an autonumber field as a key. Instead, I select fields that naturally serve to uniquely identify rows. Looking at it simplistically, a table that identifies a person by name (table [Person]) would have two key fields, one for first name and a second for last name. Duplicates should certainly be allowed for each of these key fields individually, but taken together they are obviously unique (ignoring the fact that two people can have the same first and last names). As an example, assume I want a table that documents a person's job history ([Person Job History]). This table would have the same primary keys as the [Person] table above (separate fields for first name and last name), but would also have a third primary key field, probably for date of employment. [Person] FirstName (PK) LastName (PK) [Person Job History] FirstName (PK) LastName (PK) DateOfEmployment (PK) Employer A one-to-many relationship with referential integrity would exist between the key fields of [Person] and the matching key fields in [Person Job History]. In filling out this table with a form, I would want to be able to use a combo box that pulls down available values from the [Person] table for first name and last name, separately of course. My problem lies with the combo box for the LastName field in [Person Job History], specifically, the second column position of the LastName field in the [Person] table. The combo box for the FirstName field uses the first column of [Person] with no problem. If I set the BoundColumn and COlumnCount to 2 for the combo box for the LastName field I can successfully populate [Person Job History], but the values that appear in this field are first names and not last names. This is not at all desirable. My workaround is to create a query on [Person] that simply lists the last name in the first column, and to set this query as the Row Source for the combo box for LastName in [Person Job History]. This seems silly to have to do. I'm wondering if there is a different way to solve this problem. Thanks! "Jeff Boyce" wrote: I'm not sure I'm following your description (but maybe I'm being too literal g)... A table would only have one PK, although you could create a multi-field primary key. A table with "duplicates OK" on its PK would be ... better off without a PK! A PK is supposed to uniquely identify a row, so there would NEVER be any duplicates. If you are describing the use of Lookup data types in your tables, please reconsider. This approach, while well-intentioned, can cause considerable confusion for those who try to understand it later (including yourself!). Perhaps I'm just getting lost with all the PK/F/... -- do you have a few 'rows' of data as an example, so we can get some idea what domain you're working in? Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... I have the following tables and keys and am seeking a way to easily populate Table 2 with values from Table 1. Table 1: PK1, PK2, F1, F2, ... Table 2: FK1, FK2, PK3, F1, F2, ... PK1 and PK2 are primary key fields in Table 1. They are indexed with Duplicates OK. FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2 are foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and inconsequential non-key fields in each table. [Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table 1].PK2 has a 1:N relationship to [Table 2].FK2. I set the Lookup Display Control to Combo Box for the foreign key fields [Table 2].FK1 and [Table 2].FK2, and I would like each these combo boxes to separately display the values available in the parent table fields to which they are linked. This is not a problem for [Table 2].FK1 - I simply specify Table 1 as the Row Source and retain the default Bound Column value of 1. However, I am unable to get [Table 2].FK2 to behave the same way. If I set Bound Column and Column Count to 2 I see both columns in the combo box, and I can seemingly select something that populates the field without violating referential integrity, but a PK1 value is displayed and not a PK2 value. This makes no sense to me. My typical workaround is to create a query that yields PK2 values, and I set the Row Source for [Table 2].FK2 to this query. It doesn't seem right that I should have to do this, so I suspect that I am missing something quite obvious. |
#6
|
|||
|
|||
multiple key fields in parent table and combo boxes for foreig
If you are describing an "in-the-table" use of lookup datatypes, please stop
NOW! What you described (using other than ...) is one of the issues that crops up trying to do data entry/validation/lookup via tables instead of forms, where it belongs. In a form, a combobox can bind any column you choose, and can display any column(s) you choose. Check the properties on the combobox control in the form... Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... Sorry for not being clearer, but this is an entirely fictitious pair of tables that I created only to illustrate the problem I have with the combo box functionality. My actual tables truly need the multiple key fields, but they would have only presented unnecessary confusion if I had described them instead. You suggest using a query to concatenate the first and last names for selection purposes, with VBA functionality behind it I assume to place the separate values in the table fields. This is a nice idea that would halve the effort on the user's part in doing data entry for these fields. This is not the problem that I thought I was trying to solve, but you have changed my way of looking at this issue and I may very well give it a try. However, I still hope there is a cleaner way of handling combo box lookups on fields that are not in the first column of the parent table. "Jeff Boyce" wrote: The natural vs. arbitrary key discussion can devolve into religious wars, so let's not go there! I'm curious why you would want to select LastName and FirstName separately for your Job History. Aren't you actually collecting information about the Person? What about using a query to concatenate these two and letting your users pick the correct person? You could still load those two separate values into the Job History table, but your users might find it easier. Note: I have several (some quite painful) experiences with two folks having the same name (and even living in the same house). You might be operating in a VERY limited domain in which this could never happen ... GOOD LUCK! If you ever do have two people with the same name, are you going to expand the number of fields you use to uniquely identify an individual? (this is one of the situations in which an "unnatural" key may have fewer problems in "behind the curtain" implementation -- of course, your users would never need to know...). Good luck Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... Jeff: Sorry, I don't think I conveyed my question well enough. Let me try to elaborate. Firstly, I am of the "natural key field" school. I rarely use an autonumber field as a key. Instead, I select fields that naturally serve to uniquely identify rows. Looking at it simplistically, a table that identifies a person by name (table [Person]) would have two key fields, one for first name and a second for last name. Duplicates should certainly be allowed for each of these key fields individually, but taken together they are obviously unique (ignoring the fact that two people can have the same first and last names). As an example, assume I want a table that documents a person's job history ([Person Job History]). This table would have the same primary keys as the [Person] table above (separate fields for first name and last name), but would also have a third primary key field, probably for date of employment. [Person] FirstName (PK) LastName (PK) [Person Job History] FirstName (PK) LastName (PK) DateOfEmployment (PK) Employer A one-to-many relationship with referential integrity would exist between the key fields of [Person] and the matching key fields in [Person Job History]. In filling out this table with a form, I would want to be able to use a combo box that pulls down available values from the [Person] table for first name and last name, separately of course. My problem lies with the combo box for the LastName field in [Person Job History], specifically, the second column position of the LastName field in the [Person] table. The combo box for the FirstName field uses the first column of [Person] with no problem. If I set the BoundColumn and COlumnCount to 2 for the combo box for the LastName field I can successfully populate [Person Job History], but the values that appear in this field are first names and not last names. This is not at all desirable. My workaround is to create a query on [Person] that simply lists the last name in the first column, and to set this query as the Row Source for the combo box for LastName in [Person Job History]. This seems silly to have to do. I'm wondering if there is a different way to solve this problem. Thanks! "Jeff Boyce" wrote: I'm not sure I'm following your description (but maybe I'm being too literal g)... A table would only have one PK, although you could create a multi-field primary key. A table with "duplicates OK" on its PK would be ... better off without a PK! A PK is supposed to uniquely identify a row, so there would NEVER be any duplicates. If you are describing the use of Lookup data types in your tables, please reconsider. This approach, while well-intentioned, can cause considerable confusion for those who try to understand it later (including yourself!). Perhaps I'm just getting lost with all the PK/F/... -- do you have a few 'rows' of data as an example, so we can get some idea what domain you're working in? Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... I have the following tables and keys and am seeking a way to easily populate Table 2 with values from Table 1. Table 1: PK1, PK2, F1, F2, ... Table 2: FK1, FK2, PK3, F1, F2, ... PK1 and PK2 are primary key fields in Table 1. They are indexed with Duplicates OK. FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2 are foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and inconsequential non-key fields in each table. [Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table 1].PK2 has a 1:N relationship to [Table 2].FK2. I set the Lookup Display Control to Combo Box for the foreign key fields [Table 2].FK1 and [Table 2].FK2, and I would like each these combo boxes to separately display the values available in the parent table fields to which they are linked. This is not a problem for [Table 2].FK1 - I simply specify Table 1 as the Row Source and retain the default Bound Column value of 1. However, I am unable to get [Table 2].FK2 to behave the same way. If I set Bound Column and Column Count to 2 I see both columns in the combo box, and I can seemingly select something that populates the field without violating referential integrity, but a PK1 value is displayed and not a PK2 value. This makes no sense to me. My typical workaround is to create a query that yields PK2 values, and I set the Row Source for [Table 2].FK2 to this query. It doesn't seem right that I should have to do this, so I suspect that I am missing something quite obvious. |
#7
|
|||
|
|||
multiple key fields in parent table and combo boxes for foreig
Yes, you make a good point about form combo boxes.
Thanks! "Jeff Boyce" wrote: If you are describing an "in-the-table" use of lookup datatypes, please stop NOW! What you described (using other than ...) is one of the issues that crops up trying to do data entry/validation/lookup via tables instead of forms, where it belongs. In a form, a combobox can bind any column you choose, and can display any column(s) you choose. Check the properties on the combobox control in the form... Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... Sorry for not being clearer, but this is an entirely fictitious pair of tables that I created only to illustrate the problem I have with the combo box functionality. My actual tables truly need the multiple key fields, but they would have only presented unnecessary confusion if I had described them instead. You suggest using a query to concatenate the first and last names for selection purposes, with VBA functionality behind it I assume to place the separate values in the table fields. This is a nice idea that would halve the effort on the user's part in doing data entry for these fields. This is not the problem that I thought I was trying to solve, but you have changed my way of looking at this issue and I may very well give it a try. However, I still hope there is a cleaner way of handling combo box lookups on fields that are not in the first column of the parent table. "Jeff Boyce" wrote: The natural vs. arbitrary key discussion can devolve into religious wars, so let's not go there! I'm curious why you would want to select LastName and FirstName separately for your Job History. Aren't you actually collecting information about the Person? What about using a query to concatenate these two and letting your users pick the correct person? You could still load those two separate values into the Job History table, but your users might find it easier. Note: I have several (some quite painful) experiences with two folks having the same name (and even living in the same house). You might be operating in a VERY limited domain in which this could never happen ... GOOD LUCK! If you ever do have two people with the same name, are you going to expand the number of fields you use to uniquely identify an individual? (this is one of the situations in which an "unnatural" key may have fewer problems in "behind the curtain" implementation -- of course, your users would never need to know...). Good luck Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... Jeff: Sorry, I don't think I conveyed my question well enough. Let me try to elaborate. Firstly, I am of the "natural key field" school. I rarely use an autonumber field as a key. Instead, I select fields that naturally serve to uniquely identify rows. Looking at it simplistically, a table that identifies a person by name (table [Person]) would have two key fields, one for first name and a second for last name. Duplicates should certainly be allowed for each of these key fields individually, but taken together they are obviously unique (ignoring the fact that two people can have the same first and last names). As an example, assume I want a table that documents a person's job history ([Person Job History]). This table would have the same primary keys as the [Person] table above (separate fields for first name and last name), but would also have a third primary key field, probably for date of employment. [Person] FirstName (PK) LastName (PK) [Person Job History] FirstName (PK) LastName (PK) DateOfEmployment (PK) Employer A one-to-many relationship with referential integrity would exist between the key fields of [Person] and the matching key fields in [Person Job History]. In filling out this table with a form, I would want to be able to use a combo box that pulls down available values from the [Person] table for first name and last name, separately of course. My problem lies with the combo box for the LastName field in [Person Job History], specifically, the second column position of the LastName field in the [Person] table. The combo box for the FirstName field uses the first column of [Person] with no problem. If I set the BoundColumn and COlumnCount to 2 for the combo box for the LastName field I can successfully populate [Person Job History], but the values that appear in this field are first names and not last names. This is not at all desirable. My workaround is to create a query on [Person] that simply lists the last name in the first column, and to set this query as the Row Source for the combo box for LastName in [Person Job History]. This seems silly to have to do. I'm wondering if there is a different way to solve this problem. Thanks! "Jeff Boyce" wrote: I'm not sure I'm following your description (but maybe I'm being too literal g)... A table would only have one PK, although you could create a multi-field primary key. A table with "duplicates OK" on its PK would be ... better off without a PK! A PK is supposed to uniquely identify a row, so there would NEVER be any duplicates. If you are describing the use of Lookup data types in your tables, please reconsider. This approach, while well-intentioned, can cause considerable confusion for those who try to understand it later (including yourself!). Perhaps I'm just getting lost with all the PK/F/... -- do you have a few 'rows' of data as an example, so we can get some idea what domain you're working in? Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... I have the following tables and keys and am seeking a way to easily populate Table 2 with values from Table 1. Table 1: PK1, PK2, F1, F2, ... Table 2: FK1, FK2, PK3, F1, F2, ... PK1 and PK2 are primary key fields in Table 1. They are indexed with Duplicates OK. FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2 are foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and inconsequential non-key fields in each table. [Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table 1].PK2 has a 1:N relationship to [Table 2].FK2. I set the Lookup Display Control to Combo Box for the foreign key fields [Table 2].FK1 and [Table 2].FK2, and I would like each these combo boxes to separately display the values available in the parent table fields to which they are linked. This is not a problem for [Table 2].FK1 - I simply specify Table 1 as the Row Source and retain the default Bound Column value of 1. However, I am unable to get [Table 2].FK2 to behave the same way. If I set Bound Column and Column Count to 2 I see both columns in the combo box, and I can seemingly select something that populates the field without violating referential integrity, but a PK1 value is displayed and not a PK2 value. This makes no sense to me. My typical workaround is to create a query that yields PK2 values, and I set the Row Source for [Table 2].FK2 to this query. It doesn't seem right that I should have to do this, so I suspect that I am missing something quite obvious. |
#8
|
|||
|
|||
multiple key fields in parent table and combo boxes for foreig
Jeff:
It sounds like you are deadset against setting table fields as combo boxes linked to fields in other tables. If that is the case, I'd appreciate hearing more about why. I do this regularly, but not directly in support of any functionality that a user would see. (I have users manipulate data through forms as you indicate.) I just want to know if I am doing something that might cause me problems. So far, all of my databases are well-behaved. Thanks! "Jeff Boyce" wrote: If you are describing an "in-the-table" use of lookup datatypes, please stop NOW! What you described (using other than ...) is one of the issues that crops up trying to do data entry/validation/lookup via tables instead of forms, where it belongs. In a form, a combobox can bind any column you choose, and can display any column(s) you choose. Check the properties on the combobox control in the form... Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... Sorry for not being clearer, but this is an entirely fictitious pair of tables that I created only to illustrate the problem I have with the combo box functionality. My actual tables truly need the multiple key fields, but they would have only presented unnecessary confusion if I had described them instead. You suggest using a query to concatenate the first and last names for selection purposes, with VBA functionality behind it I assume to place the separate values in the table fields. This is a nice idea that would halve the effort on the user's part in doing data entry for these fields. This is not the problem that I thought I was trying to solve, but you have changed my way of looking at this issue and I may very well give it a try. However, I still hope there is a cleaner way of handling combo box lookups on fields that are not in the first column of the parent table. "Jeff Boyce" wrote: The natural vs. arbitrary key discussion can devolve into religious wars, so let's not go there! I'm curious why you would want to select LastName and FirstName separately for your Job History. Aren't you actually collecting information about the Person? What about using a query to concatenate these two and letting your users pick the correct person? You could still load those two separate values into the Job History table, but your users might find it easier. Note: I have several (some quite painful) experiences with two folks having the same name (and even living in the same house). You might be operating in a VERY limited domain in which this could never happen ... GOOD LUCK! If you ever do have two people with the same name, are you going to expand the number of fields you use to uniquely identify an individual? (this is one of the situations in which an "unnatural" key may have fewer problems in "behind the curtain" implementation -- of course, your users would never need to know...). Good luck Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... Jeff: Sorry, I don't think I conveyed my question well enough. Let me try to elaborate. Firstly, I am of the "natural key field" school. I rarely use an autonumber field as a key. Instead, I select fields that naturally serve to uniquely identify rows. Looking at it simplistically, a table that identifies a person by name (table [Person]) would have two key fields, one for first name and a second for last name. Duplicates should certainly be allowed for each of these key fields individually, but taken together they are obviously unique (ignoring the fact that two people can have the same first and last names). As an example, assume I want a table that documents a person's job history ([Person Job History]). This table would have the same primary keys as the [Person] table above (separate fields for first name and last name), but would also have a third primary key field, probably for date of employment. [Person] FirstName (PK) LastName (PK) [Person Job History] FirstName (PK) LastName (PK) DateOfEmployment (PK) Employer A one-to-many relationship with referential integrity would exist between the key fields of [Person] and the matching key fields in [Person Job History]. In filling out this table with a form, I would want to be able to use a combo box that pulls down available values from the [Person] table for first name and last name, separately of course. My problem lies with the combo box for the LastName field in [Person Job History], specifically, the second column position of the LastName field in the [Person] table. The combo box for the FirstName field uses the first column of [Person] with no problem. If I set the BoundColumn and COlumnCount to 2 for the combo box for the LastName field I can successfully populate [Person Job History], but the values that appear in this field are first names and not last names. This is not at all desirable. My workaround is to create a query on [Person] that simply lists the last name in the first column, and to set this query as the Row Source for the combo box for LastName in [Person Job History]. This seems silly to have to do. I'm wondering if there is a different way to solve this problem. Thanks! "Jeff Boyce" wrote: I'm not sure I'm following your description (but maybe I'm being too literal g)... A table would only have one PK, although you could create a multi-field primary key. A table with "duplicates OK" on its PK would be ... better off without a PK! A PK is supposed to uniquely identify a row, so there would NEVER be any duplicates. If you are describing the use of Lookup data types in your tables, please reconsider. This approach, while well-intentioned, can cause considerable confusion for those who try to understand it later (including yourself!). Perhaps I'm just getting lost with all the PK/F/... -- do you have a few 'rows' of data as an example, so we can get some idea what domain you're working in? Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... I have the following tables and keys and am seeking a way to easily populate Table 2 with values from Table 1. Table 1: PK1, PK2, F1, F2, ... Table 2: FK1, FK2, PK3, F1, F2, ... PK1 and PK2 are primary key fields in Table 1. They are indexed with Duplicates OK. FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2 are foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and inconsequential non-key fields in each table. [Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table 1].PK2 has a 1:N relationship to [Table 2].FK2. I set the Lookup Display Control to Combo Box for the foreign key fields [Table 2].FK1 and [Table 2].FK2, and I would like each these combo boxes to separately display the values available in the parent table fields to which they are linked. This is not a problem for [Table 2].FK1 - I simply specify Table 1 as the Row Source and retain the default Bound Column value of 1. However, I am unable to get [Table 2].FK2 to behave the same way. If I set Bound Column and Column Count to 2 I see both columns in the combo box, and I can seemingly select something that populates the field without violating referential integrity, but a PK1 value is displayed and not a PK2 value. This makes no sense to me. My typical workaround is to create a query that yields PK2 values, and I set the Row Source for [Table 2].FK2 to this query. It doesn't seem right that I should have to do this, so I suspect that I am missing something quite obvious. |
#9
|
|||
|
|||
multiple key fields in parent table and combo boxes for foreig
No, I'm not against the notion, just asking. If it's working for you, why
mess with it?! Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... Jeff: It sounds like you are deadset against setting table fields as combo boxes linked to fields in other tables. If that is the case, I'd appreciate hearing more about why. I do this regularly, but not directly in support of any functionality that a user would see. (I have users manipulate data through forms as you indicate.) I just want to know if I am doing something that might cause me problems. So far, all of my databases are well-behaved. Thanks! "Jeff Boyce" wrote: If you are describing an "in-the-table" use of lookup datatypes, please stop NOW! What you described (using other than ...) is one of the issues that crops up trying to do data entry/validation/lookup via tables instead of forms, where it belongs. In a form, a combobox can bind any column you choose, and can display any column(s) you choose. Check the properties on the combobox control in the form... Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... Sorry for not being clearer, but this is an entirely fictitious pair of tables that I created only to illustrate the problem I have with the combo box functionality. My actual tables truly need the multiple key fields, but they would have only presented unnecessary confusion if I had described them instead. You suggest using a query to concatenate the first and last names for selection purposes, with VBA functionality behind it I assume to place the separate values in the table fields. This is a nice idea that would halve the effort on the user's part in doing data entry for these fields. This is not the problem that I thought I was trying to solve, but you have changed my way of looking at this issue and I may very well give it a try. However, I still hope there is a cleaner way of handling combo box lookups on fields that are not in the first column of the parent table. "Jeff Boyce" wrote: The natural vs. arbitrary key discussion can devolve into religious wars, so let's not go there! I'm curious why you would want to select LastName and FirstName separately for your Job History. Aren't you actually collecting information about the Person? What about using a query to concatenate these two and letting your users pick the correct person? You could still load those two separate values into the Job History table, but your users might find it easier. Note: I have several (some quite painful) experiences with two folks having the same name (and even living in the same house). You might be operating in a VERY limited domain in which this could never happen ... GOOD LUCK! If you ever do have two people with the same name, are you going to expand the number of fields you use to uniquely identify an individual? (this is one of the situations in which an "unnatural" key may have fewer problems in "behind the curtain" implementation -- of course, your users would never need to know...). Good luck Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... Jeff: Sorry, I don't think I conveyed my question well enough. Let me try to elaborate. Firstly, I am of the "natural key field" school. I rarely use an autonumber field as a key. Instead, I select fields that naturally serve to uniquely identify rows. Looking at it simplistically, a table that identifies a person by name (table [Person]) would have two key fields, one for first name and a second for last name. Duplicates should certainly be allowed for each of these key fields individually, but taken together they are obviously unique (ignoring the fact that two people can have the same first and last names). As an example, assume I want a table that documents a person's job history ([Person Job History]). This table would have the same primary keys as the [Person] table above (separate fields for first name and last name), but would also have a third primary key field, probably for date of employment. [Person] FirstName (PK) LastName (PK) [Person Job History] FirstName (PK) LastName (PK) DateOfEmployment (PK) Employer A one-to-many relationship with referential integrity would exist between the key fields of [Person] and the matching key fields in [Person Job History]. In filling out this table with a form, I would want to be able to use a combo box that pulls down available values from the [Person] table for first name and last name, separately of course. My problem lies with the combo box for the LastName field in [Person Job History], specifically, the second column position of the LastName field in the [Person] table. The combo box for the FirstName field uses the first column of [Person] with no problem. If I set the BoundColumn and COlumnCount to 2 for the combo box for the LastName field I can successfully populate [Person Job History], but the values that appear in this field are first names and not last names. This is not at all desirable. My workaround is to create a query on [Person] that simply lists the last name in the first column, and to set this query as the Row Source for the combo box for LastName in [Person Job History]. This seems silly to have to do. I'm wondering if there is a different way to solve this problem. Thanks! "Jeff Boyce" wrote: I'm not sure I'm following your description (but maybe I'm being too literal g)... A table would only have one PK, although you could create a multi-field primary key. A table with "duplicates OK" on its PK would be ... better off without a PK! A PK is supposed to uniquely identify a row, so there would NEVER be any duplicates. If you are describing the use of Lookup data types in your tables, please reconsider. This approach, while well-intentioned, can cause considerable confusion for those who try to understand it later (including yourself!). Perhaps I'm just getting lost with all the PK/F/... -- do you have a few 'rows' of data as an example, so we can get some idea what domain you're working in? Regards Jeff Boyce Microsoft Office/Access MVP "AccessMan" wrote in message ... I have the following tables and keys and am seeking a way to easily populate Table 2 with values from Table 1. Table 1: PK1, PK2, F1, F2, ... Table 2: FK1, FK2, PK3, F1, F2, ... PK1 and PK2 are primary key fields in Table 1. They are indexed with Duplicates OK. FK1, FK2, and PK3 are primary key fields in Table 2. FK1 and FK2 are foriegn keys of PK1 and PK2. F1, F2, etc. are unrelated and inconsequential non-key fields in each table. [Table 1].PK1 has a 1:N relationship to [Table 2].FK1, and [Table 1].PK2 has a 1:N relationship to [Table 2].FK2. I set the Lookup Display Control to Combo Box for the foreign key fields [Table 2].FK1 and [Table 2].FK2, and I would like each these combo boxes to separately display the values available in the parent table fields to which they are linked. This is not a problem for [Table 2].FK1 - I simply specify Table 1 as the Row Source and retain the default Bound Column value of 1. However, I am unable to get [Table 2].FK2 to behave the same way. If I set Bound Column and Column Count to 2 I see both columns in the combo box, and I can seemingly select something that populates the field without violating referential integrity, but a PK1 value is displayed and not a PK2 value. This makes no sense to me. My typical workaround is to create a query that yields PK2 values, and I set the Row Source for [Table 2].FK2 to this query. It doesn't seem right that I should have to do this, so I suspect that I am missing something quite obvious. |
Thread Tools | |
Display Modes | |
|
|