Product Matrix Routine

[benkingery]

We often need to create product records in our item database and it gets very time consuming. I want to create a product matrix that will help me automatically build the product listings. I want to be able to enter this information into a 3 column table:

Style
-----
M1122


Colors
------
Black
Brown
Green


Sizes
-----
5.5
6
6.5
7
7.5
8
8.5
9
10


And then, get inserts into my product table for all possible products under the matrix. In this instance, it would be:

M1122-Black-5.5
M1122-Black-6
M1122-Black-6.5
M1122-Black-7
M1122-Black-7.5
M1122-Black-8
M1122-Black-8.5
M1122-Black-9
M1122-Black-10
M1122-Brown-5.5
M1122-Brown-6
Etc, etc.

I just need help with some logic on how to proceed with making the procedure loops through each possibility and appending those values to the product table.

Anyone have any ideas?

 

[benkingery]

I actually created 3 tables that are each 1 column. First table is for Style, second is for Size, third is for Sizes. Then if I bring each table into an access query and define no relationship between them at all, then by nature Access runs a cartesian product result for this result and I get the following:

Style Color Size
M1122 Black 055
M1122 Brown 055
M1122 Green 055
M1122 Black 060
M1122 Brown 060
M1122 Green 060
M1122 Black 065
M1122 Brown 065
M1122 Green 065
M1122 Black 070
M1122 Brown 070
M1122 Green 070
M1122 Black 075
M1122 Brown 075
M1122 Green 075
M1122 Black 080
M1122 Brown 080
M1122 Green 080
M1122 Black 085
M1122 Brown 085
M1122 Green 085
M1122 Black 090
M1122 Brown 090
M1122 Green 090
M1122 Black 100
M1122 Brown 100
M1122 Green 100
Black 055
Brown 055
Green 055
Black 060
Brown 060
Green 060
Black 065
Brown 065
Green 065
Black 070
Brown 070
Green 070
Black 075
Brown 075
Green 075
Black 080
Brown 080
Green 080
Black 085
Brown 085

ETC, etc, etc

The first 27 results are EXACTLY what I want, but I don't want the rest, does anyone know how to eliminate that from the query? I can't just choose the top XX records because the results will change with each situation I input into the individual tables.

Any ideas?

 

[JPearson]

Not sure about how to limit that but you can create a matrix based off the tables to generate the data you are needing.
　

Dim m1(1), m2(3), m3(9), r1(27) as string or () if variable to set later
dim i1, i2, i3, rn as integer
　
'set the m1, m2, m3 values. can be set from table
　
rn = 1
　
for I1= lbound(m1) to ubound(m1)
        for i2= lbound(m2) to ubound(m2)
                  for i3 = lbound(m3) to ubound(m3)
                       r1(rn)= m1(i1) & m2(i2) & m3(i3)
                       rn = rn+1
                   next
          next
next
　
rn =1
for rn= lbound(r1) to ubound (r1)
           Whereever you want data to end up
next

This is how to use a matrix to make the data. This is similar to something I did in excel. You might be able to cycle through the records if you created them in a table by opening each recordset and loading into a the matrix. I am unsure how to do this directly from the table without a bit more research but it should be possible to use a similar structure.

 

[jdraw]

We often need to create product records in our item database and it gets very time consuming. I want to create a product matrix that will help me automatically build the product listings. I want to be able to enter this information into a 3 column table:

Style
-----
M1122


Colors
------
Black
Brown
Green


Sizes
-----
5.5
6
6.5
7
7.5
8
8.5
9
10


And then, get inserts into my product table for all possible products under the matrix. In this instance, it would be:

M1122-Black-5.5
M1122-Black-6
M1122-Black-6.5
M1122-Black-7
M1122-Black-7.5
M1122-Black-8
M1122-Black-8.5
M1122-Black-9
M1122-Black-10
M1122-Brown-5.5
M1122-Brown-6
Etc, etc.

I just need help with some logic on how to proceed with making the procedure loops through each possibility and appending those values to the product table.

Anyone have any ideas?

Create three tables instead. Then produce a set of values for all combos (known as a Cartesian product).

Select style & "-" & color & "-" & size into MyMatrix
FROM styles,colors,sizes


where the tables and (fields) are
Styles (style)
Colors (color)
Sizes (size)

Edit: I saw that you had another post using Cartesian product. I don't know where the unwanted combinations are coming from. I ran this set up shown above, and have no extra records.

 

[benkingery]

jdraw, yes this is exactly the way to do it. I tried again and realized that I had a whole bunch of null values in my table that were causing some unwanted results. With the exact values where they need to be, it works perfectly.

Then I just call the SQL statement from a form and I'm off to the races. Its working perfectly.