Duplicate Returns

[misschanda via AccessMonster.com]

Hello All,
I need help. I made a query using two tables: prodform(which has 526 records)
and analysis(which has 480 records). The tables are joined by the fieldname
Product Name.
When I ran the table I noticed duplicate entries, not to mention a return of
1179 records.

Is there a way to sort/filter the query as to not return duplicates.

Thanks
LA

 

[misschanda via AccessMonster.com]

Hello All,
I need help. I made a query using two tables: prodform(which has 526 records)
and analysis(which has 480 records). The tables are joined by the fieldname
Product Name.
When I ran the table I noticed duplicate entries, not to mention a return of
1179 records.

Is there a way to sort/filter the query as to not return duplicates.

Thanks
LA

In addition I would like to be able to filter using two fieldnames. In other
words, name and size in all analysis is the criteria in determing if record
is a duplicate.
Thx,
LA

 

[Michel Walsh]

There are probably duplicated productName values in both tables:

TableA.ProductName
a
a
b



and

TableB.ProducName
a
b
b


then

SELECT TableA.ProductName, TableB.ProducName
FROM TableA INNER JOIN tableB ON TableA.ProductName=TableB.ProducName


would produce 4 rows, in the result.

Which of the records, from each of the table, have to be kept?


You can use a GROUP BY clause to 'aggregate' some fields, like:

SELECT ProductName, SUM(amountOfSale)
FROM TableA
GROUP BY ProductName


and then, you won't have duplicated values (in the groups).


You can also make the GROUPs after the join, instead of making them
individually on each table, but I doubt the resulting aggregate would be
meaningful.




Vanderghast, Access MVP

 

[Michel Walsh]

A GROUP can be made of more than one field:

SELECT productName, size, SomeAGGREGATE(someOtherField)
FROM yourTable
GROUP BY productName, size


Note that in the SELECT clause, each field you use there has to be either in
the GROUP BY clause, either Aggregated (SUM, MAX, MIN, COUNT, FIRST, LAST,
.... )

Hoping it may help,
Vanderghast, Access MVP

 

[misschanda via AccessMonster.com]

when i did as indicated the results returned 2 columns not four.

There are probably duplicated productName values in both tables:

TableA.ProductName
a
a
b

and

TableB.ProducName
a
b
b

then

SELECT TableA.ProductName, TableB.ProducName
FROM TableA INNER JOIN tableB ON TableA.ProductName=TableB.ProducName

would produce 4 rows, in the result.

Which of the records, from each of the table, have to be kept?

You can use a GROUP BY clause to 'aggregate' some fields, like:

SELECT ProductName, SUM(amountOfSale)
FROM TableA
GROUP BY ProductName

and then, you won't have duplicated values (in the groups).

You can also make the GROUPs after the join, instead of making them
individually on each table, but I doubt the resulting aggregate would be
meaningful.

Vanderghast, Access MVP

Hello All,
I need help. I made a query using two tables: prodform(which has 526

[quoted text clipped - 10 lines]

Thanks
LA

 

[Michel Walsh]

You probably used, added a DISTINCT.


TableA f1
a
a
b




TableB
g1
a
b
b



SELECT TableA.f1, TableB.g1
FROM TableA INNER JOIN TableB ON TableA.f1 = TableB.g1;


QueryAB
f1 g1
a a
a a
b b
b b

- 4 rows



SELECT DISTINCT TableA.f1, TableB.g1
FROM TableA INNER JOIN TableB ON TableA.f1 = TableB.g1;

QueryDISTINCT
f1 g1
a a
b b

- 2 rows