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
|
|||
|
|||
How to obtain logical combination for query result?
I have a table named ProgramCodes and it may need to be redone.
the fields in this table a 1. Guitar (item) 2. Option (item) 3. Code (code the user enters for the Guitar and Guitar w/ Option(s) combinations. So I've created a form and a sub form for the user to be able to enter the Codes per Guitar and Guitar w/ Option(s) The main form is just the Guitar item the user can search to find the Guitar itemto start entering the Codes and combinations by entering the Option items for the Guitar combo. The record source is from the Guitars table (which is just all the guitar items) The sub form is all the fields of the ProgramCodes table. Linked by the Guitar item. Please help me how to achieve the following as noted: Guitar is an Item Option is an Item Code is what the programmer uses for their understanding of how to program their machines to make the guitar with options if any. This field (Code) in the ProgramCodes table (and form) is a free-from and just a text file for the programmer (user) to enter the code(s) associated to the guitar and guitar with option combos. So someone purchases a guitar and select several options or not, there will be various Code (programming codes) to build that guitar is the guitar item is just the bare guitar (skeleton / shell). The options make up the different features on the guitar. The programming codes make these features function in their special ways. How can the Codes get entered into a table or tables if the current ProgramCodes table is insufficient so that when you query on, say 127R-RB, you get all the invoices with DC135 guitar item with ONLY RB as an option item. or if you query on an invoice with DC135 to see the programming code you get the result of just 125R and 135T IF and only IF that item on the invoice had no option. or if you query on an invoice with DC135 and the invoice has options 26 and RB that it'll only show 127R-RB and 136T-RB as my Code result BUT it can't show the Code for just the 26 or just the RB, it needs to show the combined Option's Code. here are the results that need to be obtained DC135 is a Guitar item: DC135 without any Options should be assigned Codes = 125R and 135T DC135 with RB as an Option and should be assigned Codes = 127R-RB and 135T-RB DC135 with 26 as an Option and should be assigned Codes = 125R and 136T DC135 with 29 as an option and should be assigned Codes = 125R and 139T DC135 with 26 & RB are Options and should be assigned Codes = 127R-RB and 136T-RB DC135 with 29 & RB are Options and should be these Codes = 127R-RB and 139T- RB (it can not display any of the other codes above, it can only display this code for this combo) I hope i made sense. I don't want to complicate this anymore then it is by itself. The other tables with Invoice information is insignificant for what needs happen here with the Codes in ProgramCodes table. Thank you! -- thank you for all your help! ~ angel ~ Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
How to obtain logical combination for query result?
On Oct 4, 2:50 am, "~ angel ~ via AccessMonster.com" u33627@uwe
wrote: I have a table named ProgramCodes and it may need to be redone. the fields in this table a 1. Guitar (item) 2. Option (item) 3. Code (code the user enters for the Guitar and Guitar w/ Option(s) combinations. So I've created a form and a sub form for the user to be able to enter the Codes per Guitar and Guitar w/ Option(s) The main form is just the Guitar item the user can search to find the Guitar itemto start entering the Codes and combinations by entering the Option items for the Guitar combo. The record source is from the Guitars table (which is just all the guitar items) The sub form is all the fields of the ProgramCodes table. Linked by the Guitar item. Please help me how to achieve the following as noted: Guitar is an Item Option is an Item Code is what the programmer uses for their understanding of how to program their machines to make the guitar with options if any. This field (Code) in the ProgramCodes table (and form) is a free-from and just a text file for the programmer (user) to enter the code(s) associated to the guitar and guitar with option combos. So someone purchases a guitar and select several options or not, there will be various Code (programming codes) to build that guitar is the guitar item is just the bare guitar (skeleton / shell). The options make up the different features on the guitar. The programming codes make these features function in their special ways. How can the Codes get entered into a table or tables if the current ProgramCodes table is insufficient so that when you query on, say 127R-RB, you get all the invoices with DC135 guitar item with ONLY RB as an option item. or if you query on an invoice with DC135 to see the programming code you get the result of just 125R and 135T IF and only IF that item on the invoice had no option. or if you query on an invoice with DC135 and the invoice has options 26 and RB that it'll only show 127R-RB and 136T-RB as my Code result BUT it can't show the Code for just the 26 or just the RB, it needs to show the combined Option's Code. here are the results that need to be obtained DC135 is a Guitar item: DC135 without any Options should be assigned Codes = 125R and 135T DC135 with RB as an Option and should be assigned Codes = 127R-RB and 135T-RB DC135 with 26 as an Option and should be assigned Codes = 125R and 136T DC135 with 29 as an option and should be assigned Codes = 125R and 139T DC135 with 26 & RB are Options and should be assigned Codes = 127R-RB and 136T-RB DC135 with 29 & RB are Options and should be these Codes = 127R-RB and 139T- RB (it can not display any of the other codes above, it can only display this code for this combo) I hope i made sense. I don't want to complicate this anymore then it is by itself. The other tables with Invoice information is insignificant for what needs happen here with the Codes in ProgramCodes table. Initially I tried to deduce how the options determine the codes, then decided that the rules may not be the same for other item/option combinations so played safe by doing it 'long-hand' (I changed the wording to include the word 'only' to emphasize the fact that descriptions are mutually exclusive): CREATE TABLE Items ( item_code VARCHAR(10) NOT NULL UNIQUE, item_name VARCHAR(20) NOT NULL ) ; INSERT INTO Items (item_code, item_name) VALUES ('DC135', 'Guitar') ; CREATE TABLE OptionRCodes ( option_R_code VARCHAR(10) NOT NULL UNIQUE, CHECK ((option_R_code ALIKE '[1-9][0-9][0-9]R') OR (option_R_code ALIKE '[1-9][0-9][0-9]R-RB')) ) ; INSERT INTO OptionRCodes (option_R_code) VALUES ('125R') ; INSERT INTO OptionRCodes (option_R_code) VALUES ('127R-RB') ; CREATE TABLE OptionTCodes ( option_T_code VARCHAR(10) NOT NULL UNIQUE, CHECK ((option_T_code ALIKE '[1-9][0-9][0-9]T') OR (option_T_code ALIKE '[1-9][0-9][0-9]T-RB')) ) ; INSERT INTO OptionTCodes (option_T_code) VALUES ('135T') ; INSERT INTO OptionTCodes (option_T_code) VALUES ('136T') ; INSERT INTO OptionTCodes (option_T_code) VALUES ('139T') ; INSERT INTO OptionTCodes (option_T_code) VALUES ('135T-RB') ; INSERT INTO OptionTCodes (option_T_code) VALUES ('136T-RB') ; INSERT INTO OptionTCodes (option_T_code) VALUES ('139T-RB') ; CREATE TABLE ItemsSpecs ( option_R_code VARCHAR(10) NOT NULL REFERENCES OptionRCodes (option_R_code), option_T_code VARCHAR(10) NOT NULL REFERENCES OptionTCodes (option_T_code), UNIQUE (option_R_code, option_T_code), item_code VARCHAR(10) NOT NULL REFERENCES Items (item_code), description VARCHAR(30) NOT NULL ) ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('125R', '135T', 'DC135', 'without any Options') ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('127R-RB', '135T-RB', 'DC135', 'with RB only as an Option') ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('125R', '136T', 'DC135', 'with 26 only as an Option') ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('125R', '139T', 'DC135', 'with 29 only as an Option') ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('127R-RB', '136T-RB', 'DC135', 'with 26 and RB only as Options') ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('127R-RB', '139T-RB', 'DC135', 'with 26 and RB only as Options') ; Jamie. -- |
#3
|
|||
|
|||
How to obtain logical combination for query result?
You may want to explain in English what all that code is designed to do and
why. "Jamie Collins" wrote in message ups.com... On Oct 4, 2:50 am, "~ angel ~ via AccessMonster.com" u33627@uwe wrote: I have a table named ProgramCodes and it may need to be redone. the fields in this table a 1. Guitar (item) 2. Option (item) 3. Code (code the user enters for the Guitar and Guitar w/ Option(s) combinations. So I've created a form and a sub form for the user to be able to enter the Codes per Guitar and Guitar w/ Option(s) The main form is just the Guitar item the user can search to find the Guitar itemto start entering the Codes and combinations by entering the Option items for the Guitar combo. The record source is from the Guitars table (which is just all the guitar items) The sub form is all the fields of the ProgramCodes table. Linked by the Guitar item. Please help me how to achieve the following as noted: Guitar is an Item Option is an Item Code is what the programmer uses for their understanding of how to program their machines to make the guitar with options if any. This field (Code) in the ProgramCodes table (and form) is a free-from and just a text file for the programmer (user) to enter the code(s) associated to the guitar and guitar with option combos. So someone purchases a guitar and select several options or not, there will be various Code (programming codes) to build that guitar is the guitar item is just the bare guitar (skeleton / shell). The options make up the different features on the guitar. The programming codes make these features function in their special ways. How can the Codes get entered into a table or tables if the current ProgramCodes table is insufficient so that when you query on, say 127R-RB, you get all the invoices with DC135 guitar item with ONLY RB as an option item. or if you query on an invoice with DC135 to see the programming code you get the result of just 125R and 135T IF and only IF that item on the invoice had no option. or if you query on an invoice with DC135 and the invoice has options 26 and RB that it'll only show 127R-RB and 136T-RB as my Code result BUT it can't show the Code for just the 26 or just the RB, it needs to show the combined Option's Code. here are the results that need to be obtained DC135 is a Guitar item: DC135 without any Options should be assigned Codes = 125R and 135T DC135 with RB as an Option and should be assigned Codes = 127R-RB and 135T-RB DC135 with 26 as an Option and should be assigned Codes = 125R and 136T DC135 with 29 as an option and should be assigned Codes = 125R and 139T DC135 with 26 & RB are Options and should be assigned Codes = 127R-RB and 136T-RB DC135 with 29 & RB are Options and should be these Codes = 127R-RB and 139T- RB (it can not display any of the other codes above, it can only display this code for this combo) I hope i made sense. I don't want to complicate this anymore then it is by itself. The other tables with Invoice information is insignificant for what needs happen here with the Codes in ProgramCodes table. Initially I tried to deduce how the options determine the codes, then decided that the rules may not be the same for other item/option combinations so played safe by doing it 'long-hand' (I changed the wording to include the word 'only' to emphasize the fact that descriptions are mutually exclusive): CREATE TABLE Items ( item_code VARCHAR(10) NOT NULL UNIQUE, item_name VARCHAR(20) NOT NULL ) ; INSERT INTO Items (item_code, item_name) VALUES ('DC135', 'Guitar') ; CREATE TABLE OptionRCodes ( option_R_code VARCHAR(10) NOT NULL UNIQUE, CHECK ((option_R_code ALIKE '[1-9][0-9][0-9]R') OR (option_R_code ALIKE '[1-9][0-9][0-9]R-RB')) ) ; INSERT INTO OptionRCodes (option_R_code) VALUES ('125R') ; INSERT INTO OptionRCodes (option_R_code) VALUES ('127R-RB') ; CREATE TABLE OptionTCodes ( option_T_code VARCHAR(10) NOT NULL UNIQUE, CHECK ((option_T_code ALIKE '[1-9][0-9][0-9]T') OR (option_T_code ALIKE '[1-9][0-9][0-9]T-RB')) ) ; INSERT INTO OptionTCodes (option_T_code) VALUES ('135T') ; INSERT INTO OptionTCodes (option_T_code) VALUES ('136T') ; INSERT INTO OptionTCodes (option_T_code) VALUES ('139T') ; INSERT INTO OptionTCodes (option_T_code) VALUES ('135T-RB') ; INSERT INTO OptionTCodes (option_T_code) VALUES ('136T-RB') ; INSERT INTO OptionTCodes (option_T_code) VALUES ('139T-RB') ; CREATE TABLE ItemsSpecs ( option_R_code VARCHAR(10) NOT NULL REFERENCES OptionRCodes (option_R_code), option_T_code VARCHAR(10) NOT NULL REFERENCES OptionTCodes (option_T_code), UNIQUE (option_R_code, option_T_code), item_code VARCHAR(10) NOT NULL REFERENCES Items (item_code), description VARCHAR(30) NOT NULL ) ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('125R', '135T', 'DC135', 'without any Options') ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('127R-RB', '135T-RB', 'DC135', 'with RB only as an Option') ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('125R', '136T', 'DC135', 'with 26 only as an Option') ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('125R', '139T', 'DC135', 'with 29 only as an Option') ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('127R-RB', '136T-RB', 'DC135', 'with 26 and RB only as Options') ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('127R-RB', '139T-RB', 'DC135', 'with 26 and RB only as Options') ; Jamie. -- |
#4
|
|||
|
|||
How to obtain logical combination for query result?
Hi Jamie,
I appreciate you trying to take a wing at this one ... i am a bit confused, and it maybe because of how I explained it. On your suggestion ... Items with item_code and item_name is basically the Guitars table I have all ready and has all the possible Guitar items. Now it seems like you are suggesting I separate the Codes into 2 tables. One for the ones with "R" and ones with "T". R means Rear of the Guitar T mean Top of the Guitar i did not include this but it seems you have pulled that out of the Codes as there's is some logic the Guitar programmer created in the Codes item numbering. Then finally creating the match and putting them into the Item Specs table. is that right? I am now trying to get more info from the Guitar programmer to see if there's any real logic in the Codes he uses in case this R and T identifier does not always work throughout the others. Jamie Collins wrote: On Oct 4, 2:50 am, "~ angel ~ via AccessMonster.com" u33627@uwe wrote: CREATE TABLE Items ( item_code VARCHAR(10) NOT NULL UNIQUE, item_name VARCHAR(20) NOT NULL ) ; INSERT INTO Items (item_code, item_name) VALUES ('DC135', 'Guitar') ; CREATE TABLE OptionRCodes ( option_R_code VARCHAR(10) NOT NULL UNIQUE, CHECK ((option_R_code ALIKE '[1-9][0-9][0-9]R') OR (option_R_code ALIKE '[1-9][0-9][0-9]R-RB')) ) ; INSERT INTO OptionRCodes (option_R_code) VALUES ('125R') ; INSERT INTO OptionRCodes (option_R_code) VALUES ('127R-RB') ; CREATE TABLE OptionTCodes ( option_T_code VARCHAR(10) NOT NULL UNIQUE, CHECK ((option_T_code ALIKE '[1-9][0-9][0-9]T') OR (option_T_code ALIKE '[1-9][0-9][0-9]T-RB')) ) ; INSERT INTO OptionTCodes (option_T_code) VALUES ('135T') ; INSERT INTO OptionTCodes (option_T_code) VALUES ('136T') ; INSERT INTO OptionTCodes (option_T_code) VALUES ('139T') ; INSERT INTO OptionTCodes (option_T_code) VALUES ('135T-RB') ; INSERT INTO OptionTCodes (option_T_code) VALUES ('136T-RB') ; INSERT INTO OptionTCodes (option_T_code) VALUES ('139T-RB') ; CREATE TABLE ItemsSpecs ( option_R_code VARCHAR(10) NOT NULL REFERENCES OptionRCodes (option_R_code), option_T_code VARCHAR(10) NOT NULL REFERENCES OptionTCodes (option_T_code), UNIQUE (option_R_code, option_T_code), item_code VARCHAR(10) NOT NULL REFERENCES Items (item_code), description VARCHAR(30) NOT NULL ) ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('125R', '135T', 'DC135', 'without any Options') ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('127R-RB', '135T-RB', 'DC135', 'with RB only as an Option') ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('125R', '136T', 'DC135', 'with 26 only as an Option') ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('125R', '139T', 'DC135', 'with 29 only as an Option') ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('127R-RB', '136T-RB', 'DC135', 'with 26 and RB only as Options') ; INSERT INTO ItemsSpecs (option_R_code, option_T_code, item_code, description) VALUES ('127R-RB', '139T-RB', 'DC135', 'with 26 and RB only as Options') ; Jamie. -- -- thank you for all your help! ~ angel ~ Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
How to obtain logical combination for query result?
Hi Amy,
i thought I did on the 2nd post after the initial one. but going back it appears that never posted. The "Codes" are guitar programming codes used to program the machine that programs the guitar based on the options the customer selects. So a customer purchases a the base Guitar on an invoice and selects various options to make the end Guitar. This part of the system is in their Accounting system. The basic data is extracted and created into two tables in Access. GuitarHeader (has the customer & invoice info for the base guitar) GuitarDetails (has the invoicenumber and all the options, since an invoice is for one guitar, the guitar is not in this file) Now for the Access part ... ProgramCodes table (i may have to have this split) has: Guitar Code Option GuitarCodeOptionID Because the Code (programming code) is different depending on the Guitar and guitar with certain option combinations to make the end guitar be what it is and do the functions that it does, i need to try to create a file to hold all these Codes to the guitar and guitar and option combinations so that the Codes will print on a label. The label goes on the base Guitar with the options and now we need the Codes to the production team. The look at the Guitar they need to build and would like to use it and this only to program their machines. Currently they have to look at the label for the Guitar and the options printed (since I don't have the Codes on their yet) and have to look-up a worksheet to figure out what Codes to enter into their machines. Having the Codes print with the rest of the Guitar info will save all the manual looking up. The ProgramCodes table is set-up with the Form and subform but I don't have it right because of the combinations. The combination of the options for a unique code could be 2 or 6, etc ... i hope this helps clarify the situation. thanks! Amy Blankenship wrote: You may want to explain in English what all that code is designed to do and why. On Oct 4, 2:50 am, "~ angel ~ via AccessMonster.com" u33627@uwe wrote: [quoted text clipped - 171 lines] -- -- thank you for all your help! ~ angel ~ Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200710/1 |
#6
|
|||
|
|||
How to obtain logical combination for query result?
Jamie,
ok, ultimately no one really needs to understand the logic behind the code. just need to know that the Code(s) are unique depending on the Guitar and Guitar to Options combinations. The Guitar to Options can be just 1 option to the Guitar item which could have 1 unique code or up to 4 unique codes or up to 6 options to the Guitar item with up to 4 unique codes. The user needs to be able to Select the Guitar (which is set-up on the main form now). then 1. enter the Codes for no Options 2. enter the Codes for 1 Option combo 3. enter the Codes for 2 Option combo 4. enter the Codes for 3 Option combo 5. enter the Codes for 4 Option combo 6. enter the Codes for 5 Option combo or 7. enter the Codes for 6 Option combo Each Guitar has different codes, each guitar and option(s) has different codes. there is no reason to try and make sense of the Codes itself. it'll just be the result that needs to be shown based on the combinations. HTH! ~ angel ~ wrote: Hi Jamie, I appreciate you trying to take a wing at this one ... i am a bit confused, and it maybe because of how I explained it. On your suggestion ... Items with item_code and item_name is basically the Guitars table I have all ready and has all the possible Guitar items. Now it seems like you are suggesting I separate the Codes into 2 tables. One for the ones with "R" and ones with "T". R means Rear of the Guitar T mean Top of the Guitar i did not include this but it seems you have pulled that out of the Codes as there's is some logic the Guitar programmer created in the Codes item numbering. Then finally creating the match and putting them into the Item Specs table. is that right? I am now trying to get more info from the Guitar programmer to see if there's any real logic in the Codes he uses in case this R and T identifier does not always work throughout the others. On Oct 4, 2:50 am, "~ angel ~ via AccessMonster.com" u33627@uwe wrote: [quoted text clipped - 79 lines] -- -- thank you for all your help! ~ angel ~ Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200710/1 |
#7
|
|||
|
|||
How to obtain logical combination for query result?
"~ angel ~ via AccessMonster.com" u33627@uwe wrote in message news:7931d8aadaf59@uwe... Hi Amy, i thought I did on the 2nd post after the initial one. but going back it appears that never posted. I was talking to him, not you ;-) |
#8
|
|||
|
|||
How to obtain logical combination for query result?
"~ angel ~ via AccessMonster.com" u33627@uwe wrote in message news:7931c5222482c@uwe... Hi Jamie, I appreciate you trying to take a wing at this one ... i am a bit confused, and it maybe because of how I explained it. On your suggestion ... Items with item_code and item_name is basically the Guitars table I have all ready and has all the possible Guitar items. Now it seems like you are suggesting I separate the Codes into 2 tables. One for the ones with "R" and ones with "T". R means Rear of the Guitar T mean Top of the Guitar i did not include this but it seems you have pulled that out of the Codes as there's is some logic the Guitar programmer created in the Codes item numbering. It seems to me that instead of having a separate table for each that it makes more sense to have a table defining code types where one record would be for R and one would be for T and you could add more records as you needed more zones. Then you can actually concatenate the zones together with the numeric part of the code to come up with the full code. HTH; Amy |
#9
|
|||
|
|||
How to obtain logical combination for query result?
Amy,
not sure if you saw my last post. we can't use the R and T as the identifiers. i just spoke with the manager and ultimately the codes do not have any real logic. it's programming codes for the guitars. so, i need to create in Access for a way for the guitar programmer to enter in the unique Codes for each Guitar and guitar / option combos. the most important thing they have specified is the combinations that make tell what code to use as each code is unique depending on the items. Amy Blankenship wrote: Hi Jamie, I appreciate you trying to take a wing at this one ... [quoted text clipped - 17 lines] there's is some logic the Guitar programmer created in the Codes item numbering. It seems to me that instead of having a separate table for each that it makes more sense to have a table defining code types where one record would be for R and one would be for T and you could add more records as you needed more zones. Then you can actually concatenate the zones together with the numeric part of the code to come up with the full code. HTH; Amy -- thank you for all your help! ~ angel ~ Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200710/1 |
#10
|
|||
|
|||
How to obtain logical combination for query result?
"~ angel ~ via AccessMonster.com" u33627@uwe wrote in message news:7931d8aadaf59@uwe... Hi Amy, i thought I did on the 2nd post after the initial one. but going back it appears that never posted. The "Codes" are guitar programming codes used to program the machine that programs the guitar based on the options the customer selects. So a customer purchases a the base Guitar on an invoice and selects various options to make the end Guitar. This part of the system is in their Accounting system. The basic data is extracted and created into two tables in Access. GuitarHeader (has the customer & invoice info for the base guitar) GuitarDetails (has the invoicenumber and all the options, since an invoice is for one guitar, the guitar is not in this file) Now for the Access part ... ProgramCodes table (i may have to have this split) has: Guitar Code Option GuitarCodeOptionID Because the Code (programming code) is different depending on the Guitar and guitar with certain option combinations to make the end guitar be what it is and do the functions that it does, i need to try to create a file to hold all these Codes to the guitar and guitar and option combinations so that the Codes will print on a label. The label goes on the base Guitar with the options and now we need the Codes to the production team. The look at the Guitar they need to build and would like to use it and this only to program their machines. Currently they have to look at the label for the Guitar and the options printed (since I don't have the Codes on their yet) and have to look-up a worksheet to figure out what Codes to enter into their machines. Having the Codes print with the rest of the Guitar info will save all the manual looking up. The ProgramCodes table is set-up with the Form and subform but I don't have it right because of the combinations. The combination of the options for a unique code could be 2 or 6, etc ... i hope this helps clarify the situation. OK, let me try a full text response to your question. GuitarBase - this is the base guitar the user selected to start out with GuitarBaseID PK AutoNum GuitarBaseDesc etc. OptionTypes - this is the T or R or whatever OptionTypeCode PK this will actually contain the T or R or whatever OptionTypeCodeDesc -Top or Rear or whatever Options - these are the options available, presumably they can be shared across guitars OptionID PK AutoNumber OptionTypeCode FK to OptionTypes OptionCode (Jamie will tell you you need to constrain this to be unique) the important thing is this is your option code without T or R OptionDesc GuitarBaseOptions - this tells you which options are available for which guitar GuitarBaseID FK to GuitarBase OptionID FK to Options ClientGuitar - this is a single particular guitar that has been ordered ClientGuitarID PK AutoNumber GuitarBaseID FK GuitarBase ClientID FK to Clients (not defined here, I figure you can handle it) etc. ClientGuitarDetails ClientGuitarID OptionID So, once you have the OptionID, you can easily match it to a particular guitar and figure out what that option is and how it is coded. Note that the Options Table may not be correct, because it looks like you can have the same option code for both the top and the rear. If that is the case, you would possibly take the OptionTypeCode out of Options and move it to ClientGuitarDetails. But this should be enough to point you in the right direction. HTH; Amy |
Thread Tools | |
Display Modes | |
|
|