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:

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

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

--
thank you for all your help!

~ angel ~

Message posted via http://www.accessmonster.com

"~ 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

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

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

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.

--

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.

--

"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

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

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

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