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
|
|||
|
|||
Make a field lookup dependent on the value in another field of a record?
I am trying to do the same thing, but am not sure what you mean by
"set your Form to appear in datasheet view (same look as a Table)" (from Eric's reply) or "your idea of a form appearing as datasheet view is a good one, and is what I'll do" (from your reply to Eric). In my case, I have 100s of options in the lookup table, and would like to limit them based on the previous entry if possible, so that people entering data don't need as much training. Thanks for any suggestions, Susan ----- James H. Power wrote: ----- Hello: In table design you can use the lookup wizard to provide a list of possible values for a field that's obtained from another table. Is it possible to further limit the values in the lookup list, based on the value of another field in the current record? For example, suppose I have a lookup TableA with fields and values as follow: TableA: FieldA FieldB a 1 b 2 c 3 c 4 d 5 d 6 Then suppose I'm developing the design of TableB with fields and values as follow, where FieldD is where I want to have the lookup values appear: TableB: FieldC FieldD c ? d ? FieldC has values that correspond to those in FieldA. What I'd like is that when the value in FieldC is "c", the lookup list returns only the corresponding values from FiledB of TableA where FieldA has the value "c", so that lookup list for FieldD then only shows possible values of "3" and "4". When the value in FieldC is "d", then the lookup list for FieldD only shows possible values of "5" and "6", and so forth. An approximation of the SQL statement that would achieve this would be SELECT DISTINCTROW TableA.FieldB FROM TABLEA WHERE (TableA.FieldA = "c") Except that I would like the quoted "c" in the above SQL statement to be the value of FieldC in the current record. I figure I can do this with a control on a form, but I'd rather put it into the table's design. Can anybody help? (and I hope my question made sense). Jim P. |
#2
|
|||
|
|||
Make a field lookup dependent on the value in another field of a record?
hi Susan. suggest you read the article at the following link before putting
any lookup fields in any of your tables: http://www.mvps.org/access/lookupfields.htm hth "Susan A" wrote in message ... I am trying to do the same thing, but am not sure what you mean by "set your Form to appear in datasheet view (same look as a Table)" (from Eric's reply) or "your idea of a form appearing as datasheet view is a good one, and is what I'll do" (from your reply to Eric). In my case, I have 100s of options in the lookup table, and would like to limit them based on the previous entry if possible, so that people entering data don't need as much training. Thanks for any suggestions, Susan ----- James H. Power wrote: ----- Hello: In table design you can use the lookup wizard to provide a list of possible values for a field that's obtained from another table. Is it possible to further limit the values in the lookup list, based on the value of another field in the current record? For example, suppose I have a lookup TableA with fields and values as follow: TableA: FieldA FieldB a 1 b 2 c 3 c 4 d 5 d 6 Then suppose I'm developing the design of TableB with fields and values as follow, where FieldD is where I want to have the lookup values appear: TableB: FieldC FieldD c ? d ? FieldC has values that correspond to those in FieldA. What I'd like is that when the value in FieldC is "c", the lookup list returns only the corresponding values from FiledB of TableA where FieldA has the value "c", so that lookup list for FieldD then only shows possible values of "3" and "4". When the value in FieldC is "d", then the lookup list for FieldD only shows possible values of "5" and "6", and so forth. An approximation of the SQL statement that would achieve this would be SELECT DISTINCTROW TableA.FieldB FROM TABLEA WHERE (TableA.FieldA = "c") Except that I would like the quoted "c" in the above SQL statement to be the value of FieldC in the current record. I figure I can do this with a control on a form, but I'd rather put it into the table's design. Can anybody help? (and I hope my question made sense). Jim P. |
#4
|
|||
|
|||
Make a field lookup dependent on the value in another field of a record?
seems i read somewhere that you can't reference one table field in another
table field - but since i don't use lookup fields in tables, i've never tried it. in a form, you can filter a combo box control's "droplist" based on the contents of another control, by setting a full reference to the other control as a criteria in the RowSource query or SQL statement. "WaltS [MVP WMC]" wrote in message ... Tina - (and others here) That said ("The Evils of Lookup Fields in Tables"), is there a way to do what Susan has asked? I, too, would like to have the "lookup" displayed results limited based on the contents of another field in the current record, and where the lookup source is another table, that in a field which is NOT the to-be-displayed field, has the same contents as that of the other field in the current record. Thanks in advance... -- --- REgards walts --- - - _ --- - - |~| - - --- ^ --- - - --- - - 52-Ken (WMC-MVP) Sun-57 - - "tina" wrote in message ... hi Susan. suggest you read the article at the following link before putting any lookup fields in any of your tables: http://www.mvps.org/access/lookupfields.htm hth "Susan A" wrote in message ... I am trying to do the same thing, but am not sure what you mean by "set your Form to appear in datasheet view (same look as a Table)" (from Eric's reply) or "your idea of a form appearing as datasheet view is a good one, and is what I'll do" (from your reply to Eric). In my case, I have 100s of options in the lookup table, and would like to limit them based on the previous entry if possible, so that people entering data don't need as much training. Thanks for any suggestions, Susan ----- James H. Power wrote: ----- Hello: In table design you can use the lookup wizard to provide a list of possible values for a field that's obtained from another table. Is it possible to further limit the values in the lookup list, based on the value of another field in the current record? For example, suppose I have a lookup TableA with fields and values as follow: TableA: FieldA FieldB a 1 b 2 c 3 c 4 d 5 d 6 Then suppose I'm developing the design of TableB with fields and values as follow, where FieldD is where I want to have the lookup values appear: TableB: FieldC FieldD c ? d ? FieldC has values that correspond to those in FieldA. What I'd like is that when the value in FieldC is "c", the lookup list returns only the corresponding values from FiledB of TableA where FieldA has the value "c", so that lookup list for FieldD then only shows possible values of "3" and "4". When the value in FieldC is "d", then the lookup list for FieldD only shows possible values of "5" and "6", and so forth. An approximation of the SQL statement that would achieve this would be SELECT DISTINCTROW TableA.FieldB FROM TABLEA WHERE (TableA.FieldA = "c") Except that I would like the quoted "c" in the above SQL statement to be the value of FieldC in the current record. I figure I can do this with a control on a form, but I'd rather put it into the table's design. Can anybody help? (and I hope my question made sense). Jim P. |
#5
|
|||
|
|||
Make a field lookup dependent on the value in another field of a record?
Tina -
That ("seems i read somewhere that you can't reference one table field in another table field") seemed to be (one of) the problem(s). So... Making me think as you are smile... I presume instead of lookup fields, you would use validation rules (LIKE "red OR LIKE "blue" OR...) when dealing with "a few" items. Which begs the question - is there a max number of characters to the size of a set of validation rules. If there is (and that limit would be), then how does one resolve the following... You have a large number of organizations that are identified by an entity and a local name (e.g., Veterans of Foreign Wars - National, or American Legion - Howard Van Wagner Post 962). You want to ensure uniformity of input, without the use of lookup fields, but you would like Access to help... Thanks again... -- --- REgards walts --- - - _ --- - - |~| - - --- ^ --- - - --- - - 52-Ken (WMC-MVP) Sun-57 - - "tina" wrote in message ... seems i read somewhere that you can't reference one table field in another table field - but since i don't use lookup fields in tables, i've never tried it. in a form, you can filter a combo box control's "droplist" based on the contents of another control, by setting a full reference to the other control as a criteria in the RowSource query or SQL statement. "WaltS [MVP WMC]" wrote in message ... Tina - (and others here) That said ("The Evils of Lookup Fields in Tables"), is there a way to do what Susan has asked? I, too, would like to have the "lookup" displayed results limited based on the contents of another field in the current record, and where the lookup source is another table, that in a field which is NOT the to-be-displayed field, has the same contents as that of the other field in the current record. Thanks in advance... -- --- REgards walts --- - - _ --- - - |~| - - --- ^ --- - - --- - - 52-Ken (WMC-MVP) Sun-57 - - "tina" wrote in message ... hi Susan. suggest you read the article at the following link before putting any lookup fields in any of your tables: http://www.mvps.org/access/lookupfields.htm hth "Susan A" wrote in message ... I am trying to do the same thing, but am not sure what you mean by "set your Form to appear in datasheet view (same look as a Table)" (from Eric's reply) or "your idea of a form appearing as datasheet view is a good one, and is what I'll do" (from your reply to Eric). In my case, I have 100s of options in the lookup table, and would like to limit them based on the previous entry if possible, so that people entering data don't need as much training. Thanks for any suggestions, Susan ----- James H. Power wrote: ----- Hello: In table design you can use the lookup wizard to provide a list of possible values for a field that's obtained from another table. Is it possible to further limit the values in the lookup list, based on the value of another field in the current record? For example, suppose I have a lookup TableA with fields and values as follow: TableA: FieldA FieldB a 1 b 2 c 3 c 4 d 5 d 6 Then suppose I'm developing the design of TableB with fields and values as follow, where FieldD is where I want to have the lookup values appear: TableB: FieldC FieldD c ? d ? FieldC has values that correspond to those in FieldA. What I'd like is that when the value in FieldC is "c", the lookup list returns only the corresponding values from FiledB of TableA where FieldA has the value "c", so that lookup list for FieldD then only shows possible values of "3" and "4". When the value in FieldC is "d", then the lookup list for FieldD only shows possible values of "5" and "6", and so forth. An approximation of the SQL statement that would achieve this would be SELECT DISTINCTROW TableA.FieldB FROM TABLEA WHERE (TableA.FieldA = "c") Except that I would like the quoted "c" in the above SQL statement to be the value of FieldC in the current record. I figure I can do this with a control on a form, but I'd rather put it into the table's design. Can anybody help? (and I hope my question made sense). Jim P. |
#6
|
|||
|
|||
Make a field lookup dependent on the value in another field of a record?
if you want to base the list of choices on the value of another field in the
record, then a validation rule won't work. you need to do it the way i said, in a form. it's an easy enough setup, but not something i can talk you thru step-by-step here. if you want to send me a copy of your database, sans proprietary data but with a few dummy records, then do the following: 1) compact the copy and zip to under 1 MB. 2) refer to the newsgroups in the email Subject line and send to ttaccKILLALLSPAMMess1 at DEADyahoo dot com, removing all the capital letters. i'll do a quick setup and email it back so you can see how it's done. hth "WaltS [MVP WMC]" wrote in message ... Tina - That ("seems i read somewhere that you can't reference one table field in another table field") seemed to be (one of) the problem(s). So... Making me think as you are smile... I presume instead of lookup fields, you would use validation rules (LIKE "red OR LIKE "blue" OR...) when dealing with "a few" items. Which begs the question - is there a max number of characters to the size of a set of validation rules. If there is (and that limit would be), then how does one resolve the following... You have a large number of organizations that are identified by an entity and a local name (e.g., Veterans of Foreign Wars - National, or American Legion - Howard Van Wagner Post 962). You want to ensure uniformity of input, without the use of lookup fields, but you would like Access to help... Thanks again... -- --- REgards walts --- - - _ --- - - |~| - - --- ^ --- - - --- - - 52-Ken (WMC-MVP) Sun-57 - - "tina" wrote in message ... seems i read somewhere that you can't reference one table field in another table field - but since i don't use lookup fields in tables, i've never tried it. in a form, you can filter a combo box control's "droplist" based on the contents of another control, by setting a full reference to the other control as a criteria in the RowSource query or SQL statement. "WaltS [MVP WMC]" wrote in message ... Tina - (and others here) That said ("The Evils of Lookup Fields in Tables"), is there a way to do what Susan has asked? I, too, would like to have the "lookup" displayed results limited based on the contents of another field in the current record, and where the lookup source is another table, that in a field which is NOT the to-be-displayed field, has the same contents as that of the other field in the current record. Thanks in advance... -- --- REgards walts --- - - _ --- - - |~| - - --- ^ --- - - --- - - 52-Ken (WMC-MVP) Sun-57 - - "tina" wrote in message ... hi Susan. suggest you read the article at the following link before putting any lookup fields in any of your tables: http://www.mvps.org/access/lookupfields.htm hth "Susan A" wrote in message ... I am trying to do the same thing, but am not sure what you mean by "set your Form to appear in datasheet view (same look as a Table)" (from Eric's reply) or "your idea of a form appearing as datasheet view is a good one, and is what I'll do" (from your reply to Eric). In my case, I have 100s of options in the lookup table, and would like to limit them based on the previous entry if possible, so that people entering data don't need as much training. Thanks for any suggestions, Susan ----- James H. Power wrote: ----- Hello: In table design you can use the lookup wizard to provide a list of possible values for a field that's obtained from another table. Is it possible to further limit the values in the lookup list, based on the value of another field in the current record? For example, suppose I have a lookup TableA with fields and values as follow: TableA: FieldA FieldB a 1 b 2 c 3 c 4 d 5 d 6 Then suppose I'm developing the design of TableB with fields and values as follow, where FieldD is where I want to have the lookup values appear: TableB: FieldC FieldD c ? d ? FieldC has values that correspond to those in FieldA. What I'd like is that when the value in FieldC is "c", the lookup list returns only the corresponding values from FiledB of TableA where FieldA has the value "c", so that lookup list for FieldD then only shows possible values of "3" and "4". When the value in FieldC is "d", then the lookup list for FieldD only shows possible values of "5" and "6", and so forth. An approximation of the SQL statement that would achieve this would be SELECT DISTINCTROW TableA.FieldB FROM TABLEA WHERE (TableA.FieldA = "c") Except that I would like the quoted "c" in the above SQL statement to be the value of FieldC in the current record. I figure I can do this with a control on a form, but I'd rather put it into the table's design. Can anybody help? (and I hope my question made sense). Jim P. |
#7
|
|||
|
|||
Make a field lookup dependent on the value in another field of a record?
Tina -
Thanks again for your help - as I am trying to "learn" a tad about Access, I'd rather fumble my way around a bit - I remember better that way. And, if you don't mind spending some more of your time... First a fact as I have come to understand it... A lookup field in a table, based on another table, while it displays the looked-up value, actually saves in the table an id from the other table. From what you said, am I correct to infer... A lookup field in a form, based on a table, used to create an entry in a different table, actually saves the looked-up value in that table. If I am correct, then I have learned a "subtle" fact, and, better understand "The Evils of Lookup Fields in Tables." If I am wrong... well, er, some additional insight if you would. -- --- REgards walts --- - - _ --- - - |~| - - --- ^ --- - - --- - - 52-Ken (WMC-MVP) Sun-57 - - "tina" wrote in message ... if you want to base the list of choices on the value of another field in the record, then a validation rule won't work. you need to do it the way i said, in a form. it's an easy enough setup, but not something i can talk you thru step-by-step here. if you want to send me a copy of your database, sans proprietary data but with a few dummy records, then do the following: 1) compact the copy and zip to under 1 MB. 2) refer to the newsgroups in the email Subject line and send to ttaccKILLALLSPAMMess1 at DEADyahoo dot com, removing all the capital letters. i'll do a quick setup and email it back so you can see how it's done. hth "WaltS [MVP WMC]" wrote in message ... Tina - That ("seems i read somewhere that you can't reference one table field in another table field") seemed to be (one of) the problem(s). So... Making me think as you are smile... I presume instead of lookup fields, you would use validation rules (LIKE "red OR LIKE "blue" OR...) when dealing with "a few" items. Which begs the question - is there a max number of characters to the size of a set of validation rules. If there is (and that limit would be), then how does one resolve the following... You have a large number of organizations that are identified by an entity and a local name (e.g., Veterans of Foreign Wars - National, or American Legion - Howard Van Wagner Post 962). You want to ensure uniformity of input, without the use of lookup fields, but you would like Access to help... Thanks again... -- --- REgards walts --- - - _ --- - - |~| - - --- ^ --- - - --- - - 52-Ken (WMC-MVP) Sun-57 - - "tina" wrote in message ... seems i read somewhere that you can't reference one table field in another table field - but since i don't use lookup fields in tables, i've never tried it. in a form, you can filter a combo box control's "droplist" based on the contents of another control, by setting a full reference to the other control as a criteria in the RowSource query or SQL statement. "WaltS [MVP WMC]" wrote in message ... Tina - (and others here) That said ("The Evils of Lookup Fields in Tables"), is there a way to do what Susan has asked? I, too, would like to have the "lookup" displayed results limited based on the contents of another field in the current record, and where the lookup source is another table, that in a field which is NOT the to-be-displayed field, has the same contents as that of the other field in the current record. Thanks in advance... -- --- REgards walts --- - - _ --- - - |~| - - --- ^ --- - - --- - - 52-Ken (WMC-MVP) Sun-57 - - "tina" wrote in message ... hi Susan. suggest you read the article at the following link before putting any lookup fields in any of your tables: http://www.mvps.org/access/lookupfields.htm hth "Susan A" wrote in message ... I am trying to do the same thing, but am not sure what you mean by "set your Form to appear in datasheet view (same look as a Table)" (from Eric's reply) or "your idea of a form appearing as datasheet view is a good one, and is what I'll do" (from your reply to Eric). In my case, I have 100s of options in the lookup table, and would like to limit them based on the previous entry if possible, so that people entering data don't need as much training. Thanks for any suggestions, Susan ----- James H. Power wrote: ----- Hello: In table design you can use the lookup wizard to provide a list of possible values for a field that's obtained from another table. Is it possible to further limit the values in the lookup list, based on the value of another field in the current record? For example, suppose I have a lookup TableA with fields and values as follow: TableA: FieldA FieldB a 1 b 2 c 3 c 4 d 5 d 6 Then suppose I'm developing the design of TableB with fields and values as follow, where FieldD is where I want to have the lookup values appear: TableB: FieldC FieldD c ? d ? FieldC has values that correspond to those in FieldA. What I'd like is that when the value in FieldC is "c", the lookup list returns only the corresponding values from FiledB of TableA where FieldA has the value "c", so that lookup list for FieldD then only shows possible values of "3" and "4". When the value in FieldC is "d", then the lookup list for FieldD only shows possible values of "5" and "6", and so forth. An approximation of the SQL statement that would achieve this would be SELECT DISTINCTROW TableA.FieldB FROM TABLEA WHERE (TableA.FieldA = "c") Except that I would like the quoted "c" in the above SQL statement to be the value of FieldC in the current record. I figure I can do this with a control on a form, but I'd rather put it into the table's design. Can anybody help? (and I hope my question made sense). Jim P. |
#8
|
|||
|
|||
Make a field lookup dependent on the value in another field of a record?
i usually stick with a thread, once i post to it, until it has been dormant
for a couple weeks. i'll always answer specific questions, or at least declare my lack of knowledge g; i just haven't the time to try to give detailed instructions for a whole process to achieve a particular solution. and of course, anyone else in the newsgroups can post a response to any question, too - no thread is exclusive. having said all that... i think you're correct in your basic grasp of how to use a lookup table via a combo box (or list box, for that matter) in a form. but just to clarify: your form is based on tblA. you want to save an ID from the lookup table into a field in tblA. so in the form, you create a combo box, bound to the field in tblA, with the combo's RowSource based on the lookup table. hth "WaltS [MVP WMC]" wrote in message ... Tina - Thanks again for your help - as I am trying to "learn" a tad about Access, I'd rather fumble my way around a bit - I remember better that way. And, if you don't mind spending some more of your time... First a fact as I have come to understand it... A lookup field in a table, based on another table, while it displays the looked-up value, actually saves in the table an id from the other table. From what you said, am I correct to infer... A lookup field in a form, based on a table, used to create an entry in a different table, actually saves the looked-up value in that table. If I am correct, then I have learned a "subtle" fact, and, better understand "The Evils of Lookup Fields in Tables." If I am wrong... well, er, some additional insight if you would. -- --- REgards walts --- - - _ --- - - |~| - - --- ^ --- - - --- - - 52-Ken (WMC-MVP) Sun-57 - - "tina" wrote in message ... if you want to base the list of choices on the value of another field in the record, then a validation rule won't work. you need to do it the way i said, in a form. it's an easy enough setup, but not something i can talk you thru step-by-step here. if you want to send me a copy of your database, sans proprietary data but with a few dummy records, then do the following: 1) compact the copy and zip to under 1 MB. 2) refer to the newsgroups in the email Subject line and send to ttaccKILLALLSPAMMess1 at DEADyahoo dot com, removing all the capital letters. i'll do a quick setup and email it back so you can see how it's done. hth "WaltS [MVP WMC]" wrote in message ... Tina - That ("seems i read somewhere that you can't reference one table field in another table field") seemed to be (one of) the problem(s). So... Making me think as you are smile... I presume instead of lookup fields, you would use validation rules (LIKE "red OR LIKE "blue" OR...) when dealing with "a few" items. Which begs the question - is there a max number of characters to the size of a set of validation rules. If there is (and that limit would be), then how does one resolve the following... You have a large number of organizations that are identified by an entity and a local name (e.g., Veterans of Foreign Wars - National, or American Legion - Howard Van Wagner Post 962). You want to ensure uniformity of input, without the use of lookup fields, but you would like Access to help... Thanks again... -- --- REgards walts --- - - _ --- - - |~| - - --- ^ --- - - --- - - 52-Ken (WMC-MVP) Sun-57 - - "tina" wrote in message ... seems i read somewhere that you can't reference one table field in another table field - but since i don't use lookup fields in tables, i've never tried it. in a form, you can filter a combo box control's "droplist" based on the contents of another control, by setting a full reference to the other control as a criteria in the RowSource query or SQL statement. "WaltS [MVP WMC]" wrote in message ... Tina - (and others here) That said ("The Evils of Lookup Fields in Tables"), is there a way to do what Susan has asked? I, too, would like to have the "lookup" displayed results limited based on the contents of another field in the current record, and where the lookup source is another table, that in a field which is NOT the to-be-displayed field, has the same contents as that of the other field in the current record. Thanks in advance... -- --- REgards walts --- - - _ --- - - |~| - - --- ^ --- - - --- - - 52-Ken (WMC-MVP) Sun-57 - - "tina" wrote in message ... hi Susan. suggest you read the article at the following link before putting any lookup fields in any of your tables: http://www.mvps.org/access/lookupfields.htm hth "Susan A" wrote in message ... I am trying to do the same thing, but am not sure what you mean by "set your Form to appear in datasheet view (same look as a Table)" (from Eric's reply) or "your idea of a form appearing as datasheet view is a good one, and is what I'll do" (from your reply to Eric). In my case, I have 100s of options in the lookup table, and would like to limit them based on the previous entry if possible, so that people entering data don't need as much training. Thanks for any suggestions, Susan ----- James H. Power wrote: ----- Hello: In table design you can use the lookup wizard to provide a list of possible values for a field that's obtained from another table. Is it possible to further limit the values in the lookup list, based on the value of another field in the current record? For example, suppose I have a lookup TableA with fields and values as follow: TableA: FieldA FieldB a 1 b 2 c 3 c 4 d 5 d 6 Then suppose I'm developing the design of TableB with fields and values as follow, where FieldD is where I want to have the lookup values appear: TableB: FieldC FieldD c ? d ? FieldC has values that correspond to those in FieldA. What I'd like is that when the value in FieldC is "c", the lookup list returns only the corresponding values from FiledB of TableA where FieldA has the value "c", so that lookup list for FieldD then only shows possible values of "3" and "4". When the value in FieldC is "d", then the lookup list for FieldD only shows possible values of "5" and "6", and so forth. An approximation of the SQL statement that would achieve this would be SELECT DISTINCTROW TableA.FieldB FROM TABLEA WHERE (TableA.FieldA = "c") Except that I would like the quoted "c" in the above SQL statement to be the value of FieldC in the current record. I figure I can do this with a control on a form, but I'd rather put it into the table's design. Can anybody help? (and I hope my question made sense). Jim P. |
#9
|
|||
|
|||
Make a field lookup dependent on the value in another field of a record?
Tina -
Thanks - now to think on it smile -- --- REgards walts --- - - _ --- - - |~| - - --- ^ --- - - --- - - 52-Ken (WMC-MVP) Sun-57 - - |
Thread Tools | |
Display Modes | |
|
|