General discussion

Locked

Duplicate Records in a Query

By tlscruggs ·
I've created a query utilizing multiple tables. I am not getting the results as expected. The query is displaying results from the underlying tables and duplicating data. I want it to display one instance of the record and not duplicate.I've looked at the UniqueRecords properties. What step am I not taking?

This conversation is currently closed to new comments.

16 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Duplicate Records in a Query

by roc180 In reply to Duplicate Records in a Qu ...

Sounds like you are doing a many to many join in your query. To fix the problem look at your join criteria, try to uniquely define the join by specifying multiple fields to join on:
(T1.id=T2.id AND T1.name=T2.name),
or use a GROUP BY clause to flatten your results :
SELECT F1,F2,F3
FROM T1
WHERE F1=Something
GROUP BY F1,F2,F3

If this dosen't solve your problem you cansend me the SQL and your tables and I can take a look at that.

Good Luck

Neal Martinelli

Collapse -

Duplicate Records in a Query

by tlscruggs In reply to Duplicate Records in a Qu ...

Poster rated this answer

Collapse -

Duplicate Records in a Query

by SteveD In reply to Duplicate Records in a Qu ...

Did you try setting Unique Records and/or Unique Values to Yes?

Collapse -

Duplicate Records in a Query

by tlscruggs In reply to Duplicate Records in a Qu ...
Collapse -

Duplicate Records in a Query

by donq In reply to Duplicate Records in a Qu ...

Your data source is NOT normalized or your Tables aren't properly indexed IF there aren't supposed to be any duplicated? The easiest fix for you is to open your existing query in SQL View and enter the word "DISTINCT" rifht behind "SELECT" as in SELECT DISTINCT <Table1>.<Field1> .....

Collapse -

Duplicate Records in a Query

by tlscruggs In reply to Duplicate Records in a Qu ...

My SQL begins with SELECT DISTINCT <Table> . <Field>

Collapse -

Duplicate Records in a Query

by davram In reply to Duplicate Records in a Qu ...

Why don't you just post the SQL query, also? It would be a lot easier for the rest of us to point you the error.
You probably should provide us with some informations about the technical environment you are using, too.
Thanks.

Collapse -

Duplicate Records in a Query

by tlscruggs In reply to Duplicate Records in a Qu ...

Poster rated this answer

Collapse -

Duplicate Records in a Query

by LadySkyDiver In reply to Duplicate Records in a Qu ...

If your tables are normalized, you not only want to use DISTINCT but double check your join properties between the tables. Are you doing an inner, left/right outer, or full outer join? Depending on the type of join you do will, also, depend on your result set.

Collapse -

Duplicate Records in a Query

by tlscruggs In reply to Duplicate Records in a Qu ...

Poster rated this answer

Back to Web Development Forum
16 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Software Forums