double cartesian product??

[k2tile]

Hi,

First post to this forum so take it easy on me! I'm also a first time Access user.

What I'm trying to do is generate a list of product and color combination. Think T-shirts in small, medium and large. Actually, my product is tile, which is glazed according to different glaze codes. Our product numbers consist of the glaze code suffixed by the part number. (ie. RED-SQUARE).

That was really easy. I just created a simple query with an expression that combines the fields with ampersands. Here's my query:

SELECT [GL] & "-" & [PN] AS ProductNumber, Description & " in " & Name AS ["Description"]
FROM GLAZE, PART;

Now here's what has me stuck: We have several series of glazes (dark glazes, bright glazes, smooth, rough, etc). Each one multiplies the price of the product by a different amount, but within that series the customer can choose whatever glaze they want without impacting the price. Our website requires that the series be tacked onto the end of the product code.

So if we have two glaze categories each with two glazes, and two products, there should only be 8 total product codes. When I try to incorporate the series from the glaze table into my query, I end up getting 4(glazes)x2(products)x2(glaze series)=16 product codes.

How do I remove the invalid combination where the glaze code at the start of the product code doesn't belong to the glaze series at the end of the product code?

Thanks, and I hope I was able to explain my situation clearly.

 

[k2tile]

I forgot to mention that the field Series exists in both the Glazes table and the Products table. They're totally unrelated.

Not sure if this will affect my final query but I thought I should mention it.

Thanks again

 

[MSAccessRookie]

Hi,

First post to this forum so take it easy on me! I'm also a first time Access user.

What I'm trying to do is generate a list of product and color combination. Think T-shirts in small, medium and large. Actually, my product is tile, which is glazed according to different glaze codes. Our product numbers consist of the glaze code suffixed by the part number. (ie. RED-SQUARE).

That was really easy. I just created a simple query with an expression that combines the fields with ampersands. Here's my query:

SELECT [GL] & "-" & [PN] AS ProductNumber, Description & " in " & Name AS ["Description"]
FROM GLAZE, PART;

Now here's what has me stuck: We have several series of glazes (dark glazes, bright glazes, smooth, rough, etc). Each one multiplies the price of the product by a different amount, but within that series the customer can choose whatever glaze they want without impacting the price. Our website requires that the series be tacked onto the end of the product code.

So if we have two glaze categories each with two glazes, and two products, there should only be 8 total product codes. When I try to incorporate the series from the glaze table into my query, I end up getting 4(glazes)x2(products)x2(glaze series)=16 product codes.

How do I remove the invalid combination where the glaze code at the start of the product code doesn't belong to the glaze series at the end of the product code?

Thanks, and I hope I was able to explain my situation clearly.

You are correct when you say that as written your query returns a Cartesian Product. This is because you have provided no connection between the Tables. Since you have also not provided the layout for your Tables, I can only give you a general explanation of how to Join Tables to get the information that you are looking for.

The standard method of selecting from two Tables is to Join them together on a particular Field that they have in common:

SELECT [GL] & "-" & [PN] AS ProductNumber, Description & " in " & Name AS ["Description"]
FROM GLAZE INNER JOIN PART ON GLAZE.JoinColumn=PART.JoinColumn

An Alternative to this would be the following:

SELECT [GL] & "-" & [PN] AS ProductNumber, Description & " in " & Name AS ["Description"]
FROM GLAZE, PART
WHERE GLAZE.JoinColumn=PART.JoinColumn

The First method is the Access Standard Method. If you have any questions, post back.

 

[k2tile]

Thanks for the advice. I actually ended up revising the structure to make the table more normalized.

Now have another problem. I've further separated the glazes from the products(renamed to "form") by use of some many-many joins. Now I'm getting an "ambiguous outer join, please write your own query" error when I try to run the attached query. Take a look at it.

Any idea what I'm doing wrong?