How to obtain logical combination for query result?

[~ angel ~ via AccessMonster.com]

and another example:
DC200
1 - DC200 - RB - 200R-RB
1 - DC200 - FA - 200R-RB
1 - DC200 - AC - 200R-RB
1 - DC200 - RB - 2020T-RB
1 - DC200 - FA - 2020T-RB
1 - DC200 - AC - 2020T-RB

So, if an invoice has options RB, FA, and AC (all 3)
then the a result for the programming codes for this invoice for guitar
DC200 needs to display:
200R-RB and 2020T-RB

NOTE that if an invoice for DC200 has just RB, the 200R-RB and 2020T-RB
are NOT the programming codes from this list.

Currently a programming code for just an RB option for DC200 has not been
defined.
I just wanted to point this scenario out.

notice that 200R-RB repeats for the 3 Options as well as the 2020T-RB for the
same 3 options.
this is saying the if the DC200 had these 3 Options it needs to ONLY display:
200R-RB and 2020T-RB

They query would display each one 3 times, one per each Option.

So I thought I can do a Group query but that doesn't always work either once
you get into the Guitars with several options where the Code repeat several
times for the various Options and Option Combos.

I don't know if i need to change my data collection or help with a complex
query so the result achieved.

But from what I've gathered thus far, the data collection seems to be fine.

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??

On Oct 4, 6:51 pm, "~ angel ~ via AccessMonster.com" <u33627@uwe>
wrote:

[quoted text clipped - 24 lines]

--

 

[Amy Blankenship]

Option is the the itemnumber
Code is the programming code

not sure how I managed to make you think it's the same.

So the programming code is completely unrelated to the Option (itemnumber)?
What you said before is the programming codes are how the programmers
program the guitars based on what options are selected. If this is not the
case and there is NO relation between what options are selected and what the
program codes are, what on earth are we talking about?

Guitar (itemnumber) can have many Options (also itemnumbers)

Yes, and I gave you a table design that would allow you to specify which
options would be available on which guitar. Unfortunately, I can lead you
to the water but I can't make you drink.

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.

OK, why would there be more options than codes? I suspect at least part of
the reason you can't get the design of this database right is that you
cannot explain in words what the actual relationship between all your
entities is. Since you can't do that, you haven't got a hope of designing
tables that accurately model the relationships.

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).

Yes, this is what the ClientGuitar and ClientGuitarOptions table were
supposed to do for you.

I'm sorry. I've done what I can to try to enhance your understanding of
what structure you need to solve the problem, but you're holding on so tight
to your existing, flawed table structure that you won't open yourself to the
solution that will work for you.

 

[Amy Blankenship]

and another example:
DC200
1 - DC200 - RB - 200R-RB
1 - DC200 - FA - 200R-RB
1 - DC200 - AC - 200R-RB
1 - DC200 - RB - 2020T-RB
1 - DC200 - FA - 2020T-RB
1 - DC200 - AC - 2020T-RB

So, if an invoice has options RB, FA, and AC (all 3)
then the a result for the programming codes for this invoice for guitar
DC200 needs to display:
200R-RB and 2020T-RB

NOTE that if an invoice for DC200 has just RB, the 200R-RB and 2020T-RB
are NOT the programming codes from this list.

Currently a programming code for just an RB option for DC200 has not
been
defined.
I just wanted to point this scenario out.

notice that 200R-RB repeats for the 3 Options as well as the 2020T-RB for
the
same 3 options.
this is saying the if the DC200 had these 3 Options it needs to ONLY
display:
200R-RB and 2020T-RB

They query would display each one 3 times, one per each Option.

Don't worry at this point what the query will be. Worry about getting all
the data modeled properly. You can literally display your data in any form
you want if it is in there and organized right.

 

[~ angel ~ via AccessMonster.com]

InvoiceDate InvoiceNumber GuitarItem Option_Item Option Code ComboID
9/7/07 - 2365186 - AE185 - TN
9/7/07 - 2365186 - AE185 - STF
9/7/07 - 2365186 - AE185 - AB
above 3 options for this item AE185 on this particular invoice has no Code,
hence the "Option", "Code", and "ComboID" fields are null which are from the
ProgramCodes table

9/7/07 2365186 AE185 38 38 187RT38 2
9/7/07 2365186 AE185 38 38 185RR 2
9/7/07 2365186 AE185 38 38 186RHT 2

9/7/07 2365186 AE185 BB BB 186RHT 3
9/7/07 2365186 AE185 BB BB 188RT-B 3
9/7/07 2365186 AE185 BB BB 185RR 3

9/7/07 2365186 AE185 BB BB 188RT38B 4
9/7/07 2365186 AE185 BB BB 185RR 4
9/7/07 2365186 AE185 38 38 188RT38B 4
9/7/07 2365186 AE185 38 38 186RHT 4
9/7/07 2365186 AE185 BB BB 186RHT 4
9/7/07 2365186 AE185 38 38 185RR 4


Then the grouped query does this:
InvoiceDate InvoiceNumber GuitarItem ComboID
9/7/07 - 2365186 - AE185
9/7/07 - 2365186 - AE185 - 2
9/7/07 - 2365186 - AE185 - 3
9/7/07 - 2365186 - AE185 - 4

See how it lists all the Combo and not even showing the ComboID 1 here.

Based on the info for invoice 2365186 for Guitar AE185
the query result should only be, because the Options that this item on this
invoice has is BOTH 38 & BB

Hope this helps in understanding what i've tried to describe so far.
In the previous message is what it should ONLY show:
ComboID 4 and just the 3 CODES
185RR 186RHT 188RT38B

and another example:
DC200

[quoted text clipped - 26 lines]

They query would display each one 3 times, one per each Option.

Don't worry at this point what the query will be. Worry about getting all
the data modeled properly. You can literally display your data in any form
you want if it is in there and organized right.

 

[~ angel ~ via AccessMonster.com]

another invoice with AE185 as an example:
InvoiceDate InvoiceNumber GuitarItem Option_Item Option Code ComboID
9/7/07 2365203 AE185 NIN
9/7/07 2365203 AE185 38 38 187RT38 2
9/7/07 2365203 AE185 38 38 186RHT 2
9/7/07 2365203 AE185 38 38 185RR 2
9/7/07 2365203 AE185 38 38 188RT38B 4
9/7/07 2365203 AE185 38 38 186RHT 4
9/7/07 2365203 AE185 38 38 185RR 4

in this case it should ONLY show ComboID = 2 because the only Option with
possible Code is Option = 38
so the result should be:
185RR 186RHT 187RT38 ComboID 2

InvoiceDate InvoiceNumber GuitarItem Option_Item Option Code ComboID
9/7/07 - 2365186 - AE185 - TN
9/7/07 - 2365186 - AE185 - STF
9/7/07 - 2365186 - AE185 - AB
above 3 options for this item AE185 on this particular invoice has no Code,
hence the "Option", "Code", and "ComboID" fields are null which are from the
ProgramCodes table

9/7/07 2365186 AE185 38 38 187RT38 2
9/7/07 2365186 AE185 38 38 185RR 2
9/7/07 2365186 AE185 38 38 186RHT 2

9/7/07 2365186 AE185 BB BB 186RHT 3
9/7/07 2365186 AE185 BB BB 188RT-B 3
9/7/07 2365186 AE185 BB BB 185RR 3

9/7/07 2365186 AE185 BB BB 188RT38B 4
9/7/07 2365186 AE185 BB BB 185RR 4
9/7/07 2365186 AE185 38 38 188RT38B 4
9/7/07 2365186 AE185 38 38 186RHT 4
9/7/07 2365186 AE185 BB BB 186RHT 4
9/7/07 2365186 AE185 38 38 185RR 4

Then the grouped query does this:
InvoiceDate InvoiceNumber GuitarItem ComboID
9/7/07 - 2365186 - AE185
9/7/07 - 2365186 - AE185 - 2
9/7/07 - 2365186 - AE185 - 3
9/7/07 - 2365186 - AE185 - 4

See how it lists all the Combo and not even showing the ComboID 1 here.

Based on the info for invoice 2365186 for Guitar AE185
the query result should only be, because the Options that this item on this
invoice has is BOTH 38 & BB

Hope this helps in understanding what i've tried to describe so far.
In the previous message is what it should ONLY show:
ComboID 4 and just the 3 CODES
185RR 186RHT 188RT38B
[quoted text clipped - 5 lines]

the data modeled properly. You can literally display your data in any form
you want if it is in there and organized right.

 

[~ angel ~ via AccessMonster.com]

GuitarProgramCodes.GuitarItem, Max(Concatenate("SELECT Code FROM ProgramCodes

WHERE Guitar = """ & [GuitarItem] & """ And Option Is Null ")) AS Codes
FROM GuitarProgramCodes
GROUP BY GuitarProgramCodes.InvoiceDate, GuitarProgramCodes.InvoiceNumber,
GuitarProgramCodes.GuitarItem
ORDER BY GuitarProgramCodes.InvoiceNumber;

Results in: which is not correct either although it is showing the correct
Codes
combination of the 3 listed for when AE185 doesn’t have ANY Options. But this
is
showing the Null Options Codes when there are NO matching Options. Not true.

GuitarProgramCodesSummaryNulls
InvoiceDate InvoiceNumber GuitarItem Codes
9/7/07 2365186 AE185 185RR 186RHT 187RT
9/7/07 2365203 AE185 185RR 186RHT 187RT
9/7/07 2365428 AE185 185RR 186RHT 187RT
9/11/07 2366116 AE185 185RR 186RHT 187RT
9/12/07 2366228 AE185 185RR 186RHT 187RT
9/13/07 2366532 AE185 185RR 186RHT 187RT
9/18/07 2367519 AE185 185RR 186RHT 187RT
9/26/07 2368963 AE185 185RR 186RHT 187RT
9/28/07 2369326 AE185 185RR 186RHT 187RT

And this
SELECT GuitarProgramCodesDetails1.InvoiceDate,
GuitarProgramCodesDetails1.InvoiceNumber, GuitarProgramCodesDetails1.
GuitarItem,
Max(Concatenate("SELECT Code FROM ProgramCodes WHERE Guitar & Option =""" &
[GuitarItem] & [Option] & """")) AS Codes
FROM GuitarProgramCodesDetails1
GROUP BY GuitarProgramCodesDetails1.InvoiceDate,
GuitarProgramCodesDetails1.InvoiceNumber, GuitarProgramCodesDetails1.
GuitarItem
ORDER BY GuitarProgramCodesDetails1.InvoiceNumber;


Is actually showing the same result, at least for this item:


GuitarProgramCodesSummary
InvoiceDate InvoiceNumber GuitarItem Codes
9/7/07 2365186 AE185 185RR 186RHT 187RT
9/7/07 2365203 AE185 185RR 186RHT 187RT
9/7/07 2365428 AE185 185RR 186RHT 187RT
9/11/07 2366116 AE185 185RR 186RHT 187RT
9/12/07 2366228 AE185 185RR 186RHT 187RT
9/13/07 2366532 AE185 185RR 186RHT 187RT
9/18/07 2367519 AE185 185RR 186RHT 187RT
9/26/07 2368963 AE185 185RR 186RHT 187RT
9/28/07 2369326 AE185 185RR 186RHT 187RT

So what and where should I make the changes?

and another example:
DC200

[quoted text clipped - 26 lines]

They query would display each one 3 times, one per each Option.

Don't worry at this point what the query will be. Worry about getting all
the data modeled properly. You can literally display your data in any form
you want if it is in there and organized right.

 

[Amy Blankenship]

another invoice with AE185 as an example:
InvoiceDate InvoiceNumber GuitarItem Option_Item Option Code ComboID
9/7/07 2365203 AE185 NIN
9/7/07 2365203 AE185 38 38 187RT38 2
9/7/07 2365203 AE185 38 38 186RHT 2
9/7/07 2365203 AE185 38 38 185RR 2
9/7/07 2365203 AE185 38 38 188RT38B 4
9/7/07 2365203 AE185 38 38 186RHT 4
9/7/07 2365203 AE185 38 38 185RR 4

in this case it should ONLY show ComboID = 2 because the only Option with
possible Code is Option = 38
so the result should be:
185RR 186RHT 187RT38 ComboID 2

InvoiceDate InvoiceNumber GuitarItem Option_Item Option Code ComboID
9/7/07 - 2365186 - AE185 - TN
9/7/07 - 2365186 - AE185 - STF
9/7/07 - 2365186 - AE185 - AB
above 3 options for this item AE185 on this particular invoice has no
Code,
hence the "Option", "Code", and "ComboID" fields are null which are from
the
ProgramCodes table

9/7/07 2365186 AE185 38 38 187RT38 2
9/7/07 2365186 AE185 38 38 185RR 2
9/7/07 2365186 AE185 38 38 186RHT 2

9/7/07 2365186 AE185 BB BB 186RHT 3
9/7/07 2365186 AE185 BB BB 188RT-B 3
9/7/07 2365186 AE185 BB BB 185RR 3

9/7/07 2365186 AE185 BB BB 188RT38B 4
9/7/07 2365186 AE185 BB BB 185RR 4
9/7/07 2365186 AE185 38 38 188RT38B 4
9/7/07 2365186 AE185 38 38 186RHT 4
9/7/07 2365186 AE185 BB BB 186RHT 4
9/7/07 2365186 AE185 38 38 185RR 4

Then the grouped query does this:
InvoiceDate InvoiceNumber GuitarItem ComboID
9/7/07 - 2365186 - AE185
9/7/07 - 2365186 - AE185 - 2
9/7/07 - 2365186 - AE185 - 3
9/7/07 - 2365186 - AE185 - 4

See how it lists all the Combo and not even showing the ComboID 1 here.

Based on the info for invoice 2365186 for Guitar AE185
the query result should only be, because the Options that this item on
this
invoice has is BOTH 38 & BB

Hope this helps in understanding what i've tried to describe so far.
In the previous message is what it should ONLY show:
ComboID 4 and just the 3 CODES
185RR 186RHT 188RT38B

and another example:
DC200

[quoted text clipped - 5 lines]

the data modeled properly. You can literally display your data in any
form
you want if it is in there and organized right.

I give up. I have asked you and asked you to tell me the real world
relationship between the program code and the options, why (in real world
terms) there would be a null program code for an option, and why there would
be more than one option with the same code. You respond with bunches of
data from your failed table structure and query results that are not doing
what you want in part because you are not getting the table structure right.
I cannot help you, as you are not able to respond to questions
appropriately. Good luck with it.

-Amy

 

[~ angel ~ via AccessMonster.com]

Amy,
i don't know and they aren't not telling me.
they said it's really un-important.

and i've explained to you that the programming codes are simply the codes
used to program the guitars.

Some guitars don't need any programming, can you not understand that?

these are all pretty much electric guitars.

so some can be just "standard", no programming necessary.

Options are things that you or I could purchase additional and therefore
programming required to do what these add'l options were purchase.

that's all we really need to know.

the guitar programmer is the one that is assigning what codes (if any) are
for what Guitar items and Options.
in the end, we really don't need to understand or know that.

All we need to know are that certain combinations have different programs.


I have to assume you are simply not capable as this seems pretty complex.

thanks for trying.

another invoice with AE185 as an example:
InvoiceDate InvoiceNumber GuitarItem Option_Item Option Code ComboID

[quoted text clipped - 61 lines]
I give up. I have asked you and asked you to tell me the real world
relationship between the program code and the options, why (in real world
terms) there would be a null program code for an option, and why there would
be more than one option with the same code. You respond with bunches of
data from your failed table structure and query results that are not doing
what you want in part because you are not getting the table structure right.
I cannot help you, as you are not able to respond to questions
appropriately. Good luck with it.

-Amy

 

[Amy Blankenship]

Amy,
i don't know and they aren't not telling me.
they said it's really un-important.

and i've explained to you that the programming codes are simply the codes
used to program the guitars.

Some guitars don't need any programming, can you not understand that?

these are all pretty much electric guitars.

so some can be just "standard", no programming necessary.

Options are things that you or I could purchase additional and therefore
programming required to do what these add'l options were purchase.

that's all we really need to know.

the guitar programmer is the one that is assigning what codes (if any) are
for what Guitar items and Options.
in the end, we really don't need to understand or know that.

All we need to know are that certain combinations have different programs.


I have to assume you are simply not capable as this seems pretty complex.

It's not possible to properly structure a database without knowing what real
world conditions are being modeled. However, the first design I gave you
should be a good starting point for you.

Good luck!