Query returns the # of input records squared

[wbohlin]

Just when I though I had everything perfect... I have one query on one table that returns the number of input records squared.. i.e. if I have 20 records in the table I'm querying, the query returns 400 records. While I'm sure this must be an easy fix, I can't seem to find the problem.

 

[pbaldy]

What's the SQL of the query? It sound like there's no join, which will produce a Cartesian product.

 

[wbohlin]

SELECT [Fisher 1-11 to 12-11 Line Items].[Part Number], [Fisher 1-11 to 12-11 Line Items].[Manufacturer Part Number], [Fisher 1-11 to 12-11 Line Items].[Manufacturer Name], [Fisher 1-11 to 12-11 Line Items].[Product Description], [Fisher 1-11 to 12-11 Line Items].[Unit of Measure - Standard], [Fisher 1-11 to 12-11 Line Items].[Total Sales], [Fisher 1-11 to 12-11 Line Items].[Taiwan 2011 Quantity Ordered by unit of measure], [Fisher 1-11 to 12-11 Line Items].[Taiwan 2011 Price per unit of measure], [Fisher 1-11 to 12-11 Line Items_1].[Hayward 2011 Quanity Ordered by unit of measure], [Fisher 1-11 to 12-11 Line Items].[Hayward 2011 Price per unit of measure], [Fisher 1-11 to 12-11 Line Items].[Philadelphia 2011 Quantity Ordered by unit of measure], [Fisher 1-11 to 12-11 Line Items].[Philadelphia 2011 Price per unit of measure]
FROM [Fisher 1-11 to 12-11 Line Items], [Total Sales], [Fisher 1-11 to 12-11 Line Items] AS [Fisher 1-11 to 12-11 Line Items_1], [Total Sales] AS [Total Sales_1]
GROUP BY [Fisher 1-11 to 12-11 Line Items].[Part Number], [Fisher 1-11 to 12-11 Line Items].[Manufacturer Part Number], [Fisher 1-11 to 12-11 Line Items].[Manufacturer Name], [Fisher 1-11 to 12-11 Line Items].[Product Description], [Fisher 1-11 to 12-11 Line Items].[Unit of Measure - Standard], [Fisher 1-11 to 12-11 Line Items].[Total Sales], [Fisher 1-11 to 12-11 Line Items].[Taiwan 2011 Quantity Ordered by unit of measure], [Fisher 1-11 to 12-11 Line Items].[Taiwan 2011 Price per unit of measure], [Fisher 1-11 to 12-11 Line Items_1].[Hayward 2011 Quantity Ordered by unit of measure], [Fisher 1-11 to 12-11 Line Items].[Hayward 2011 Price per unit of measure], [Fisher 1-11 to 12-11 Line Items].[Philadelphia 2011 Quantity Ordered by unit of measure], [Fisher 1-11 to 12-11 Line Items].[Philadelphia 2011 Price per unit of measure];

 

[pbaldy]

Like I said, no join equals a Cartesian product. You need to join the table on the appropriate field(s). In design view that would be lines between the tables.

 

[wbohlin]

But only one table is being used..

 

[wbohlin]

I think I have it.. I cloned another report and eliminated some fields, but I think Access has a bug.. it remembers things that have been deleted. I just recreated the query from scratch and it works.

I do appreciate the help!

 

[pbaldy]

Glad you got it sorted out. I count two tables, plus each was added again with an alias, which is in effect 4 tables:

FROM [Fisher 1-11 to 12-11 Line Items], [Total Sales], [Fisher 1-11 to 12-11 Line Items] AS [Fisher 1-11 to 12-11 Line Items_1], [Total Sales] AS [Total Sales_1]