duplicates in product name

[misschanda via AccessMonster.com]

Hello,
From the following SQL I am getting a return of 1600 records when there are
only ~200 records, in other words lots of duplicates. I have tried to use
distinct and that reduces it to ~1450. Is there another way to reduce.

Thanks
LA

SELECT DISTINCT AllAnalysis.[Product Name], [Product Formulation].[Product
Launch Date/ Recieve Date], [Product Formulation].ModificationVersions,
[Product Formulation].[Size(mm)], [Product Formulation].[Resins(commonname)],
[Product Formulation].[Polymer PartNumber], [Product Formulation].[Additives
Part Number 2], [Product Formulation].[Additives Part Number 3], [Product
Formulation].STabaxolType, [Product Formulation].[1stRoll (rpm)], [Product
Formulation].DrawRatio1, [Product Formulation].DrawRatio2, [Product
Formulation].Drawratio3, [Product Formulation].DrawTemperature1, [Product
Formulation].DrawTemperature2, AllAnalysis.[FreeShrinkage(%)], AllAnalysis.
[Ten at 5% Elg (cN/tex)], AllAnalysis.[Break Energy (in-lbf)], AllAnalysis.
[StrainAt175gpd(%)], AllAnalysis.AverageBreakCycles, AllAnalysis.
[AbrasionToBreakLoad(g)]
FROM AllAnalysis INNER JOIN [Product Formulation] ON AllAnalysis.[Product
Name] = [Product Formulation].[Product Name]
GROUP BY AllAnalysis.[Product Name], [Product Formulation].[Product Launch
Date/ Recieve Date], [Product Formulation].ModificationVersions, [Product
Formulation].[Size(mm)], [Product Formulation].[Resins(commonname)], [Product
Formulation].[Polymer PartNumber], [Product Formulation].[Additives Part
Number 2], [Product Formulation].[Additives Part Number 3], [Product
Formulation].STabaxolType, [Product Formulation].[1stRoll (rpm)], [Product
Formulation].DrawRatio1, [Product Formulation].DrawRatio2, [Product
Formulation].Drawratio3, [Product Formulation].DrawTemperature1, [Product
Formulation].DrawTemperature2, AllAnalysis.[FreeShrinkage(%)], AllAnalysis.
[Ten at 5% Elg (cN/tex)], AllAnalysis.[Break Energy (in-lbf)], AllAnalysis.
[StrainAt175gpd(%)], AllAnalysis.AverageBreakCycles, AllAnalysis.
[AbrasionToBreakLoad(g)]
ORDER BY AllAnalysis.[Product Name];

 

[Jerry Whittle]

200 records in which table? You could have only 200 in one table but multiple
matching records in the other.

Is AllAnalysis.[Product Name] a primary key field? How about
[Product Formulation].[Product Name] ?

If neither of the two is a primary key, you could have a mini-Cartesian
Product.

Having both the DISTINCT clause and the GROUP BY without an aggregate
function returned is a little overkill. I'd dump the GROUP BY.

 

[misschanda via AccessMonster.com]

Jerry,
There are approx. 200 records in one table and 400 records in the other table.
There common link is Product Name. Product Name is neither a primary key in
either table.

What exactly is a Mini Cartesian??
I got rid of the group by and left DISTINCT== not a difference. Any more
suggestions..
Thanks

LA

200 records in which table? You could have only 200 in one table but multiple
matching records in the other.

Is AllAnalysis.[Product Name] a primary key field? How about
[Product Formulation].[Product Name] ?

If neither of the two is a primary key, you could have a mini-Cartesian
Product.

Having both the DISTINCT clause and the GROUP BY without an aggregate
function returned is a little overkill. I'd dump the GROUP BY.

Hello,
From the following SQL I am getting a return of 1600 records when there are

[quoted text clipped - 31 lines]

[AbrasionToBreakLoad(g)]
ORDER BY AllAnalysis.[Product Name];

 

[Douglas J. Steele]

If you have, say, 4 rows in Table A with a particular Product Name and 3
rows in Table B with the same Product Name, the result will be 12 rows (each
row in Table A matched with each row in Table B)

I don't understand why Product Name wouldn't be unique in Product
Formulation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Jerry,
There are approx. 200 records in one table and 400 records in the other
table.
There common link is Product Name. Product Name is neither a primary key
in
either table.

What exactly is a Mini Cartesian??
I got rid of the group by and left DISTINCT== not a difference. Any more
suggestions..
Thanks

LA

200 records in which table? You could have only 200 in one table but
multiple
matching records in the other.

Is AllAnalysis.[Product Name] a primary key field? How about
[Product Formulation].[Product Name] ?

If neither of the two is a primary key, you could have a mini-Cartesian
Product.

Having both the DISTINCT clause and the GROUP BY without an aggregate
function returned is a little overkill. I'd dump the GROUP BY.

Hello,
From the following SQL I am getting a return of 1600 records when there
are

[quoted text clipped - 31 lines]

[AbrasionToBreakLoad(g)]
ORDER BY AllAnalysis.[Product Name];

 

[misschanda via AccessMonster.com]

Product name isn't unique in product formulation because the table spans for
years and new addition to old product required new entry of same product name.

If you have, say, 4 rows in Table A with a particular Product Name and 3
rows in Table B with the same Product Name, the result will be 12 rows (each
row in Table A matched with each row in Table B)

I don't understand why Product Name wouldn't be unique in Product
Formulation.

Jerry,
There are approx. 200 records in one table and 400 records in the other

[quoted text clipped - 28 lines]

[AbrasionToBreakLoad(g)]
ORDER BY AllAnalysis.[Product Name];

 

[Douglas J. Steele]

Do you only want the latest formulation for each product?

If so, you can write a query to do that, and use that query in your other
query instead of the table. How you'd write that query depends on how the
table's structured, but assuming that there's a FormulationDate field, you
can use something like:

SELECT [Product Formulation].[Product Name], [Product Formulation].Field1,
[Product Formulation].Field2, ...
FROM [Product Formulation] INNER JOIN
(
SELECT [Product Name], Max([FormulationDate] AS LatestDate
FROM [Product Formulation]
GROUP BY [Product Name]
) AS Subselect
ON [Product Formulation].[Product Name] = Subselect.[Product Name]
AND [Product Formulation].FormulationDate = Subselecte.LatestDate

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Product name isn't unique in product formulation because the table spans
for
years and new addition to old product required new entry of same product
name.

If you have, say, 4 rows in Table A with a particular Product Name and 3
rows in Table B with the same Product Name, the result will be 12 rows
(each
row in Table A matched with each row in Table B)

I don't understand why Product Name wouldn't be unique in Product
Formulation.

Jerry,
There are approx. 200 records in one table and 400 records in the other

[quoted text clipped - 28 lines]

[AbrasionToBreakLoad(g)]
ORDER BY AllAnalysis.[Product Name];