Brian,
I'm going to "answer" this question, so we can work this through in
the RFC's. You can make up the extra (whatever amount seems fair to
you) in the tip or by posting another question (where you will accept
anything as an answer) to my attention.
Here's how we're probably going to solve this:
We'll write a VBA routine that opens a recordset with the Products,
Attributes and Values in sorted order. We will run back and forth
through that recordset, assembling the information into a special
table. This table is likely to have only one field, into which we will
concatenate the values. Once this is done, you will be able to produce
a query on one field which will give you a list of all possible
combinations.
If that sounds good to you, we'll start working through it. Let me
know.
Warning: If you have products with lots of attributes and values, this
can get very hefty. Remember, a product with 3 attributes, each of
which has 10 values, produces 1000 combinations!
- Hammer |
Clarification of Answer by
hammer-ga
on
17 Feb 2003 09:00 PST
Brian? Are you still with me on this one?
- Hammer
|
Request for Answer Clarification by
bselltiz-ga
on
18 Feb 2003 09:10 PST
Hey Hammer, I'm still here - just had some craziness to deal with
because of the snow. I will go ahead and rate this answer and then we
will work out the details as we go. Also, you mentioned RFCs, I am not
familiar with what you meant.
You're solution sounds fine although I am unfamiliar with how you will
produce a query's recordset from a concatenated field value, but
whatever works is fine. Let me know how to proceed.
Thanks :->
Brian
|
Request for Answer Clarification by
bselltiz-ga
on
18 Feb 2003 09:12 PST
also, I just realized it will be better for me to wait and rate this
so I can include any extra reimbursement via the tip.
|
Request for Answer Clarification by
bselltiz-ga
on
18 Feb 2003 09:34 PST
RFCs, requests for clarification.... sorry, it was a long night
|
Clarification of Answer by
hammer-ga
on
18 Feb 2003 09:35 PST
RFC = Request For Clarification
OK, first you need to make a decision. As we discussed, you have some
oddness in your structure. Currently, the first table, with the
Product and Attribute, is redundant because you have the same info in
the second table. Do you want to:
1. Stick with your current structure. (Not recommended)
2. Ditch Table 1 and just work with Table 2
3. Redo your tables as specified in our discussion in the comments
Let me know which you want. Also, can you post your mdb where I can
get it, just so I can follow along a bit less blindly. <G>
- Hammer
|
Request for Answer Clarification by
bselltiz-ga
on
18 Feb 2003 11:12 PST
I need to stick with the current structure.
I have e-mailed you the database so you can take a look at it.
The tables in use are:
tblPRODATT
tblPROGPRODATTVALUES
If outlook blocks access to the attachment let me know and I will put
it on our website for download.
|
Clarification of Answer by
hammer-ga
on
18 Feb 2003 11:25 PST
I am getting an error trying to open the database. Could you put it on
the website? Thanks.
- Hammer
|
Request for Answer Clarification by
bselltiz-ga
on
18 Feb 2003 21:16 PST
here's the link
www.digitalprovisions.com/bjm/bjmwhmaintables.mdb
also, it's a Access 2002 database, just in case you were trying to
open it in Access 2000.
|
Clarification of Answer by
hammer-ga
on
19 Feb 2003 04:30 PST
OK, I'll set up an Office 2002 box.
- Hammer
|
Clarification of Answer by
hammer-ga
on
20 Feb 2003 08:37 PST
OK, Brian, here it is!
First, you need to build a small table called tblMATRIX. It has two
fields:
Name Type
-------------------
Product Text
Attributes Memo
Next, cut and paste the code below into a new module. Deal with the
line wraps, etc. When you run the first routine (BuildAllMatrix), it
will clear out tblMATRIX and repopulate it. Then, you can run queries
and reports against it. I've done the best I could to figure out how
to identify the products. The SQL embedded in the code should make it
fairly obvious what I've done. I'll be out of town for a few days, but
I'll respond to any questions when I get back.
Good luck!
- Hammer
' ****************** CODE BEGIN *********************
Option Compare Database
Option Explicit
Public Sub BuildAllMatrix()
Dim cnn As ADODB.Connection
Dim rstProducts As ADODB.Recordset
Dim strSQL As String
Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
Set rstProducts = New ADODB.Recordset
cnn.Execute ("DELETE * FROM tblMATRIX;")
strSQL = "SELECT DISTINCT tblPRODATT.CATALOGID FROM tblPRODATT;"
rstProducts.Open strSQL, cnn, adOpenStatic, adLockOptimistic
If Not rstProducts.BOF And Not rstProducts.EOF Then
rstProducts.MoveFirst
Do Until rstProducts.EOF
BuildAttributeMatrix rstProducts!CatalogID
rstProducts.MoveNext
Loop
End If
End Sub
Public Function BuildAttributeMatrix(strCatalogID As String) As
Boolean
Dim cnn As ADODB.Connection
Dim rstAttributes As ADODB.Recordset
Dim strSQL As String
Dim strVal() As String
Dim strInsert As String
Dim intAttributeCount As Integer
Dim intLoop As Integer
Dim blnReturn As Boolean
' Set this flag to False to indicate an error condition.
blnReturn = True
Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
Set rstAttributes = New ADODB.Recordset
' Get the list of attributes for this product
strSQL = "SELECT DISTINCT tblPRODATT.ATTRIBUTEORDER,
tblPROGPRODATTVALUES.ATTRIBUTE " & _
"FROM tblPROGPRODATTVALUES " & _
"INNER JOIN tblPRODATT ON (tblPROGPRODATTVALUES.PROGNAME =
tblPRODATT.PROGNAME) AND (tblPROGPRODATTVALUES.CATALOGID =
tblPRODATT.CATALOGID) AND (tblPROGPRODATTVALUES.ATTRIBUTE =
tblPRODATT.ATTRIBUTE) " & _
"WHERE tblPROGPRODATTVALUES.CATALOGID = '" & strCatalogID & "'
" & _
"ORDER BY tblPRODATT.ATTRIBUTEORDER;"
intAttributeCount = 0
rstAttributes.Open strSQL, cnn, adOpenStatic, adLockOptimistic
If Not rstAttributes.BOF And Not rstAttributes.EOF Then
With rstAttributes
' Ensure a correct RecordCount
.MoveLast
.MoveFirst
' Determine how many value recordsets we need
intAttributeCount = .RecordCount
' Create an array of recordsets to hold the values for
each attribute
Dim rstValues() As ADODB.Recordset
ReDim rstValues(intAttributeCount)
For intLoop = 1 To intAttributeCount
Set rstValues(intLoop) = New ADODB.Recordset
Next intLoop
intLoop = 1
' Populate each recordset in the array with the values for
one attribute
Do Until .EOF
strSQL = "SELECT tblPROGPRODATTVALUES.ATTRIBUTE,
tblPROGPRODATTVALUES.VALUE " & _
"FROM tblPROGPRODATTVALUES " & _
"WHERE ((tblPROGPRODATTVALUES.CATALOGID = '" &
strCatalogID & "') And (tblPROGPRODATTVALUES.ATTRIBUTE = '" &
!Attribute & "')) " & _
"ORDER BY tblPROGPRODATTVALUES.VALUE;"
rstValues(intLoop).Open strSQL, cnn, adOpenStatic,
adLockOptimistic
intLoop = intLoop + 1
.MoveNext
Loop
End With
End If
' Reset all the value recordsets
For intLoop = 1 To intAttributeCount
rstValues(intLoop).MoveFirst
Next intLoop
' Initialize Value array
ReDim strVal(intAttributeCount)
For intLoop = 1 To intAttributeCount
strVal(intLoop) = ""
Next intLoop
' Loop through the lists of values, assembling matrix entries
Do Until rstValues(1).EOF
For intLoop = 1 To intAttributeCount
strVal(intLoop) = rstValues(intLoop)!Attribute + " " +
rstValues(intLoop)!Value
Next intLoop
strInsert = Join(strVal, " - ")
cnn.Execute "INSERT INTO tblMATRIX VALUES('" & strCatalogID &
"','" & strInsert & "');"
rstValues(intAttributeCount).MoveNext
For intLoop = intAttributeCount To 2 Step -1
If rstValues(intLoop).EOF Then
rstValues(intLoop).MoveFirst
rstValues(intLoop - 1).MoveNext
End If
Next intLoop
Loop
'Clean Up
For intLoop = 1 To intAttributeCount
strVal(intLoop) = ""
Next intLoop
For intLoop = 1 To intAttributeCount
rstValues(intLoop).Close
Set rstValues(intLoop) = Nothing
Next intLoop
rstAttributes.Close
Set rstAttributes = Nothing
Set cnn = Nothing
BuildAttributeMatrix = blnReturn
End Function
' ************** CODE END ******************
|
Clarification of Answer by
hammer-ga
on
23 Feb 2003 08:18 PST
Brian,
I forgot to put the clean up in the first small routine. Here's a replacement.
Public Sub BuildAllMatrix()
Dim cnn As ADODB.Connection
Dim rstProducts As ADODB.Recordset
Dim strSQL As String
Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
Set rstProducts = New ADODB.Recordset
cnn.Execute ("DELETE * FROM tblMATRIX;")
strSQL = "SELECT DISTINCT tblPRODATT.CATALOGID FROM tblPRODATT;"
rstProducts.Open strSQL, cnn, adOpenStatic, adLockOptimistic
If Not rstProducts.BOF And Not rstProducts.EOF Then
rstProducts.MoveFirst
Do Until rstProducts.EOF
BuildAttributeMatrix rstProducts!CatalogID
rstProducts.MoveNext
Loop
End If
' Clean up
rstProducts.Close
Set rstProducts = Nothing
Set cnn = Nothing
strSQL = ""
End Sub
|
