Duplicate values in query

[C Tate]

The database I work with has many queries set up - often one query is based
on another. Sometimes one of the queries has lots of duplicate records which
seems to be a mistake and something I can't understand. Why might this be?
What can be done to avoid it? I actually turned on the grouping to get rid
of the duplicates but I am sure this is probably not the best way forward.
No doubt they are designed wrongly?

 

[[MVP] S.Clark]

You can post the SQL, but "wrongly" is always subject to interpretation.
The needs at the time may have warranted the dupped records.

FWIW, all queries have a Unique Records and Unique Values property that you
can set as well as use Group By.

 

[[MVP] S.Clark]

You can post the SQL, but "wrongly" is always subject to interpretation.
The needs at the time may have warranted the dupped records.

FWIW, all queries have a Unique Records and Unique Values property that you
can set as well as use Group By.

 

[Michel Walsh]

... duplicated reply for a duplicated value ...


V.

 

[C Tate]

Many thanks. I will post the sql (though I have no knowledge of that
language) in a couple of days (perhaps under a new thread?) since I am off
work at present. However, I have discovered a further something about the
query which seems odd.

Basically our database keeps records on homeless people. If they loose
contact with a service they are 'closed'. If they come back they are
're-opened'. This re-opening process basically seems to involve an append
query to create a duplicate record in the 'bio' table. In addition to this
table is another called 'outcomes'.

When these two tables are used together in a query, the re-opening process
seems to cause duplicate entries to appear in the query results - making it
appear that there are two outcomes for a client instead of one, or eight
instead of four. However, no duplicate outcomes appear in the 'outcomes'
table.

I am totally mystified as to why this should happen but it evidently has
something to do with the 're-opening' process and the append query.