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
|
|||
|
|||
Storing multiple fields in a table from a single form combo lookup
Hi,
I'm wondering if it's possible to store multiple fields of data from a single lookup combo box on a data input form. I'm familiar with how to populate form fields with 'reference/display' data from a lookup but my issue is that I need the multiple look up data stored rather than just 'displayed' for potential individual record adjustments. I am creating an event registration database and need to track fees payable and catering for each delegate for a multi day event involving several types of registration (full, day only etc), involving up to 9 catering occassions (tick boxes for a.m. refreshments, lunch and p.m. refrehsments for each day). I managed to create a lookup that displayed the default values for fees and catering attendance according to the parameters of the particular registration type however couldn't then make adjustments to individual records eg delegate 'A' advises they can't attend lunch on day 2 so I need to make an adjustment to their registration record accordingly in order to ensure my catering numbers report remains accurate. Any help/suggestions gratefully accepted as I've been searching forums/advice sites for hours and all seem to lead back to the same solution - that there should be no need to 'store' data that is already contained in other tables but in this case I can't think of a way around it unless I create an 'Adjustments' table that stores any changes to the 'default' data and my 'Amount Payable' and 'Catering Numbers' reports calculate the difference between the default and adjustments but that seems to be the long way around.... |
#2
|
|||
|
|||
Storing multiple fields in a table from a single form combo lookup
On Sun, 31 May 2009 18:42:01 -0700, EdHead
wrote: Hi, I'm wondering if it's possible to store multiple fields of data from a single lookup combo box on a data input form. Yes, using the AfterUpdate event of the combo box. But it would ordinarily be A Very Bad Idea to do so. See below. I'm familiar with how to populate form fields with 'reference/display' data from a lookup but my issue is that I need the multiple look up data stored rather than just 'displayed' for potential individual record adjustments. I am creating an event registration database and need to track fees payable and catering for each delegate for a multi day event involving several types of registration (full, day only etc), involving up to 9 catering occassions (tick boxes for a.m. refreshments, lunch and p.m. refrehsments for each day). And if you add another occasion you'll, what, redesign your table, rewrite all your queries, revise all your forms, redo all your reports!? OUCH! If you have a one (event) to many (catering occasions) relationship you would do much better to model it as a one to many relationship, with each catering occasion being a new *RECORD* in a table related one to many to the Events table. I managed to create a lookup that displayed the default values for fees and catering attendance according to the parameters of the particular registration type however couldn't then make adjustments to individual records eg delegate 'A' advises they can't attend lunch on day 2 so I need to make an adjustment to their registration record accordingly in order to ensure my catering numbers report remains accurate. Any help/suggestions gratefully accepted as I've been searching forums/advice sites for hours and all seem to lead back to the same solution - that there should be no need to 'store' data that is already contained in other tables but in this case I can't think of a way around it unless I create an 'Adjustments' table that stores any changes to the 'default' data and my 'Amount Payable' and 'Catering Numbers' reports calculate the difference between the default and adjustments but that seems to be the long way around.... If you use a table with foreign keys to the event and to the attendee tables, and add a new record for each catering event, then you can easily specify vegetarian, omit a record, add an extra fee, or whatever. If you have each event as a field in the table, it's a lot less flexible and adaptable. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Storing multiple fields in a table from a single form combo lo
Thanks, John.
I certainly take your point about a catering table with multiple catering records for each delegate (i guess I kind of lost my way off the 'relational' track because I was a little pre-occupied trying to devise a way to automate the data input for the catering events). If I link up a catering table as suggested, is there any way you could suggest for me to achieve some degree of auto-population of catering records applicable to the type of registration? Registration code is currently determined by concatenation of 3 registration categories. Thanks for your advice. Currently m "John W. Vinson" wrote: On Sun, 31 May 2009 18:42:01 -0700, EdHead wrote: Hi, I'm wondering if it's possible to store multiple fields of data from a single lookup combo box on a data input form. Yes, using the AfterUpdate event of the combo box. But it would ordinarily be A Very Bad Idea to do so. See below. I'm familiar with how to populate form fields with 'reference/display' data from a lookup but my issue is that I need the multiple look up data stored rather than just 'displayed' for potential individual record adjustments. I am creating an event registration database and need to track fees payable and catering for each delegate for a multi day event involving several types of registration (full, day only etc), involving up to 9 catering occassions (tick boxes for a.m. refreshments, lunch and p.m. refrehsments for each day). And if you add another occasion you'll, what, redesign your table, rewrite all your queries, revise all your forms, redo all your reports!? OUCH! If you have a one (event) to many (catering occasions) relationship you would do much better to model it as a one to many relationship, with each catering occasion being a new *RECORD* in a table related one to many to the Events table. I managed to create a lookup that displayed the default values for fees and catering attendance according to the parameters of the particular registration type however couldn't then make adjustments to individual records eg delegate 'A' advises they can't attend lunch on day 2 so I need to make an adjustment to their registration record accordingly in order to ensure my catering numbers report remains accurate. Any help/suggestions gratefully accepted as I've been searching forums/advice sites for hours and all seem to lead back to the same solution - that there should be no need to 'store' data that is already contained in other tables but in this case I can't think of a way around it unless I create an 'Adjustments' table that stores any changes to the 'default' data and my 'Amount Payable' and 'Catering Numbers' reports calculate the difference between the default and adjustments but that seems to be the long way around.... If you use a table with foreign keys to the event and to the attendee tables, and add a new record for each catering event, then you can easily specify vegetarian, omit a record, add an extra fee, or whatever. If you have each event as a field in the table, it's a lot less flexible and adaptable. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Storing multiple fields in a table from a single form combo lo
On Sun, 31 May 2009 20:25:01 -0700, EdHead
wrote: Thanks, John. I certainly take your point about a catering table with multiple catering records for each delegate (i guess I kind of lost my way off the 'relational' track because I was a little pre-occupied trying to devise a way to automate the data input for the catering events). If I link up a catering table as suggested, is there any way you could suggest for me to achieve some degree of auto-population of catering records applicable to the type of registration? Registration code is currently determined by concatenation of 3 registration categories. Sure; you could have a "template" set of catering records with those fields, and run an Append query using the categories as criteria. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Storing multiple fields in a table from a single form combo lo
Ah yes....I did attempt an apend query before I posted here but wasn't sure
if this is what I needed - now you've confirmed it I will have another go at tweaking the criteria which just wasn't doing it for me. Thanks for all your help. Helen "John W. Vinson" wrote: On Sun, 31 May 2009 20:25:01 -0700, EdHead wrote: Thanks, John. I certainly take your point about a catering table with multiple catering records for each delegate (i guess I kind of lost my way off the 'relational' track because I was a little pre-occupied trying to devise a way to automate the data input for the catering events). If I link up a catering table as suggested, is there any way you could suggest for me to achieve some degree of auto-population of catering records applicable to the type of registration? Registration code is currently determined by concatenation of 3 registration categories. Sure; you could have a "template" set of catering records with those fields, and run an Append query using the categories as criteria. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|