View Question
 Question
 Subject: Access Programming Question Category: Computers > Programming Asked by: bselltiz-ga List Price: \$15.00 Posted: 14 Feb 2003 07:43 PST Expires: 16 Mar 2003 07:43 PST Question ID: 161331
 ```I need an easy way to get all possible combinations for a varying number of values. I have two tables Table 1 FIELD1 NAME = PRODUCT EXAMPLE VALUES - SHIRT PANTS AND SO ON... FIELD2 NAME = ATTRIBUTE EXAMPLE VALUES - SIZE COLOR WEIGHT AND SO ON... Table 2 FIELD1 NAME = PRODUCT (FORIEGN KEY TO TABLE1) FIELD2 NAME = ATTRIBUTE (FORIEGN KEY TO TABLE1) FIELD3 NAME = VALUE (ACTUAL ATTRIBUTE VALUE) EXAMPLE VALUES - RED BLACK GREEN EXAMPLE RECORDS - SHIRT/SIZE/SMALL - SHIRT/SIZE/MEDIUM SHIRT/SIZE/LARGE SHIRT/COLOR/RED SHIRT/COLOR/BLACK SHIRT/COLOR/GREEN SHIRT/WEIGHT/20 SHIRT/WEIGHT/40 SHIRT/WEIGHT/60 AND SO ON... Either using code or a query how do I get all possible combinations of the attribute values for each product? Products can have an unlimited number of attributes and attributes can have an unlimited number of values. AN EXAMPLE OF THE RECORDS I WOULD NEED WOULD BE SHIRT - SIZE SMALL - COLOR RED - WEIGHT 20 SHIRT - SIZE SMALL - COLOR BLACK - WEIGHT 20 SHIRT - SIZE MEDIUM - COLOR GREEN - WEIGHT 60 PANT - SIZE MEDIUM - COLOR RED - WEIGHT 40 AND SO ON UNTIL I HAD ALL POSSIBLE COMBINATIONS. Windows XP Pro Access 2002```
 Subject: Re: Access Programming Question Answered By: hammer-ga on 15 Feb 2003 16:11 PST Rated:
 ```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. - 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```
 bselltiz-ga rated this answer: and gave an additional tip of: \$50.00 ```Excellent Stuff! As always Hammer provided me with exactly what I needed and then some. This question got a bit complicated but Hammer handled it no problem and came up with the solution that I needed to get my project done. I couldn't have done it without her! Thanks Hammer! :->```

 Subject: Re: Access Programming Question From: hammer-ga on 14 Feb 2003 08:56 PST
 ```Answerguru, I believe customer is looking for a cross-product of his information, not a sorted list. In other words, not: Shirt Color Black Shirt Color Green Shirt Color Red Shirt Size Large Shirt Size Medium Shirt Size Small but: Shirt Black Large Shirt Black Medium Shirt Black Small Shirt Green Large Shirt Green Medium Shirt Green Small Shirt Red Large Shirt Red Medium Shirt Red Small Is this correct, Brian? - Hammer```
 Subject: Re: Access Programming Question From: bselltiz-ga on 14 Feb 2003 13:36 PST
 ```Yes you've got it Erika, the only thing is that there may be more than two attributes making the results look like the following: Shirt Black Large 10lbs Shirt Black Medium 5 lbs Shirt Black Small 3 lbs Shirt Green Large 8 lbs Shirt Green Medium 8 lbs Shirt Green Small 5 lbs and all possible combinations of those 3 or more attributes. I know it's confusing, sorry :->```
 Subject: Re: Access Programming Question From: hammer-ga on 14 Feb 2003 15:46 PST
 ```Brian, This is not going to happen, as a query, without a massive amount of code. It can be produced as text, such as writing it out to a text file. It can also be slotted into an Excel spreadsheet. The problem with doing it as an Access query or table is that you don't know beforehand how many columns you're going to end up with. Databases HATE that! In addition, your structure is a bit odd. You have multiple foriegn keys pointing into the same table. I'd expect to see something more like this: Table1 ------- ID (Autonumber) Product Attribute Table2 ------- ID (Autonumber) ProductAttributePairID (Foreign Key to Table1.ID) Value Do you see what I mean? At any rate, this kind of matrixing is quite difficult. You're not going to get something that you can just base an Access report on without moving a few mountains. Is there some other form of output that would work for you? - Hammer```
 Subject: Re: Access Programming Question From: bselltiz-ga on 14 Feb 2003 20:34 PST
 ```Hey Erika, I was kind of aware of the problem with not knowing how many fields I was going to wind up with, that was one of my major roadblocks :-> I had toyed around with a crosstab query in the hopes of having that determine the field count, or something like that, but I couldn't get it to work the way I wanted. I do need to get this done since it will greatly simplify one of my apps processes. I don't necc need the code written for me as much as I need an example written that I can build on. Let me know via e-mail if we can work something out for me to reimburse you for what I need done. Thanks again, Brian```
 Subject: Re: Access Programming Question From: hammer-ga on 15 Feb 2003 06:30 PST
 ```I am not permitted to work with you on this outside of the Google Answers environment, however, I'll be glad to work through it with you here. There will be quite a bit of clarification involved here, to determine what the end result has to be and how best to get you there. Before you post, decide what your final result needs to be. Text file? Spreadsheet? Access report/query? Etc... Warning: The Access report/query is going to be the most difficult route, since you'll actually have to dynamically build the report/query in code. - Hammer```
 Subject: Re: Access Programming Question From: bselltiz-ga on 15 Feb 2003 11:45 PST
 ```No problem. working through here will be fine. I need the results to be in a query....figures right. So I need to be pointed in the right direction for the code. I am usually pretty good at figuring out problems like this so if you head me in the right direction I can begin to work on it and then tap your knowledge when I get stuck. Let me know how much to get me started. I will also need to know how to get payment to you since another researcher has already claimed to have answered this question. Thanks again for all your time, Brian```