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 |
#11
|
|||
|
|||
How to obtain logical combination for query result?
Hi Amy,
sort of ... I have the Guitars and Options tables set-up I also have the Codes table of all the unique codes which the programmer will add all them shortly. I am not sure on these parts of what you have suggested below. Yes, options can interchangeable used by any guitar as far as I know. Let's not get hung-up on the T or R example, here's a code with neither Code CodeID B52-2V 50 What do you mean here "OptionTypeCode FK to OptionTypes"? Each of the Options in the Options table has a unique OptionID, although each option itself is a unique itemnumber Each of the Guitars in the Guitars table has a unique GuitarID, although each guitar itself is a unique itemnumber Each of the Codes in the ProgrammingCodes table has a unique CodeID My ProgramCodes table: GuitarID OptionID CodeID GuitarOptionCodeID I don't get the GuitarBaseOptions part, there is no such thing?? so then the Client portion is also confusing. so from the ProgramCodes table: i'm missing the part of how the I can get the 1 GuitarOptionCodeID for this scenario: GuitarID with a CodeID and the 1 GuitarOptionCodeID for this scenario as well: GuitarID with 1 OptionID BUT how do I get the GuitarOptionID for when the GuitarID and OptionIDs have several CodeIDs? What links the GuitarID and OptionIDs combi to obtain the GuitarOptionID? GuitarID OptionID CodeID GuitarOptionCodeID C66M - - B52-2V - 76 C66M - CAP - B52-2V - 77 C66M - HSS - B52-2V - 78 C66M - - B521-2R - 79 C66M - CAP - B521-2R - 80 C66M - HSS - B521-2R - 81 C66M - - B521-2TV - 82 C66M - CAP - B531-2TV - 83 C66M - HSS - B541-2TV - 84 results would be: C66M - NULL - B52-2V and B521-2R and B521-2TV C66M - CAP - B52-2V C66M - HSS - B52-2V C66M - CAP and HSS - B531-2R and B541-2TV since the GuitarOptionCodeID is unique for every entry, i can't use that as the unique code identifier for the guitar and guitar option combo. Amy Blankenship wrote: Quote:
thank you for all your help! ~ angel ~ Message posted via http://www.accessmonster.com |
#12
|
|||
|
|||
How to obtain logical combination for query result?
"~ angel ~ via AccessMonster.com" u33627@uwe wrote in message news:7932973e50798@uwe... Hi Amy, sort of ... I have the Guitars and Options tables set-up I also have the Codes table of all the unique codes which the programmer will add all them shortly. I am not sure on these parts of what you have suggested below. Yes, options can interchangeable used by any guitar as far as I know. Let's not get hung-up on the T or R example, here's a code with neither Code CodeID B52-2V 50 That is what the "or whatever" is for. Possibly you have a B type? Bottom? It is a geneal catchall that allows you to have full control over that piece of the option code that is shared and, as importantly, a way to call up what the meaning of that is at weill. What do you mean here "OptionTypeCode FK to OptionTypes"? Foreign key. So you link out to the OptionTypeCode table, and not only do you manage to auto fill the T, P, B, or whatever, you also are able to present a user-readable description of what that means. Each of the Options in the Options table has a unique OptionID, although each option itself is a unique itemnumber Each of the Guitars in the Guitars table has a unique GuitarID, although each guitar itself is a unique itemnumber Each of the Codes in the ProgrammingCodes table has a unique CodeID Well if you're not willing to revisit your table structure at all then I'm not sure I can help you. But I am not exactly sure how the ProgrammingCodes relate to the options. I suspect that the OptionCode I suggested would fit that bill, once you concatenate the related option codes together. My ProgramCodes table: GuitarID OptionID CodeID GuitarOptionCodeID OK, so you have a base GuitarID, and that now means that any sets of options are referring to that base guitar, not to one specific guitar, which is what the ClientGuitar table would have gained you. Once you have defined the ClientGuitar, then you can have multiple ClientGuitarOptions applied to it. I don't get the GuitarBaseOptions part, there is no such thing?? There should be, unless all guitars can have all options. You need to have a way to present to the user which options are available on which guitar. so then the Client portion is also confusing. You need to be able to call out a particular guitar for that one client. You cannot apply the options to the definition of the base model, which is what your current table structure tries to do. so from the ProgramCodes table: i'm missing the part of how the I can get the 1 GuitarOptionCodeID for this scenario: GuitarID with a CodeID and the 1 GuitarOptionCodeID for this scenario as well: GuitarID with 1 OptionID BUT how do I get the GuitarOptionID for when the GuitarID and OptionIDs have several CodeIDs? What links the GuitarID and OptionIDs combi to obtain the GuitarOptionID? The OptionID be associated with only one CodeID. Each option should be its own thing. If you have a business case for one option having multiple code ID's you should share it. That will require a table structure that is even more complex. However, I suspect you're doing something wonky with your tables that is confusing you. GuitarID OptionID CodeID GuitarOptionCodeID C66M - - B52-2V - 76 C66M - CAP - B52-2V - 77 C66M - HSS - B52-2V - 78 C66M - - B521-2R - 79 C66M - CAP - B521-2R - 80 C66M - HSS - B521-2R - 81 C66M - - B521-2TV - 82 C66M - CAP - B531-2TV - 83 C66M - HSS - B541-2TV - 84 results would be: C66M - NULL - B52-2V and B521-2R and B521-2TV C66M - CAP - B52-2V C66M - HSS - B52-2V C66M - CAP and HSS - B531-2R and B541-2TV Your first problem is you are trying to associate the options with the _definition_ of a base guitar rather than with the _instance_ of a guitar that is being configured by a client. I am not clear on why you would have a null OptionID, yet have a CodeID. My understanding is that your codes are in place in order to get the options programmed. So the information of what code to use should be in your Options table, not lumped in with everything else. since the GuitarOptionCodeID is unique for every entry, i can't use that as the unique code identifier for the guitar and guitar option combo. Yes. You need to structure all your tables so that ultimately you can get the information out of them that you need. It is not a case of just adding a table and throwing everything in and hoping it works. All your tables need to work together for good ;-). You really need to read up on normalization. I can tell you this stuff until I am blue in the face, but unless you understand the underlying concepts you will still keep trying to hold on to a structure that doesn't work. -Amy |
#13
|
|||
|
|||
How to obtain logical combination for query result?
R and T do have meaning in the real world, and if nothing else can be used
to help present the options to the user in a logical manner to make it easier to select the ones they want. "~ angel ~ via AccessMonster.com" u33627@uwe wrote in message news:79322989c460b@uwe... 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 |
#14
|
|||
|
|||
How to obtain logical combination for query result?
this is the set-up i think i've gathered that i need to do
GuitarOptionCodeID Guitar Option Code 1 - C66M - - B52-2V 1 - C66M - - B521-2R 1 - C66M - - B521-2TV 2 - C66M - CAP - B52-2V 2 - C66M - CAP - B521-2R 2 - C66M - CAP - B531-2TV 3 - C66M - HSS - B52-2V 3 - C66M - HSS - B521-2R 3 - C66M - HSS - B541-2TV now, not sure how to assign the GuitarOptionCodeID and when the Guitar and options change?? so not sure how to Amy Blankenship wrote: R and T do have meaning in the real world, and if nothing else can be used to help present the options to the user in a logical manner to make it easier to select the ones they want. Amy, not sure if you saw my last post. [quoted text clipped - 29 lines] Amy -- thank you for all your help! ~ angel ~ Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200710/1 |
#15
|
|||
|
|||
How to obtain logical combination for query result?
On Oct 4, 4:04 pm, "Amy Blankenship"
wrote: You may want to explain in English what all that code is designed to do and why. Do you ask that question whenever someone posts VBA code in direct response to an OP? When Jet syntax is enhanced to include code comments, I'll use them ;-) Amy, it's a matter of style and content. When describing table structure in a newsgroup post, I prefer SQL DDL because it's explicit, exacting, concise, etc; also, some Jet features are only available via SQL DDL e.g. CHECK constraints. I note from previous posts that you don't read SQL DDL; if you are willing to learn, I recommend: Intermediate Microsoft Jet SQL for Access 2000 http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx Myself, I've no objection to your style but I tend to be bothered when content is lacking. As we've seen, you describe your table structures using natural language and on occasions have fallen short on content IMO e.g. where you have proffered an autonumber PK but have made no mention of candidate keys. Jamie. -- |
#16
|
|||
|
|||
How to obtain logical combination for query result?
On Oct 4, 6:51 pm, "~ angel ~ via AccessMonster.com" u33627@uwe
wrote: My ProgramCodes table: GuitarID OptionID CodeID GuitarOptionCodeID I don't see how this table can work because, from the sample data in your OT, it seems all options for the item have to be considered _in combination_ when determining the code. For example, it cannot be said that guitar item 'DC135' with option 26 generates code '139T' because you've told us that when RB is also on the same guitar '139T' will not apply but '139T-RB' instead. You have confirmed elsewhere in this thread that there's no logic to this i.e. it's not simply a case of appending '-RB' to the code. Your OT showed us how the legal combinations for between zero and two options all resulted in _exactly_ two unique codes. I don't know how to extrapolate this for up to six options generating _up to_ four unique codes. I'd need to see a *lot* more sample data to be comfortable to suggesting a structure e.g. to know how different items produce different codes for the same options, etc. Jamie. -- |
#17
|
|||
|
|||
How to obtain logical combination for query result?
"Jamie Collins" wrote in message ups.com... On Oct 4, 4:04 pm, "Amy Blankenship" wrote: You may want to explain in English what all that code is designed to do and why. Do you ask that question whenever someone posts VBA code in direct response to an OP? When Jet syntax is enhanced to include code comments, I'll use them ;-) Amy, it's a matter of style and content. When describing table structure in a newsgroup post, I prefer SQL DDL because it's explicit, exacting, concise, etc; also, some Jet features are only available via SQL DDL e.g. CHECK constraints. If the user can't understand the content, the style doesn't matter. You haven't taught her any principles she can use to tailor your solution to her own circumstances or even given her pointers to search terms she could use to enhance her understanding. I note from previous posts that you don't read SQL DDL; if you are willing to learn, I recommend: I don't take SQL DDL as any sort of explanation of anything, which it isn't. You cannot teach users anything about how to design databases this way, particularly when the design you come up with is simply wrong. Intermediate Microsoft Jet SQL for Access 2000 http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx Myself, I've no objection to your style but I tend to be bothered when content is lacking. As we've seen, you describe your table structures using natural language and on occasions have fallen short on content IMO e.g. where you have proffered an autonumber PK but have made no mention of candidate keys. Yeah Mr. Hot Stuff. You recommended two different tables, just because you noticed many codes had different prefixes. You're an Access genius! Just because you like to prove you can write unintelligible (to the average user) code all the time doesn't mean you actually ARE smarter than anyone else. It just means you like to APPEAR smarter. -Amy |
#18
|
|||
|
|||
How to obtain logical combination for query result?
I think you need to step back and tell us what the actual relationship is
between the Option and the OptionCode. How can different options have the same code? You need to quit being hung up on the table design you thought would work, and allow yourself to be more open to the advice you have asked for. -Amy "~ angel ~ via AccessMonster.com" u33627@uwe wrote in message news:7933e9f13a665@uwe... this is the set-up i think i've gathered that i need to do GuitarOptionCodeID Guitar Option Code 1 - C66M - - B52-2V 1 - C66M - - B521-2R 1 - C66M - - B521-2TV 2 - C66M - CAP - B52-2V 2 - C66M - CAP - B521-2R 2 - C66M - CAP - B531-2TV 3 - C66M - HSS - B52-2V 3 - C66M - HSS - B521-2R 3 - C66M - HSS - B541-2TV now, not sure how to assign the GuitarOptionCodeID and when the Guitar and options change?? so not sure how to Amy Blankenship wrote: R and T do have meaning in the real world, and if nothing else can be used to help present the options to the user in a logical manner to make it easier to select the ones they want. Amy, not sure if you saw my last post. [quoted text clipped - 29 lines] Amy -- thank you for all your help! ~ angel ~ Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200710/1 |
#19
|
|||
|
|||
How to obtain logical combination for query result?
Option is the the itemnumber
Code is the programming code not sure how I managed to make you think it's the same. Guitar (itemnumber) can have many Options (also itemnumbers) There can be many Codes (programming code) to the following combinations Just Guitar without any Options Guitar and 1 Option Guitar and 2 Option, etc ... up to how every many but right now there is up to 6 options to a Guitar that can have up to 4 Codes. Currently 4 Codes to any combinations the most one Guitar (which can also be said as one Invoice as the same Guitar w/ different and/or same Options can be on many invoices). HTH! Amy Blankenship wrote: I think you need to step back and tell us what the actual relationship is between the Option and the OptionCode. How can different options have the same code? You need to quit being hung up on the table design you thought would work, and allow yourself to be more open to the advice you have asked for. -Amy this is the set-up i think i've gathered that i need to do [quoted text clipped - 33 lines] Amy -- thank you for all your help! ~ angel ~ Message posted via http://www.accessmonster.com |
#20
|
|||
|
|||
How to obtain logical combination for query result?
185RR is RIGHT REAR (same for all 3)
186RHT RIGHT HOLLOW TOP (same for all 3) 187RT is RIGHT TOP (top for standard) 187RT38 is RIGHT TOP FOR 38 Option (top for 38 option) 188RT-B is RIGHT TOP FOR BB Option (top for BB option) "We have 3 guitar models that use,or have 2 options combined. From example Model DC135 I entered DC135 standard 2 codes then RB option 2 codes then 26 option 2 codes then 29 option 2 codes these are all good ! Now I need codes to print for 2 options combined, for DC135 RB & 26 options together there is 2 codes for that,and RB & 29 options together there are 2 codes for that" and other Guitars and combinations I've changed since and have come up with this ComboID Guitar Option Code 1 - C66M - - B52-2V 1 - C66M - - B521-2R 1 - C66M - - B521-2TV 2 - C66M - CAP - B52-2V 2 - C66M - CAP - B521-2R 2 - C66M - CAP - B531-2TV 3 - C66M - HSS - B52-2V 3 - C66M - HSS - B521-2R 3 - C66M - HSS - B541-2TV here the B52-2V and B521-2R repeats for each of the others. AE185 1 - AE185 - - 185RR 1 - AE185 - - 186RHT 1 - AE185 - - 187RT 2 - AE185 - - 185RR 2 - AE185 - - 186RHT 2 - AE185 - - 187RT38 2 - AE185 - - 185RR 2 - AE185 - - 186RHT 2 - AE185 - - 188RT38B NO OPTIONS 3 CODES = 185RR 186RHT 187RT 38 OPTION NEEDS 3 CODES = 185RR 186RHT 187RT38 BB OPTION NEEDS 3 CODES = 185RR 186RHT 188RT-B 38 & BB OPTIONS 3 CODES = 185RR 186RHT 188RT38B Notice here that the 185RR and 186RHT repeats in each combination and the last code (only in this item number example) is different. I thought the Combo field will help to keep the Code for the Fadal Option together. But when you look at Combo 4, the same code repeats. I can’t have the result repeat the Code, it needs to show it only once for the Combo. So if an invoice 111 for item AE185 and the options for this invoice happens to have BOTH 38 & BB, it needs to ONLY show 38 & BB OPTIONS 3 CODES = 185RR 186RHT 188RT38B If an invoice 222 for item AE185 just had BB, it needs to show BB OPTION NEEDS 3 CODES = 185RR 186RHT 188RT-B So invoice 111 can’t show 188RT-B even though it has an Option of BB BECAUSE it also has Option 38 you know?? Jamie Collins wrote: On Oct 4, 6:51 pm, "~ angel ~ via AccessMonster.com" u33627@uwe wrote: My ProgramCodes table: GuitarID OptionID CodeID GuitarOptionCodeID I don't see how this table can work because, from the sample data in your OT, it seems all options for the item have to be considered _in combination_ when determining the code. For example, it cannot be said that guitar item 'DC135' with option 26 generates code '139T' because you've told us that when RB is also on the same guitar '139T' will not apply but '139T-RB' instead. You have confirmed elsewhere in this thread that there's no logic to this i.e. it's not simply a case of appending '-RB' to the code. Your OT showed us how the legal combinations for between zero and two options all resulted in _exactly_ two unique codes. I don't know how to extrapolate this for up to six options generating _up to_ four unique codes. I'd need to see a *lot* more sample data to be comfortable to suggesting a structure e.g. to know how different items produce different codes for the same options, etc. Jamie. -- -- thank you for all your help! ~ angel ~ Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200710/1 |
Thread Tools | |
Display Modes | |
|
|