Complex query??

[helpangel]

Hello everyone!
i hope someone can help me with either:
1. writing a query to get the results needed (will explain below)
2. write VBA to be used in query to get the results needed
3. or change table(s) to be able to obtain the data in all areas to get the results needed.


The result needed:
How can I get the result of ONLY when there's an exact match of the combinations and not all combinations of the matches?

I have a GuitarOptionDetails and it's linked (LEFT OUT JOIN) to a ProgramCodes.
so the basic result here is displaying correctly. it's showing everything in my GuitarOptionDetails and only where there's matches from the ProgramCodes. Linked on Guitar and Option as these fields are in both sides. The ProgramCodes has the "Code" for the guitar and guitar / option combinations.

Issue, and why I'm seeking help ... i need to somehow change that so it will only show the set of results where the combinations matches.

example
here's the "raw" data from ProgramCodes table

Guitar	Option	Code	ComboID
AE185		185RR	1
AE185		186RHT	1
AE185		187RT	1
AE185	38	185RR	2
AE185	38	186RHT	2
AE185	38	187RT38	2
AE185	BB	185RR	3
AE185	BB	186RHT	3
AE185	BB	188RT-B	3
AE185	38	185RR	4
AE185	38	186RHT	4
AE185	38	188RT38B	4
AE185	BB	185RR	4
AE185	BB	186RHT	4
AE185	BB	188RT38B	4

GuitarOptionDetails is showing the Codes for ComboID's 2, 3, and 4 because an invoice for AE185 has BOTH 38 and BB. Again, the basic LEFT OUTER JOIN query is showing all the Codes for AE185 where there's 38 or BB.

But instead, i need the result to show only the ComboID 4 Codes and not to repeat the same Codes for the ComboID 4 Codes.


I truly hope I made sense and there's a solution for this. I have no idea how to look it up to see if there's any previous posting for this or something like this, etc ...


Thank you in advance!

 

[CraigDolphin]

Well, I think what you are after is a parameter query. If you search using that term on this board, or via google, you should find plenty of examples to guide you.

Unfortunately, your post is quite hard for me to interpret accurately and the tables/relationships in the attached db are very difficult to understand or 'fix' without a better understanding of the business/real-world system you're trying to create a database for.

If you do not already know about table normalization, I strongly recommend that you also do some reading on that topic and re-visit your table structure and relationships afterwards.

 

[boblarson]

If you do not already know about table normalization, I strongly recommend that you also do some reading on that topic and re-visit your table structure and relationships afterwards.

Yes, I agree.

Here's a place to start:


http://support.microsoft.com/default.aspx/kb/100139

 

[helpangel]

what are you talking about as far as i know, the db is "normalized"?

telling me this does not help.

IF there's a particular table that is NOT "normalized" then please tell me specifically because, again, as far as my knowledge they are.

the relationships also show that.

so now I am completely confused.

what i'm asking is for help on either correctly writing a query or VBA to be used in a query or maybe help to change something but not the entire db because it is "normalized".

I'll specify the details out if the attachment by itself is not enough info.

1. Guitars - table of just all the guitar items
2. FinishOptions - table of just all the option items but has ALL the options for the guitar. Neck, headstock, body, finishes, etc ... but for this purpose, only the OptionCategory=Body
3. ProgrammingCodes - table of just all the Fadal (programming) codes (which is just for the body of a guitar as this db or the query result is to get ONLY the programming codes for the guitar and or Guitar and options purchased so the machine can be programmed properly to get the end guitar.
4. ProgramCodes - table with guitar, option, code, comboID. The comboID is solely to differentiate the set of Codes apart. it's an identifier at this point.

There's a Fadal Program Code form with a sub form.
The guitar programmer (end-user) is using this form to enter the combination of codes per guitar and guitar w/ select options.


5. GuitarHeader - table of all the invoices and the guitar item
6. GuitarDetails - table of the invoices and the option items for the guitar (guitar field is not in this table because there can only be one guitar on an invoice so the invoicenumber works, but if the guitar field is needed, it can certainly be added but see no importance for it for the relationship as it stands).


So options can be purchased, it depends what the customer wants on their guitar. A guitar order can have no options selected (purchased) or several options. And depending on the options selected, there are combinations of codes that used to program the guitar making machine.

1. Customer just wants the standard guitar (guitar item by itself), it's the set of codes from the ProgramCodes table ComboID=1 because you'll notice the Option fields is all null. meaning no Body option was selected.
2. Customer wants to add certain options, so say the invoice has the Body option (from example provided and can be seen in the db attached) 38 for the guitar AE185, it needs to show ONLY the codes where ComboID=2 as this set is valid for IF the order for AE185 just has 38 selected. it can also show the codes from the other ComboIDs because those set includes the Body option of 38. Make sense?
3. Customer invoice for another AE185 has Body options on this invoice of 38 AND BB, then from the ProgramCodes table, it needs to ONLY show the set of Codes where the ComboID=4 because this set of codes are only valid when the order is for the 38 AND BB together. it can't show the codes from comboID=2 as this is for ONLY 38 was purcahse or ComboID=3 as this is for ONLY BB was purchased.

i just don't know how to distinguish that in a query let alone in VBA as i'm a newbie when it comes to writing VBA. Just basic queries from tables, etc ... is all and even then, i'm sure I can't do a lot

Anyway, that is why i thought the comboIDs would help but at this point it's only for reporting the results on that, like grouping to see what the codes are for the combinations. As it needs a little more somewhere to show the correct set of codes depending on what was purchased as the Body option combinations for the guitars.


let me know if you have any specific questions or comments and appreciate you taking a look at least.

 

[boblarson]

Here's just a couple of reasons why I said you need to normalize. You also appear to be a little confused on where to store the items.

 

[helpangel]

Hi Bob,
thanks for pointing these out.

1. ok, for the FinishOptions table. the OptionCategory is a Group.
The Body is the group that is being used in this db. it's the OptionItems for the OptionCategory of Body that is significant here. and a way to limit the sub form on the look-up for the Option field to only see the OptionItems for the Body group. I'm sure I can use another table for just OptionCategory that has OptionCategoryID and OptionCategory (for the description).
2. The GuitarHeader, it cannot be changed. these fields you have pointed out has no bearing whatsoever for the need and use for obtaining the programming codes. so again, we can't do anything with that. In fact, if it makes it easier for everyone, i will delete all the other fields EXCEPT for the ones relevant for the need and use for obtaining the programming codes for the guitar invoices.

so, with that said, what can I do now to get closer to getting the set of programming codes to display for the Body options selected (or not) for each invoice?

 

[helpangel]

the only relationship that doesn't have a significance is the ComboID in the ProgramCodes table.

again, i created this to help the user differentiate the set of codes for the combination of Body option items to the guitar.

which also helps me to know which combination uses what Codes.

so i thought some VBA code or complex query then the basic show me Codes where the Guitar and Option matches to the GuitarItem and Option_Item.


thank very much for anything and everything that'll help achieve the end goal here.

Feedback to do this all greatly appreciated!

 

[CraigDolphin]

Helpangel,

First off, I understand that you asked a particular question about queries and maybe you don't 'get' why I mentioned table normalization to you.

What you need to understand is this:
1. We have no idea what your tables represent in the real world.
2. I have played a guitar, but I know nothing about your guitar business or how a user programs a guitar.
3. We have no idea how the tables are supposed to relate with one another. The joins in your relationships window are indeterminate (ie, can't say if they're meant to be one to many or one to one) and your key fields often have different names in different tables.
4. Before we can limit a list of guitars with certain characteristics to a certain combination of those characteristics, we first need to know how to build the larger list. We can't really guess as to which fields in which tables you mean to include in your combinations.
5. A glance at your table structure confuses us more than it helps. The naming convention seems to imply various 'somethings' to do with guitars are the subject of many of the tables but, not knowing your real world situation, it is impossible for us to say if your table structure is correct or not. That is why I prefaced my suggestion by saying "If you do not already know about...."
6. Sorry to be blunt. The post itself is extremely difficult to follow. It's like a whirlwind of thoughts blurted onto the screen.

In short, I strongly suspect your data model is flawed. An example of this:

Your guitar header table contains information about customers that should belong in a separate Customer table. It also contains invoice information which belongs in an Invoice table. You have no direct link between a guitar and guitar details (which instead links invoice number to finish details on a guitar: remember, a single invoice number might be an order for several different guitars all with different finishes)

I would instead expect to see something like the picture (attached). I still don't understand the true nature of your finish options table to be sure that an alternative structure might be better but that's not my problem.

Lastly, I did, in fact, give you the information you needed to make the query yourself. You need to use a parameter query. It was the very first sentence of my post I believe.

[Edit] And while I was posting, Bob has pointed out some more tables that need to be created

 

[helpangel]

Bob,
yes and thank you for keeping this going!

Ok, you see, i thought i've explained what you asked in the prior reply :-(

let me try again ...

The "guitaritem" is the name of the field that holds all the partnumbers for Guitars.
And is the base model or standard item for a guitar. you can just buy the standard item.
But it still needs to be "programmed" to build this standard guitar. Which will need to come from the "Codes" from the ProgramTable.

this is the bottom line project here.

It is really unimportant to anyone else where the data comes from. Basically it is downloaded from their Accounting system. this system cannot be altered to do what is needed here.
So the invoices for just the guitar items is downloaded with the information available and needed to work with on this project for obtaining the correct programming codes for the guitar purchased.

You have to get passed the fact that this part of the equation cannot be changed. it is the data that we have to work with and ultimately, for this particular project, anything else about the customer or invoice, other then what guitaritem and optionitem(s) are on the invoice (purchased). that is it. We are not trying to create the ultimate system here for all-inclusive. it can't happen. it is a separate thing JUST to get the correct programming codes. As any other information but the invoicenumbers, guitaritems and optionitems have no baring whatsoever to the programming codes and their combination of it for the guitaritem and guitaritem w/ optionitems purchase.


All we need to have is:
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.

HTH!

 

[CraigDolphin]

Ok. So let me paraphrase what I think you're saying and see if I'm interpreting correctly.

You receive 'information' from accounting. This information consists of the invoice number, date, and a guitar model identifier along with one or many 'option items' that are desired by the customer. An invoice contains information about only ONE guitar (If this is wrong then you have a problem)

This information is attached to a guitar blank and sent to the workshop. In the workshop, workers must input programming codes into something (a computer I presume) which then customizes the appropriate guitar model blank to add the appropriate 'option items'

Programming codes are different depending on the guitar model and the option chosen. There is one programming code for each valid pairing of guitar model and option-item.

You want to create a database that will provide the correct programming codes to the workers in the workshop, without making them look up the codes for the option items/guitar model themselves?

If the above interpretation is correct, then we should be able to get there, eventually.

However, you must understand that we are not asking questions, or pointing out problems with table design, for our own amusement. We are only trying to understand the situation in order to help you obtain a working solution. If you do not 'fix' problems in your table relationships this will create more issues that could render your db useless now, or in the future. Your fixes to the issues Bob pointed out make it abundantly clear that you do not yet quite understand what we mean by table normalization. That is not meant to be an insult to you, but it is something you need to come to grips with to ensure a working database.

For example, you have set up a ProgrammingCodes table with two fields. CodeID which is an autonumber field and a Code field which is a text field containing code words/strings. The proper primary key field for this table should be the CodeID field (number), not the Code field (text). In your ProgramCodes table, you should be storing the CodeID foreign key (number), not the Code text string.

Likewise for your FinishOptions table. There ought to be an OptionID field (autonumber) in this table which should be the primary key for that table. You store /that/ value in other tables that reference fisnishoptions, not the text string/name of the fisnish option itself.

Now, in your ProgramCodes table there are bigger problems. Consider this:

GuitarItem...OptionItem....Code
AE185 ...<no optionitem>...185RR
AE185...<no optionitem>...186RHT

Both these records exist in your table. Which code do you want returned by the query? There should be only one code per combination of guitar model and OptionItem should there not? Or would you want both 'results' to be returned?

Another issue, in your GuitarDetails table. The OptionItem field is populated with OptionItem codes (e.g., ABD) that are not present in your FinishOption table. Where do these extra codes come from?

I could go on, but I think we need to rebuild your db from scratch, using the information available to you from accounting as a starting point, and then deciding what tables are necessary to accomplish your goals. Sorry to harp on about this but I honestly cannot 'help you' without understanding your data, your process, and your goals completely.

 

[helpangel]

hi there!
yes, most all of your interpretations are correct EXCEPT that there are more then one programming Codes.

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.

the PK and FK is easy to do. i left at with the text so that people can see the actual data value at this point.

i can take care of that as in the end it's the text value that will be displaying.

again, point being, 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?
did that make sense?

i appreciate all the info, just want to make sure i have help focusing the actual issue I'm having problem with. You know, for the purpose of this project ...

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.

 

[CraigDolphin]

Ok. We're getting closer now. I still am missing one piece of the puzzle though. As I mentioned before, there are a lot of OptionItem codes in your GuitarDetails table that are not present in your FinishOptions table. What do these 'extra' codes represent and where do they come from?

Once I get this information, and can fully populate that last table, I will upload a working db for you.

 

[CraigDolphin]

Well, after re-reading your first post I think I may have figured out what those codes are intended to represent so, I have added them to the Finish Options table and use a field called OptionType to split out body finish option from other options.

Check out the attached db. Essentially, to solve your problem I create separate lists (queries) of body options and non-body options associated with each invoice and display those results in two separate subforms. I then use a query with a left join, in combination with the Nz() function, to create a list of invoices versus body options or, where no body option has been entered, the integer 26 which corresponds to the 'None' option (which I've added to the FinishOptions table).

I've modified the table which lists the combinations of guitar model, finish option, and code so that you explictly have to state 'None' (=FinishOptionID #26) for the Finish Option rather than just leaving this as null (which, strictly speaking, means 'not known/knowable')

Then I use a simple query to return the program codes associated with each invoice.

I finally use a query to list all invoices along with their program codes, or, if no matching codes are found, then showing a short message stating this.

I display the results of this query in a third subform on the main form. The master-child relationship between the form and subform filters out the results for everything except the invoice being displayed in the main form.

You could as easily create a parameter query to restrict the query output itself to whatever Invoice you choose.

Have a look and, if you have any questions about how it works I'd be happy to discuss it further, or if I've misunderstood the situation we can re-work it.

 

[helpangel]

Hi!
thank you. i did not receive a notice you had replied. i thought i'd check and contact you for a follow-up and saw that you did in fact post and came up with something.

however, the results on the form for the programming codes for invoice 2365186 is incorrect.

it needs to ONLY show the programming codes for BOTH 38 AND BB not the codes from just 38 or BB which it is still doing and my problem.

how can some VBA code call out just the codes when the invoices for AE185 has BOTH 38 AND BB and not show when it's just 38 or just BB.

this is the result from a simple query which i all ready have and not correct for this purpose.

i hope i made sense here.

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.

Hope that made sense! and thank you very much!!

 

[CraigDolphin]

Ok...I think I'm beginning to understand you a little better now and I finally see what you're trying to do with the extraneous ComboID field in your original db. Unfortunately, the ComboID field approach won't work because your users are selecting individual options rather than combo's of options, so you can't make a direct link via queries without redesigning your tables to make the user choose a combination of options. However, there's a better way, and no VBA required.

First though, let's step back and pin down the results you want in a hypothetical situation to be sure I understand you correctly:

Lets pretend guitar types are limited to one choice (AE185) and focus on a couple of hypothetical finish types (X and Y).

Let's say you have codes for AE185 with finish X of A,B, and C
Let's say you have codes for AE185 with finish Y of A,B, and D

You want:

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

?

Note that this logically also means that if you have Finish Z with codes F,G,H and Finish Z2 with no codes specified, the combination of Z and Z2 will result in no matching codes.

If so, then the attached should work.

However, if the following situation might occur

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

ie the combination might necessitate a code that is not present in either of the 'solo' situations then we are facing a redesign at the table level.

 

[helpangel]

Hi!
ah, thank you! we're so so close now.
it's the later part that you are correct on.

"ie the combination might necessitate a code that is not present in either of the 'solo' situations then we are facing a redesign at the table level."

please advise what I can do to achieve that result:
"AE185 Finish X only ... A,B,C
AE185 Finish Y only....A,B,D
AE185 Finish X and Finish Y ...A,B,E"

 

[helpangel]

THANK YOU! we are so so close now.
your later part is true.

However, if the following situation might occur

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

ie the combination might necessitate a code that is not present in either of the 'solo' situations then we are facing a redesign at the table level.

i'm including another guitar with more options (there can be a combination of several, not just 2 options per guitar) so to show that example, i'm including this:

Guitar	Option	Code	ComboID
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	1

if an invoice has DC200 guitaritem with all three RB, FA, and AC as the body options, then it should ONLY display:
200R-RB 2020T-RB (should not repeat for all 3 when the Code is the same).

please let me know how and what needs to be changed to capture that in such a way that it can display the correct request in a query.

from what I'm discovering, it seems that it'll take VBA code to filter through these variables to display correctly not necessarily the structure change but if structure change can reduce or eliminate VBA, then great ... just let me know how

btw - i do not get notifications for some reason.

 

[CraigDolphin]

This is going to take me some time to think through. I can visualize the structure you need to capture this but I am having a heck of a time trying to take your existing data and convert it into the structure I think you need.

Essentially you need to be able to identify a single number that represents each possible array of guitar, finish1, finish2, finish3...finish_n. You then need to provide codes to match that number. Your current tables do not capture that degree of complexity and I'm not sure if I'm going to be able to figure out how to get there from here with your existing data.

I really don't think that VBA is the way to go on this but I could be wrong.

 

[helpangel]

thank you! it seems this has gotten the best of a lot of great Access gurus!

i hope someone can come up with a solution. it's odd, i would think something like this exists with other companies and therefore other users needing help. it's seems like a 'normal' process of manufacturing something ...

also, on that part

Essentially you need to be able to identify a single number that represents each possible array ... Your current tables do not capture that degree of complexity ...

was the attempt for the combination sets / array using the ComboID to differentiate those various sets. but obviously it's not good enough.

that is what I could muster-up, something to keep the set of the codes (specific combinations) grouped together by using the ComboID as the identifier of the separation in the sets.

so how can I expand on that or change that? you will see in the form and sub-form how the end-user is using to differentiate the sets of the shared programming codes.

let me know!

thank you again!

 

[CraigDolphin]

The problem in the data is that I can use the comboID field to work backwards from the codes to the unique array....but there's no equivalent data to use to get from the invoice to the unique array.

Heading home now...but will have another crack at it tomorrow.

Cheers.