Query: returns product data twice!

[LzAnn]

Hello everyone!

Am trying to create a query to combine relevant product data. Had lots of help on the tbl structure, thank you (jzwp22)

The following fields return relevant products perfectly.
ProductID, from tblPrducts
ProductCode, from tblProducts
ProductName, from tblProductNames
ListPrice, from tblProductCompanies
Type, from tblTypes (Set criteria to 'Soil & Waste Products')

The issue is that I also need to include the descriptors, 'size' and 'angle' in the query so the user can select the correct product. Except when I include the descriptors in the query (unsurprisingly) the values returned are duplicated as all descriptors are held in one table. What I end up with are the products, but duplicated, one with the size, and one of lot with the angles. Does this mean I need to alter the tbl structure of the database to list descriptors in columns of their own?

Will attach a relationship report - any advice is greatly appreciated!

 

[MSAccessRookie]

Hello everyone!

Am trying to create a query to combine relevant product data. Had lots of help on the tbl structure, thank you (jzwp22)

The following fields return relevant products perfectly.
ProductID, from tblPrducts
ProductCode, from tblProducts
ProductName, from tblProductNames
ListPrice, from tblProductCompanies
Type, from tblTypes (Set criteria to 'Soil & Waste Products')

The issue is that I also need to include the descriptors, 'size' and 'angle' in the query so the user can select the correct product. Except when I include the descriptors in the query (unsurprisingly) the values returned are duplicated as all descriptors are held in one table. What I end up with are the products, but duplicated, one with the size, and one of lot with the angles. Does this mean I need to alter the tbl structure of the database to list descriptors in columns of their own?

Will attach a relationship report - any advice is greatly appreciated!

I am finding it is a little difficult to interpret what your issue is because of the following issues:

• You did not provide the SQL Code that you are using, which might provide better insight into the problem.
• The Table names in your example PDF Diagram are not the same as the ones in your post above.

I have made some assumptions regarding the differences in names.

tblPrducts from Above refers to the Table Products in the PDF Diagram.
tblProductNames from Above refers to the Table Product Names in the PDF Diagram.
tblProductCompanies from Above refers to the Table Product Companies in the PDF Diagram.
tblTypes from Above refers to the Table Types in the PDF Diagram.

tblPrducts can be related to tblProductNames using the Column ProductNameID, and can also be related to tblProductCompanies using the Column ProductID.
tblTypes cannot be related to any of the other tables.

If these assumptions are true, then the fact that tblTypes is not related to any other table will create a Cartesian Join type. This could be the source of your problem. Check it out and get back to us.

If you do, please include your SQL Code for evaluation.

 

[LzAnn]

Hi MSAccessRookie,

Thank you for your reply. Yes, you were right about which tables I was referring to.

I suspect the table structure of the database still isn't right in order to do what I hope. The query is to populate a combo box to list only values that the user needs to see, i.e. ProductName, ProductCode, Size, Angle, and ListPrice.

When setting up a query, I cannot imagine how it's possible to have two or more of the product details/(descriptors) listed because of the way I have the tables setup (Size, Angle, Flowrate, Manufacturer, etc) all in the same one table.

Do you think I have to alter the table structure to combat this? Hope it makes sense! Thank you very much

 

[LzAnn]

[/quote]If these assumptions are true, then the fact that tblTypes is not related to any other table will create a Cartesian Join type. This could be the source of your problem. Check it out and get back to us.

Cartesian Join type - not sure what this is, but when I set type criteria, this seems to be okay. Is this type of relationship a problem?

Table 'Types' was created so that I could categorise products and be able to refer to them on the interface, using combo boxes. Also for product searches. So, for this part, selecting all the 'Soil & Waste' categorised products is great - just what I need.

Here's the SQL for the query:
SELECT Products.ProductID, Products.ProductCode, [Product Names].ProductName, ProductCompanies.ListPrice, Types.Type
FROM Types, ([Product Names] INNER JOIN Products ON [Product Names].ProductNameID = Products.ProductNameID) INNER JOIN ProductCompanies ON Products.ProductID = ProductCompanies.ProductID
WHERE (((Types.Type)='Soil & Waste Systems'));

This works great. The problem is when I try and incorporate descriptors into the query; it's bizarre!

Thank you

 

[boblarson]

It isn't bizzare, you don't have a join going to your tblTypes which you need.

 

[MSAccessRookie]

It isn't bizzare, you don't have a join going to your tblTypes which you need.

Bob,

Since the OP cannot do this with the present structure, an alternative method will be required before the Table type is able to be used. I was unsure of the best method to approach the issue since there is nothing in common to join on.

 

[LzAnn]

Re: Query

Thank you both. I should have investigated/thought about it more before posting a thread, sorry.