VBA code help to pull only actual set of data??

[vbahelp07]

Hello,
i need some major help. i think that the only way to obtain the result required is through VBA code (which i struggle with because I can't get it %-)


ok, the objective for this project is:
To eliminate manual look-up on a physical chart the correct set of guitar programming codes combinations for each order to save time and mistakes.


what is going on now:
1. An order is placed for a guitar (standard / bare) with options (of which are Body [Fadal] options) that customers can purchase to get a specific type of guitar. this is done in the accounting system. the orders for guitars are extracted and put into MS Access to be able to do this other function that the accounting system cannot do.
2. MS Access has a few tables:

1) GuitarHeader (invoicenumber and guitaritem)
2) GuitarDetails (invoicenumber and optionitems)
one(guitaritem)-to-many(optionitems, just Body options)
3) GuitarItems (GuitarID and GuitarItem)
4) FinishOptions (OptionID, OptionItem and OptionCategory (identifies which set are the Body options)
5) ProgrammingCodes (CodeID and Codes)
6) ProgramCodes (GuitarID, OptionID, CodeID, and ComboID)

the ComboID (combination identifier) I put so the end-user can identify the set of records from the other sets because the same OptionItem can be used in various set of combinations for the guitaritme.

there is a main form and a sub-form.
The main form's record source is the GuitarItems table.
The sub-form's record source is the ProgramCodes table.
Linked on GuitarItem (or GuitarID)

anyway, no one really does the combination sets to the guitars and the reason why the form is created for the end-user to use to enter the information and identify the sets for the various combinations.

basic query linking the tables together gives me the result as requested. the issue is that i need the result to ONLY show the set of combinations where that set of Optionitem(s) are the ONLY ones on the order, not all where they match (this is what I get now as the result).


NOTE: all the guitar programming codes (the set / combinations) must be in one field so that all of the codes are displayed as one record per order.


Hope i have provided enough information here for someone to understand what I need help with.

 

[Remou]

Please post the SQL you have at the moment, even if it is not quite what you want. A little sample data and a few notes on what you would like returned, that is, mark the sample data, would be good.

 

[vbahelp07]

Hi Remou,
ok, here goes ...

this query is the "basic" query I have:

SELECT GuitarOptionDetails.InvoiceDate, GuitarOptionDetails.InvoiceNumber, GuitarOptionDetails.GuitarItem, ProgramCodes.Code, ProgramCodes.OptionCombo
FROM GuitarOptionDetails LEFT JOIN ProgramCodes ON (GuitarOptionDetails.OptionCombo = ProgramCodes.OptionCombo) AND (GuitarOptionDetails.GuitarItem = ProgramCodes.Guitar)
GROUP BY GuitarOptionDetails.InvoiceDate, GuitarOptionDetails.InvoiceNumber, GuitarOptionDetails.GuitarItem, ProgramCodes.Code, ProgramCodes.OptionCombo
ORDER BY GuitarOptionDetails.InvoiceNumber, GuitarOptionDetails.GuitarItem;

the result being the details:

InvoiceDate	InvoiceNumber	GuitarItem	Code	ComboID
9/7/2007	2365186	AE185	185RR	4
9/7/2007	2365186	AE185	186RHT	4
9/7/2007	2365186	AE185	188RT38B	4
9/7/2007	2365203	AE185	185RR	2
9/7/2007	2365203	AE185	186RHT	2
9/7/2007	2365203	AE185	187RT38	2
9/7/2007	2365428	AE185	185RR	4
9/7/2007	2365428	AE185	186RHT	4
9/7/2007	2365428	AE185	188RT38B	4
9/11/2007	2366116	AE185	185RR	4
9/11/2007	2366116	AE185	186RHT	4
9/11/2007	2366116	AE185	188RT38B	4
9/12/2007	2366228	AE185	185RR	4
9/12/2007	2366228	AE185	186RHT	4
9/12/2007	2366228	AE185	188RT38B	4
9/13/2007	2366532	AE185	185RR	2
9/13/2007	2366532	AE185	186RHT	2
9/13/2007	2366532	AE185	187RT38	2
9/18/2007	2367519	AE185	185RR	4
9/18/2007	2367519	AE185	186RHT	4
9/18/2007	2367519	AE185	188RT38B	4
9/26/2007	2368963	AE185	185RR	2
9/26/2007	2368963	AE185	186RHT	2
9/26/2007	2368963	AE185	187RT38	2
9/28/2007	2369326	AE185	185RR	4
9/28/2007	2369326	AE185	186RHT	4
9/28/2007	2369326	AE185	188RT38B	4

then the "summary" query:

SELECT GuitarProgramCodesDetails.InvoiceDate, GuitarProgramCodesDetails.InvoiceNumber, GuitarProgramCodesDetails.GuitarItem, Concatenate("SELECT Code FROM ProgramCodes WHERE Guitar & OptionCombo =""" & [GuitarItem] & [OptionCombo] & """") AS Codes
FROM GuitarProgramCodesDetails
GROUP BY GuitarProgramCodesDetails.InvoiceDate, GuitarProgramCodesDetails.InvoiceNumber, GuitarProgramCodesDetails.GuitarItem, Concatenate("SELECT Code FROM ProgramCodes WHERE Guitar & OptionCombo =""" & [GuitarItem] & [OptionCombo] & """")
ORDER BY GuitarProgramCodesDetails.InvoiceNumber;

The result of the above is:
InvoiceDate	InvoiceNumber	GuitarItem	Codes
9/7/2007	2365176	DC135C	
9/7/2007	2365181	LB20	
9/7/2007	2365182	DC135C	
9/7/2007	2365186	AE185	185RR 186RHT 188RT38B 185RR 186RHT 188RT38B
9/7/2007	2365187	DC145C	1272R-RB 1452T-RB
9/7/2007	2365195	DC145T	1271R-RB 1451T-RB
9/7/2007	2365203	AE185	185RR 186RHT 187RT38
9/7/2007	2365251	DC127T	1251R 1271T
9/7/2007	2365422	DC127M	127MR-RB 127MT-RB 127R-RB
9/7/2007	2365428	AE185	185RR 186RHT 188RT38B 185RR 186RHT 188RT38B
9/7/2007	2365440	LB20	
9/10/2007	2365903	DC400	
9/10/2007	2365905	DC145M	127MR-RB 145MT-RB
9/11/2007	2366116	AE185	185RR 186RHT 188RT38B 185RR 186RHT 188RT38B
9/12/2007	2366228	AE185	185RR 186RHT 188RT38B 185RR 186RHT 188RT38B
9/12/2007	2366358	DC400C	
9/12/2007	2366359	DC127T	1271R-RB 1271T-RB
9/13/2007	2366402	DC400T	
9/13/2007	2366532	AE185	185RR 186RHT 187RT38
9/14/2007	2366626	DC400T	
9/15/2007	2366903	DC727C	
9/14/2007	2366905	LB70	
9/15/2007	2366909	DC400C	
9/15/2007	2366910	DC135M	
9/15/2007	2366917	AE185-12	
9/17/2007	2367373	SC90S	
9/17/2007	2367385	DC127T	1251R 1271T
9/18/2007	2367519	AE185	185RR 186RHT 188RT38B 185RR 186RHT 188RT38B
9/18/2007	2367630	DC400	
9/20/2007	2368018	LB75	
9/20/2007	2368021	LB75	
9/24/2007	2368425	LB20	
9/24/2007	2368432	DC127M	127MR-RB 127MT-RB 127R-RB
9/26/2007	2368963	AE185	185RR 186RHT 187RT38
9/28/2007	2369326	AE185	185RR 186RHT 188RT38B 185RR 186RHT 188RT38B
9/29/2007	2369646	SC90C

this is what the ProgramCodes table looks like with the combination sets defined by the guitar programmer:

ProgramCodes GuitarItem OptionItem 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 BB 185RR 4 
AE185 38 186RHT 4 
AE185 BB 186RHT 4 
AE185 38 188RT38B 4 
AE185 BB 188RT38B 4

1st set (ComboID=1) are the Codes for NO body options.
185RR 186RHT 187RT for GuitarItem=AE185 when the invoices has no body options.

2nd set (ComboID=2) are the Codes for ONLY if body option=38.
185RR 186RHT 187RT38

3rd set (ComboID=3) are the Codes for ONLY if body option=BB.
185RR 186RHT 188RT-B

4th set (ComboID=4) are the Codes for when BOTH 38 AND BB are purchased.
185RR 185RR 186RHT 186RHT 188RT38BB 188RT38BB

but should only show
185RR 186RHT 188RT38BB (it should not repeat the same codes) and it also should NOT show the 187RT38 and 188RT-B because it has codes for 38 or BB.

hope this part made sense. as when you link the tables to query on, it will show ALL Codes where there's a match. The ComboID does not come into play (as parameter).

This is the only distinguishing identifier. So somehow the query needs to say something like ONLY show the Codes when the invoice for guitar AE185 ONLY has 38 AND BB not 38 AND/OR BB, which is what it is doing now.

HTH!

 

[Remou]

What code does the Concatenate function contain? From the above, it seems that it is theat function that needs to be looked at.

 

[vbahelp07]

not sure exactly, but my understanding is that the SQL is set in the query using the Concatenate()
Codes: (Concatenate("SELECT Code FROM ProgramCodes WHERE Guitar & ComboID =""" & [GuitarItem] & [ComboID] & """")

or something to that affect.
i'm thinking, 1st, it's the result from a standard query, then use the Concatenate function.
and it needs to handle the Null options as they have a set of programming codes as actual option combinations has a set of programming codes.


Anyway, it is from Duane Hookom's concatenate() function:

Option Compare Database

Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = " ") _
        As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
'   this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
    Dim rs As New ADODB.Recordset
    rs.Open pstrSQL, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
'====== uncomment next line for DAO ========
    'Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
        Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
End Function

 

[Remou]

What is OptionCombo? I cannot see an example or a table that contains this item?

The general idea is to either provide me with enough information to try this or enough data to attract the attention of someone else. Even a few lines of data that matches the SQL makes it so much easier to see what is going on

 

[vbahelp07]

sorry, that field "OptionCombo" is not used anymore. it was another attempt on my part. i have stuck with the ComboID as the differentiator of the combination sets of the Optionitems per guitar.

yes, i know. hopefully i have provided enough info here.

i don't want to confuse the matter with info that is not really relevant, like the OptionCombo, forgot to delete that, etc ... but to show code and examples.

let me know, thanks!


don't know if this simplifies it any:
Point 1 – the Body option items are/can be shared in the various sets of programming codes AKA: ComboID (combinations of the programming codes).

Point 2 – each set has their own set of programming codes.

Point 3 – because the programming codes can be shared in the various sets, it repeats in the basic query.

Point 4 – how can I have it ONLY show when it has all the Body options for the guitaritem for what’s in JUST that set ONLY? That’s the problem

Invoice = 1
GuitarItem = 1
OptionItems = null
Codes = 1, 2, 3 (set 1 AKA: ComboID=1)

Invoice = 2
GuitarItem = 1
OptionItem = 1
Codes = 1, 2, 4 (set 2 AKA: ComboID=2)

Invoice = 3
GuitarItem = 1
OptionItem = 2
Codes = 1, 2, 5 (set 3 AKA: ComboID=3)

Invoice = 4
GuitarItem = 1
OptionItems = 1 AND 2
Codes = 1, 2, 6 (set 4 AKA: ComboID=4)

The basic query, say is showing for
Invoice = 2
GuitarItem = 1
OptionItem = 1
Codes = 1, 2, 4, AND 6 and it should NOT show 6 but it is because the OptionItem = 1 which is in both of the sets. Set 2 and Set 6

Invoice = 4
GuitarItem = 1
OptionItems = 1 AND 2
Codes = 1, 2, 4, 5, and 1, 2, 6 and it should NOT be showing 4 or 5 but because the OptionItems 1 and 2 and in all the sets 2-6.

Hope this makes sense?

And to top it off, I can’t have the Codes 1 and 2 repeat.

The result for invoice = 4 should be
Invoice = 4
GuitarItem = 1
OptionItems = 1 and 2
Codes = 1, 2, 6 (ONLY these 3 but it will show 6 codes)

See below 38 as 1 and BB as 2 in comboID = 4

InvDate InvoNo  Guitar  Option  Option  Codes   ComboID
9/7/07	2365186	AE185	38	38	188RT38B	4
9/7/07	2365186	AE185	BB	BB	185RR	4
9/7/07	2365186	AE185	38	38	186RHT	4
9/7/07	2365186	AE185	BB	BB	186RHT	4
9/7/07	2365186	AE185	BB	BB	188RT38B	4
9/7/07	2365186	AE185	38	38	185RR	4

 

[Remou]

Please, just some data Is that real data in the small table above? Or just attach a sample mdb. There is a new attachment option in Tek-Tips.

 

[vbahelp07]

Hi Remou,

The date for AE185 (guitaritem) is real data in the table.

yes, it looks like you have to sign-up and use an URL to do so though. you can't simply browse your computer and attach the db. I will do this so I can attach the db. i think i maybe confusing the situation here with various examples. i put real data and also tried to put some generic data to see if it'll simplify but not sure if that helped or made it worse.

i'll try to explain better here and attach the db as well.
so in the result you see with the invoice date 9/7/07 and invoicenumber 2365186, the customer purchased the guitar AE185 and purchased 2 body options 38 AN BB.

in the table you see above is all the programming codes for Combination 4 (ComboID=4) which is the set defined by the guitar programmer end-user that assigned the programming codes for these options for this guitar.

So this is from the ProgramCodes table which there's a UI form for the guitar programmer (end-user).

Guitar Option Codes
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 BB 185RR 4
AE185 38 186RHT 4
AE185 BB 186RHT 4
AE185 38 188RT38B 4
AE185 BB 188RT38B 4

A query is showing, for invoice 2365186 ALL the Codes where there's a 38 or BB and NOT just where it's 38 AND BB. right now the only way you know that is the ComboID 4. this set is the programming codes used for ONLY when the guitar AE185 has BOTH 38 AND BB.

anyway, please see attached. let me know if i can explain anything else.

i appreciate you continuing with this.

 

[Remou]

Is this what you want:

SQL
[tt]SELECT DISTINCT GuitarProgramCodesDetails1.InvoiceDate, GuitarProgramCodesDetails1.InvoiceNumber, Concat2([InvoiceNumber]) AS Options
FROM GuitarProgramCodesDetails1[/tt]

New Concatenate:

Function Concat2(InvNo)
Dim rs As DAO.Recordset

If Nz(InvNo, 0) = 0 Then
    Concat2 = "N/A"
    Exit Function
End If

strSQL = "Select Code From GuitarProgramCodesDetails1 Where InvoiceNumber='" _
& InvNo & "'"
Set rs = CurrentDb.OpenRecordset(strSQL)
Do While Not rs.EOF()
    If InStr(strCC, Nz(rs!code, "")) = 0 Then
        strCC = strCC & " " & Nz(rs!code, "")
    End If
    rs.MoveNext
Loop
Concat2 = strCC

End Function

 

[vbahelp07]

WOW!

almost.

the result that is displaying is showing the Codes for 38 OR BB and NOT just 38 AND BB

InvoiceDate InvoiceNumber Codes
9/7/07 2365186 187RT38 186RHT 185RR 188RT-B 188RT38B

as you see
187RT38 is ONLY if the invoice JUST had option 38 along with 185RR 186RHT
188RT-B is ONLY if the invoice JUST had option BB along with 185RR 186RHT

but since this invoice has BOTH 38 AND BB, it should be
InvoiceDate InvoiceNumber Codes
9/7/07 2365186 186RHT 185RR 188RT38B

 

[Remou]

This is quite clumsy.

Function Concat2(InvNo)
Dim rs As DAO.Recordset
Dim strCC, strOpt

If Nz(InvNo, 0) = 0 Then
    Concat2 = "N/A"
    Exit Function
End If

strSQL = "Select Code, Option From GuitarProgramCodesDetails1 Where InvoiceNumber='" _
& InvNo & "'"
Set rs = CurrentDb.OpenRecordset(strSQL)
Do While Not rs.EOF()
    If InStr(strCC, Nz(rs!code, "")) = 0 Then
        strCC = strCC & " " & Nz(rs!code, "")
        strOpt = strOpt & " " & rs!Option
    End If
    rs.MoveNext
Loop

If InStr(strOpt, "BB") > 0 And InStr(strOpt, "38") > 0 Then
    strCC = Replace(strCC, "187RT38", "")
    strCC = Replace(strCC, "188RT-B", "")
    strCC = Replace(strCC, "  ", " ")
End If
Concat2 = Trim(strCC)

End Function

I suspect you need to look at the design of some of your tables.

 

[vbahelp07]

Remou,
however this part of the code is specifically for the 2 options and 2 programming codes and therefore dismisses all other option and programming codes combinations.

If InStr(strOpt, "BB") > 0 And InStr(strOpt, "38") > 0 Then
    strCC = Replace(strCC, "187RT38", "")
    strCC = Replace(strCC, "188RT-B", "")
    strCC = Replace(strCC, "  ", " ")
End If

it needs to be able to handle the variables across the board, without specifying each variable as you have here.

if you looked at the db attachment you will see other guitar items and other options with other programming codes that have similar shared programming codes depending on the options.

I suspect you need to look at the design of some of your tables.

i am open to changing any of the tables IF it will obtain the final result required here.

the only thing I could come up with for the set differentiator was the ComboID. i can't think of any other way. you have to consider that it is only the guitar programmer (end-user) that will know the combinations and needs the interface to select the guitar and enter programming codes if there are options for the guitar or not and what the option(s) are. you will see this in the current form and sub-form set-up. how else can this be set-up so the user can select 0-many options for many programming codes which are shared BUT having to link back to each of the options on the invoice?

again, i am open to changing structure IF it can bring me to this.

thank you!

 

[vbahelp07]

I REALLY appreciate all your attempts!

so this other invoice:

InvoiceDate	InvoiceNumber	Options
9/7/07	2365203	187RT38 186RHT 185RR 188RT38B

has only the Option=38 for guitarItem AE185
so it should be:

InvoiceDate	InvoiceNumber	Options
9/7/07	2365203	187RT38 186RHT 185RR

because "188RT38B" belongs to the set for when it's BOTH 38 AND BB but i believe it's displaying because on the join / match the data pulls everything for 38 except what you have specified NOT to show.

please let me know ...

 

[vbahelp07]

Remou,
this is the only other thing i can muster-up. we need something to match back to one another with the set of the combined codes.

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 I don't think, 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.

if that's good, i need a routine to generate the field "OptionCombo" in the Invoice file and concatenate all the BODY options only as the OptionCombo field result per invoice and guitar. would need help with that since it has to be invoicenumber and guitar combine the options AS OptionCombo.

thoughts / suggestions?

 

[vbahelp07]

so basically the routine will create the combined options for just the BODY options AS OptionCombo.

the user (UI) will have to change and he will have to just enter the combined values as one set per record.