Help on Update with a Join

[dobe1]

Need some expert help please.

I am attempting to update a table in MS Access with values from another table. I have attempted the following:

UPDATE x AS a SET a.c_days = (select b.currentdays
from Days_Current b
where b.custid = a.custid)
where a.custid in (select c.custid
from days_current c)

Where x is the table to update and days_current is a query.

The error I recieve is "Operation must use an updatable query".

Thank you in advance,

Dobe

 

[r937]

try like this (untested, so take a backup first) --

update x
inner 
  join Days_Current as b 
    on x.custid = b.custid
   set x.c_days = b.currentdays

r937.com | rudy.ca

 

[dobe1]

Thanks,
But I receive the same error.

Dobe

 

[gwinn7]

It is not updateable because there are repeated matches between your two tables. You can avoid this by removing duplicate entries in one of the tables, or I believe using a DISTINCT clause in some circumstances can correct this.

Gary
gwinn7

 

[PHV]

And what about this ?
UPDATE x
SET c_days = DLookUp("currentdays", "Days_Current", "custid=" & custid)
WHERE custid IN (SELECT custid FROM days_current)


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

 

[dobe1]

Thanks Gwinn,
You are probably correct. I do have duplicate custid's in the table I wish to update. So how do I update all including the duplicates. The query Days_Current does not have duplicates, but the table X does.

Dobe

 

[PHV]

Dobe, have you tried my suggestion ?

 

[dobe1]

I am working on it now. I have received an error, although different. It reads through all records and gives the usual message

"Microsoft Access can't update all the records in the update query.

Microsoft Office Access didn't update 3019(all of my records in my table) field(s) DUE TO A Type converstion failure, 0 record(s) due to key violations, 0 record(s), due to key violations, 0 record(s) due to lock violations, and 0 record(s) violations.

 

[dobe1]

Note that Days_Current is a query and not a table. Could that be a problem?

 

[PHV]

I guess that custid is not numeric:
UPDATE x
SET c_days = DLookUp("currentdays", "Days_Current", "custid=[tt][!]'[/!]"[/tt] & custid [tt][!]& "'"[/!][/tt])
WHERE custid IN (SELECT custid FROM days_current)


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

 

[dobe1]

Correct. Neither custid is numeric.

I will give this a try in a minute.

 

[dobe1]

The error reads:

"An Action Query can not be used as a row source."

I am open to any ideas on an update via join.


How do other do this in Access?

Dobe

 

[PHV]

An Action Query can not be used as a row source.
As a row source to WHAT ?
I gave you SQL code you have to execute !

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

 

[lespaul]

PHV, Not sure if you saw this post earlier:

Note that Days_Current is a query and not a table. Could that be a problem?

les

 

[PHV]

Leslie, that shouldn't matter.
Dobe, where is the SQL code located ?

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

 

[dobe1]

The code is setting resident on my pc in a local database.

Dobe

 

[PHV]

{i]The code is setting resident on my pc in a local database[/i]
Seems you didn't really understood my question.
In a QueryDef ? In VBA code ? In a RowSource property ? In a RecordSource property ? ...

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

 

[dobe1]

Sorry, and you are correct. I didn't understand.

I am running this from a query.

Dobe

 

[PHV]

BTW, perhaps Leslie was on the target !
What is the SQL code of Days_Current ?

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

 

[dobe1]

SELECT custid, (Mon & Tue & Wed & Thu & Fri & Sat & Sun) AS CurrentDays
FROM [SELECT custid, sum(day_1) AS Mon, sum(day_2) AS Tue, Sum(day_3) AS Wed, sum(day_4) AS Thu, sum(day_5) AS Fri, Sum(day_6) AS Sat, Sum(day_7) AS Sun
from(
SELECT custid,
switch(day_ = 1, '1') as Day_1,
switch(day_ = 2, '2') as Day_2,
switch(day_ = 3, '3') as Day_3,
switch(day_ = 4, '4') as Day_4,
switch(day_ = 5, '5') as Day_5,
switch(day_ = 6, '6') as Day_6,
switch(day_ = 7, '7') as Day_7
from x
group by custid, switch(day_ = 1, '1'), switch(day_ = 2, '2'), switch(day_ = 3, '3'), switch(day_ = 4, '4'), switch(day_ = 5, '5'), switch(day_ = 6, '6'), switch(day_ = 7, '7'))
group by custid
order by custid]. AS [%$##@_Alias];