Complex query??

[helpangel]

this is not the greatest and basically the path you are describing that my husband helped with ... again, not the greatest but it's the same thought in having some match back to the invoice.

basically
1. another field in the invoice table that has a combined list of the options
2. replace or another field in the ProgramCodes table also the with combined options, and go ahead and combine the set of the programming codes that goes with that set.

so now both will have, for instance:

InvoiceNumber      GuitarItem         OptionCombo
1234567              AE185               <null> no options at all
2345678              AE185               38
3456789              AE185               BB
4567891              AE185               38 BB

ProgramCodes table could look something like this:

GuitarItem   Codes                               OptionCombo
AE185         185RR 186RHT 187RT          <null>
AE185         185RR 186RHT 187RT38       38
AE185         185RR 186RHT 188RTB         BB
AE185         185RR 186RHT 1838RT38B    38 BB

link this table back to the invoice table and the result would be exactly what I need.
not "normalized" though, but this gives you the match on OptionCombo for the "sets", which is the key.
and doing that, mine as well combine the programming codes here since I have to use the Concatenate() function to combine the values in the end anyways.

 

[helpangel]

to: UNCLE GIZMO - could you jump in?

CraigDolphin has been helping and is very close ... but was wondering if i can get some reinforcement and get to closing this with a couple of good mines sooner?

summary of discussion:
This is a separate db JUST to get the correct set of guitar programming codes to display (print). As any other information but the invoicenumbers, guitaritems, optionitems, and the programming codes have no baring whatsoever to the guitar programming codes and their combination of it for the guitaritem and guitaritem w/ optionitems purchase.

InvoiceNumber
GuitarItem
OptionItem
which is split into the:
GuitarHeader with InvoiceNumber and GuitarItem
GuitarDetails with InvoiceNumber and OptionItems

One(guitaritems)-to-many(optionitems)

then the other tables for obtaining the Codes.
Guitars - GuitarID and GuitarItems
FinishOptions - OptionItems, OptionID, etc ...
ProgrammingCodes - Codes, CodeID
ProgramCodes - where the forms are used and where the data that the end-user (guitar programmer) assigns the combination codes for the specific guitaritems with or without optionitems.


As it stands, the report (which is a label) has the guitar and options (if purchased on the invoice) prints out the guitaritem and optionitem(s). This is that put on the "bare" guitar and sent down to the machine shop. the machine shop employee then has to look at the guitaritem and optionitems on the label and look up the related codes on some chart then enters the codes into the machine.

the objective here is to print those codes related only to the guitar and optionitem(s) so they don't have to manually look it up or enter the wrong code in because they looked at the wrong line on the key or something.


so i do not know how else to ask how can i write a query or something so that the set of Codes that is strictly for the guitar and guitar w/ option(s) are the ONLY Codes that will display.

Again, the ComboID in the ProgramCodes table is there for the end-user to distinguish the set of Codes for the Guitaritems to Optionitems combinations and for me or who's looking at it to know what Codes go with what combinations.

I need to somehow make that work so that is the ONLY set of Codes for the specific combination to print. NOT all the ones that match on the Optionitem(s) alone. Which you will see in the basic query i have.

and to expand on the relationship. it's been updated and in the position with the one-to-one or one-to-many the best I could define them.

guitar item AE185 has 3 codes with no options, all these 3 codes need to display when NO Body options were selected for this guitar on an invoice.

how can I make the Codes display ONLY when there's an exact match for the Codes combinations?
some will have no Option items (body options), some will have 1, or some may have 4, etc ... of which the Codes can be just 2 Codes for the combo set or 3 or more Codes, depending on the combo set. so i do i ONLY show the combo set if AE185 has BOTH 38 AND BB and not show the Codes from the other set for just AE185 w/ 38 or w/ just BB?

which is to make it easier for the machinist so they don't have to manually look up the Codes to enter into their machines.

the ONLY data that differentiates the combo sets is the ComboID. so for this invoice 2365186, it needs to ONLY show the programming codes for the ComboID=4 (from my db).

AND not to repeat 185RR and 186RHT.

please see attached

 

[CraigDolphin]

Ok. Well, I see you've posted some more since last night. Before I get to looking at that, I think I may have cracked it independently (see attached).

It required a complete revamp of the relationships and finally normalizing your combination codes table. I was also able to (hopefully) link up your existing invoice numbers with the new 'CustomerCombination' table pk due to some truly mind-bending query work.

Basically, the user must choose a unique combination of body-options for the guitar type being purchased (equivalent to choosing ComboID=4) from your early posts. The difference here, is that a single combination can have an array of finish options, and an independent array of program codes. Your original table had both in the same table thus requiring you to enter each combination of code and finish option manually.

The user does this using a combo box linked to a crosstab, parameter query that shows all existing combinations of bodyoptions for the specific guitar type. If the combination of options you want is not there, then you create a new combination, add the array of body options you want, and if there are programming codes you want associated with that array, then you enter them also. Then close that popup, and your new combination should be listed in the dropdown list.

I haven't totally bullterproofed it....there may be some instances where some of the code for automating the names of combinations could be buggy (for instance when adding a new model of guitar there are some dlookups that might cause an issue during the first instance of creating a combination for that guitar. You'll also need to create the facility to enter new body options, non-body options, and guitar models. But I'll leave those things to you.


Try playing with it: create new combinations with different arrays of body options and program codes. Then make some dummy invoices and select the combinations and see if the results aren't what you expect.

If this doesn't do it, I think I'll burst!

 

[helpangel]

Hi CraigDolphin!
WOW!
major problem here ... the guitar programmer user will not be assigning the programming codes per order but define all the sets per unique guitar and guitar w/ options and maintain when that changes. this is not to every time there's an invoice as this is inefficient. the person defines the sets and the sets are to be linked to display for the orders, not the other way around.

do you know what i mean?

this person will use the form to set-up all the combinations per guitar and guitar w/ options. this would not change often.

to assign combination sets for each invoice is not an option. it is a lot of data entry where it's not or should not be necessary.

how's this ... can you help me with the SQL statement so that it will only combine the Option_Items for each invoice / guitar where the OptionItem is from the BODY OptionCategory?
i add this routine so it'll populate or create the OptionCombo field. this will be the sole link from the invoice table to the ProgramCodes table for the output desired.

this part:

Concatenate("SELECT Option_Item FROM GuitarHeader WHERE GuitarItem & Option_Item =""" & [GuitarOptionDetails].[GuitarItem] & [GuitarOptionDetails].[Option_Item] & """")

i am getting an error plus it's not going to do it for just Body OptionCategory.

Query SQL:

SELECT GuitarOptionDetails.InvoiceDate, GuitarOptionDetails.InvoiceNumber, GuitarOptionDetails.GuitarItem, GuitarOptionDetails.Option_Item, GuitarOptionDetails.OptionCategory, Concatenate("SELECT Option_Item FROM GuitarHeader WHERE GuitarItem & Option_Item =""" & [GuitarOptionDetails].[GuitarItem] & [GuitarOptionDetails].[Option_Item] & """") AS OptionCombo
FROM GuitarOptionDetails;

or an update query to GuitarHeader into the new field "OptionCombo" where the Option_Items are combined IF the Option_Item is a BODY from the OptionCategory based on the info of the GuitarOptionDetails query like above?

i will need it to combine the BODY Option_Items in order always, so that 38 BB will always update that way and NEVER BB 38 as the OptionCombo on the ProgramCodes table, the user will always enter the combination into the OptionCombo field as 38 BB which will be the match (link) for the final result.

please let me know. i think that this is the best solution without complete rewrite or complex VBA.

 

[CraigDolphin]

I'm not following you.

In the db I provided, you only define each unique 'combination of body options and guitar once. At that time you also populate the array of codes that correspond to that unique combination. This only applies to combinations of body options. A vast number of body-option combinations already exist in the db based on your existing data.

For each invoice you simply select one body-option combination from the existing combinations that matches the particulars of the order. The program codes don't need to be re-defined for each Invoice.

Since non-body options have no bearing at all on program codes, they are stored separately from the body-option combinations and do not factor into body-option configurations at all.
And invoice that specifies:
Guitar X
Body option A
Body option B
Non-Body Option C

has the same body-option configuration as an invoice that specifies
Guitar X
Body option A
Body option B
Non-Body Option D
Non-Body Option E

but different from an invoice that specifies
Guitar Y
Body option A
Body option B
Non-Body Option C

or
Guitar X
Body option A
Body option D
Non-Body Option C

The only time you would need to define a new guitar body-option combination is if someone orders a body-option confiuration that no one else has ever ordered. They can incidentally order as many non-body options as they like, but these do not affect the body-option configuration at all so the body-option combination remains the same.

to assign combination sets for each invoice is not an option. it is a lot of data entry where it's not or should not be necessary.

It's actually less data entry work to pick a configuration than to specify each individual body option for each invoice. You specify the configuration and codes once. Then, for each invoice, you pick the configuration from the list. If the configuration is not in the list, you create it, and the list grows for the next time.

SOMEBODY presently has to input each of the body options for each invoice. So, instead of that, SOMEBODY chooses the configuration for each invoice. If the configuration doesn't exist then you need to create it. There is ZERO alternative to this scheme because you specified that programming code arrays may be entirely different for a combination of body options than can be inferred by looking at the codes for each option alone.

Remember this:

AE185 Finish X only ... A,B,C
AE185 Finish Y only....A,B,D
AE185 Finish X and Finish Y ...A,B,E

So. you MUST define the combination itself as an entity to make your codes work as you specified. This requires that you track that entity. You MUST choose that entity for each invoice.

If you are unwilling to do that, then you have an unresolvable conflict with your stated goal.

 

[helpangel]

Hi,

For each invoice you simply select one body-option combination from the existing combinations that matches the particulars of the order. The program codes don't need to be re-defined for each Invoice.

i am confused, in this you state that you select the combo for each invoice ... this is the problem. that's not going to happen for each time there's a new invoice.
each new invoice should look at the guitar w/ options (if available) and pull the programming codes combo set for that configuration on the invoice, not the other way around per this statement.

the invoice for the guitar with options purchased (if any) comes from the back office database (MAS 200). NOTHING else happens in that system and why the entire invoice data ONLY related to guitar invoices are downloaded into MS Access to do this other part of the equation. To obtain the programming codes for the guitar or guitar w/ options.

it's in the MS Access db the end-user defines all the possible sets and will maintain that if and when necessary but should not change much ones the sets have all been defined (using the UI form and the only one available in the db).

so it's linking that data from ProgramCodes table to the Invoice info and pulling the right set of codes.


anyhow, i think i came up with a solution, not great but it's a lot closer to achieving the end result then anything else thus far.

Run-time error ...
no value given one or more required parameter

UPDATE GuitarOptionDetails SET GuitarOptionDetails.OptionCombo = Concatenate("SELECT Option_Item FROM GuitarHeader WHERE GuitarItem & Option_Item =""" & [GuitarOptionDetails].[GuitarItem] & [GuitarOptionDetails].[Option_Item] & """");

i decided i am going to implement the OptionCombo in each side. i've manually added the values and it's working.

basically i need to create an update query to update the OptionCombo field where:
1. OptionCategory=BODY
2. Combine all the BODY OptionItems as one into the OptionCombo field.
3. For each InvoiceNumber AND GuitarItem.

NOTE: some invoices (guitaritems) may not have a BODY Option_Item, this can place "N" in the OptionCombo BUT only if the invoice does not have a single BODY Option_Item, if it does, it needs to put the combined BODY Option_Item values into the OptionCombo field.

How should I rewrite the SQL to achieve this with the error.

 

[CraigDolphin]

Erk. Whoa there. This is the first time you're mentioned needing to interface with an existing db's output directly. I was assuming you were receiving printouts or somesuch. That puts a whole new wrinkle on things.

However, my earlier point stands....logically, you NEED to match invoice with the unique body-option configuration to get where you want to go. You cannot escape this given your requirements: you can either accept it or keep tilting at windmills.

Your only option that will work would be to import new invoice data from your other db into two normalized tables with the fields

InvoiceID
InvoiceNumber
InvoiceDate
GuitarID

linked to another table with fields...
InvoiceID
FinishOptionID

You can combine this data into one non-normalized list (query).

Then you need to make a crosstab query that will show invoiceID as a row heading, guitar as a row heading, and the column headings being ALL body Options (only). The value would be 1 if there's a match or 0 if not.

Then you need to make an equivalent crosstab for the same permutations that exist in a query that links your CustomerCombination table to the various body option configurations.

You then make a select query and join each named body-option field of the first crosstab to the same field in the second. The fields you want to display are the InvoiceID from the first crosstab and the CustomerCombinationID field from the second.

You can use this query instead of the existing invoice table wherever you need to lookup the CustomerCombinationID in other queries etc.

Of course, to do this you run the risk that a new combination is generated by the accounting software that is not already present in your db. The only way to resolve that is to create every possible combination of body options in advance (you could populate this using append queries etc).

The general principles in my example remain true. You NEED to track using specific combinations, not individual body options, to get where you want to go.

I've outlined how you can there from here given your new revelation about the alternative db. If you wish to pursue an alternative method then all I can say is: good luck with it.

I am, however, burned out on this issue. Sorry we couldn't solve the issue.

 

[helpangel]

thanks!

we will dump this and move onto it another way to simplify more.

thanks for ALL your time and effort!
greatly appreciate it!

 

[CraigDolphin]

Ok. Can't help myself....after all this time and effort I'm making one last attempt.

This requires that you provide the information in your 'GuitarHeader' and 'GuitarDetails' tables as per your original db. I am assuming this is available to you as a file from your other db.

Once this information is entered, the db uses the approach I outlined earlier to determine whether the body option configuration matches one of those already defined in this db. Then, it displays the codes for that configuration.

There's no data entry involved other than adding, or editing body-option configurations. All data entry is assumed to be done into the other db and the two tables mentioned above need to be updated or appended to with new information. I leave how you will populate the two tables up to you.