Update Query not Updateable!

[RobTsintas]

A little background:

I'm trying to get an update query to update individuals' demographic information from an imported file. This has already been imported into a new table ("[blue]ImportDem[/blue]").

The nature of the source data means that each person appears several times in [blue]ImportDem[/blue] (with all the same information in each record), so I used a query to reduce these to one record each (Unique Values = Yes).

I have now set up an update query to update the relevant fields in the "[blue]Main Table[/blue]" table from the results of that 'reducing query'.

Even though I am not updating any of the fields from the 'reducing query', it is still causing an "Operation must use an Updateable Query" error.

Is there a simple way around this problem?

The only thing I can think of is to use a maketable query to store the 'reduced' imported data in another table, and then use that as the source (which should then allow me to update), but this seems a bit messy and shouldn't be necessary - I'd rather not have all the imported data stored twice!

Thanks in advance...

 

[PHV]

Can you post the SQL code of your update query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Crowley16]

it sounds like you are trying to use a select query when trying to update your MAIN table...

the sql you will need should look something like:

INSERT INTO MainTable(field1, field2...) SELECT field1, field2... FROM importdem;

 

[RobTsintas]

Crowley16: I'm trying to update existing records in [blue]MainTable[/blue], not add new ones.

Here is the SQL. [blue]MAINTABLE[/blue] is the table I'm trying to update, [blue]Import - Duplicateless (ImportDem)[/blue] is the query that reduces duplicate records in the imported [blue]ImportDem[/blue] table.

UPDATE [Import - Duplicateless (ImportDem)] INNER JOIN MAINTABLE ON [Import - Duplicateless (ImportDem)].[ID Number] = MAINTABLE.UNIT_NUMBER SET MAINTABLE.ID2 = [Import - Duplicateless (ImportDem)].[ID2], MAINTABLE.SURNAME = [Import - Duplicateless (ImportDem)].[Surname], MAINTABLE.FIRSTNAMES = [Import - Duplicateless (ImportDem)].[First Forename], MAINTABLE.SEX = [Import - Duplicateless (ImportDem)].[Sex], MAINTABLE.DOB = [Import - Duplicateless (ImportDem)].[Date of Birth], MAINTABLE.[Race/ethnic origin] = [Import - Duplicateless (ImportDem)].[Ethnic], MAINTABLE.[Martial status] = [Import - Duplicateless (ImportDem)].[Marital Status], MAINTABLE.[Last Refresh Date] = [Import - Duplicateless (ImportDem)].[LastRefreshDate];

I'm sure the problem comes from the fact that I've linked the [blue]MAINTABLE[/blue] to a 'SELECT DISTINCT' query in order to match the records up. It's silly that this causes the error though, as I don't want to update anything in that query, so it doesn't have to worry about matching-back to the reduced duplicates.

 

[PHV]

Have you tried to exchange the join, like this:
UPDATE MAINTABLE INNER JOIN [Import - Duplicateless (ImportDem)] ON MAINTABLE.UNIT_NUMBER = [Import - Duplicateless (ImportDem)].[ID Number] SET ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RobTsintas]

PHV: Still the same problem I'm afraid.

 

[PHV]

So, either play with a temporary table or use the ImportDem table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Crowley16]

or you could try to do it by one big query...

UPDATE MAINTABLE AS a
SET ID2 = (
SELECT ID2 FROM [Import - Duplicateless (ImportDem)]
WHERE [ID Number] = a.UNIT_NUMBER
),
SURNAME = (
SELECT ...
),
...

 

[RobTsintas]

Thanks for your help both. In the meantime I've been playing around with some code to follow the steps to remove duplicates (ie. Copy the table structure, make the ID field a Primary Key, append the fields from the original, and rename the new table to replace the original).

This seems to work fairly well so I think I'll just stick with this for now.

Thanks for your time anyway.

 

[JonFer]

Put the primary key on ImportDem before you import so it doesn't allow duplicates in the first place. Then you can join it to your main table directly for updates.