Query results in duplicate data,help?

[Guest]

hi,
i designed a query that consists of several tables, now all of the tables
dont have direct relation with each other, but i need to retrieve data from
all of them almost according to the query parameter, now what happens is that
when i run my query it generates the data but records are duplicated , how
can i filter the duplication?

 

[Steve Schapel]

Farman,

This *might* provide the result you require... In design view of the
query, right-click anywhere on the background of the upper panel of the
query design window, and then select Properties from the pop-up menu.
Set the Unique Values property to Yes.

If that doesn't fix it, you will need to provide more details of your
tables and data. If you select SQL from the View menu in query design,
and copy/paste the SQL of the query into your post here, it will help.

 

[J Scott]

I have had the same problem recently. Setting the properties to unique
values was not sufficient to remedy the problem, because Access
apparently still matches each record in table B that matches the value
in the joined field the number of times the joined field value occurs
in table A, regardless of the unique value setting. What seems to
solve this is to first create a query that only returns the unique
values for the join. Then, create another query in which you join
table B to the first query. The resulting dynaset does not return
duplicate records.

I would be very interested to hear from advanced Access users about
this conclusion. Also, can anyone recommend a good source of sample
queries that address issues of this type? Incidentally, is there
documentation of standardized nomenclature for describing these issues?

Thanks.

J Scott

 

[Steve Schapel]

J Scott,

Setting the Unique Values property of the query, which is equivalent to
using the DISTINCT keyword in SQL, should not behave as you described.
The data returned should not include any rows where the data in all
columns is the same as the data in any other row. However, maybe you
were setting the Unique Records property of the query instead?

"SQL Queries for Mere Mortals", by John Viescas and Michael Hernandez,
is a very good reference source.