Double-ebtries in Update Query

[Jake]

Hi

I have an Update Query which is updating [contract] field to [contract] &
" - " & [contractNumber]

However, for one particular office only, it is updating the field TWICE, ie

Sheffield NHS - 3322659
Leeds NHS - 2558788 - 2558788

I have no idea why Leeds is being updated twice, can anyone help?

Thanks

Jake

 

[John Vinson]

Hi

I have an Update Query which is updating [contract] field to [contract] &
" - " & [contractNumber]

However, for one particular office only, it is updating the field TWICE, ie

Sheffield NHS - 3322659
Leeds NHS - 2558788 - 2558788

I have no idea why Leeds is being updated twice, can anyone help?

Care to post the SQL code of the query? I'd guess that there are two
records for Leeds being returned by the query, but without seeing the
query or knowing your table data it's hard to say why.

Try changing the query (temporarily) back to a simple Select query and
open it as a datasheet; see if the Leeds office is being displayed
twice.

 

[Jake]

Hi John

Yes, it appears that Leeds appears twice ! Many thanks

SO:

I have an append query which appends data from a link table into a normal
table, with the fields [region] and [office].

So how would I append only distinct rows from the link table? If I set the
Unique Values property to Yes in the append query then Access crashes !!! If
I set the Unique Records property to Yes I still get the duplicates.

Can I eliminate duplicates using an update query?

Many thanks

Jake

Hi

I have an Update Query which is updating [contract] field to [contract] &
" - " & [contractNumber]

However, for one particular office only, it is updating the field TWICE, ie

Sheffield NHS - 3322659
Leeds NHS - 2558788 - 2558788

I have no idea why Leeds is being updated twice, can anyone help?

Care to post the SQL code of the query? I'd guess that there are two
records for Leeds being returned by the query, but without seeing the
query or knowing your table data it's hard to say why.

Try changing the query (temporarily) back to a simple Select query and
open it as a datasheet; see if the Leeds office is being displayed
twice.

 

[John Vinson]

So how would I append only distinct rows from the link table? If I set the
Unique Values property to Yes in the append query then Access crashes !!! If
I set the Unique Records property to Yes I still get the duplicates.

Can I eliminate duplicates using an update query?

Set the Unique Values property <wry grin>

Access should NOT crash by setting this property of a query, whether
it's a select query or an append query. First off, use Tools...
Database Utilities... Compact and Repair to be sure the database isn't
blatantly corrupted; you may also want to copy the SQL view of the
query out to Notepad, delete the query, compact the database, and copy
and paste the SQL back into the SQL window of a new query. Try setting
it in a Select query; does Access crash? If not, does it crash if you
then change it to an Append?