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

 

[dobe1]

I did place an "into" statement in there so that I could see, if it would run from a table...to no avail.

Dobe

 

[dobe1]

What the above code does:
I am given a list of customers, whose day(s) of service may be M, W, F, etc. If the customer is being service M, W, F, there will be a 1, in the day_ field in one record, a 3 in the day_ field of the second record, and a 3 in the last record. In other words, there is a record for each day of the week a customer is serviced.

I wish to populate a field I have added called C_days with a 135 in each of these records. This lets me know the days of the week that this customer is serviced.

The above code gives the following results for example:

Custid currentdays
23001 135
23003 1
23451 25

 

[PHV]

Did you try my last suggestion (1 Feb 07 14:27) with the INTO clause removed ?

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

 

[dobe1]

How do you edit the post?

 

[dobe1]

Yes
see 1 Feb 07 15:49

 

[PHV]

I guessed that this error message raised due the INTO clause ...

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

 

[dobe1]

No,
actually, I wish to have an update query call the Days_Current query. In order to do this, it would make no sense to have the "into" statement in the code.

I only put the "into" statement in the code in order to test the possibility of the problem being a reference to the query vs. a table. As it turns out, the error is the same.

Dobe

 

[PHV]

Create a query named, say, qryCurrentDays :
SELECT custid, (Sum(IIf(day_=1,1,Null)) & Sum(IIf(day_=2,2,Null)) & Sum(IIf(day_=3,3,Null)) & Sum(IIf(day_=4,4,Null)) & Sum(IIf(day_=5,5,Null)) & Sum(IIf(day_=6,6,Null)) & Sum(IIf(day_=7,7,Null))) AS CurrentDays
FROM x
GROUP BY custid

And now your update query:
UPDATE x
SET c_days = DLookUp("CurrentDays", "qryCurrentDays", "custid='" & custid & "'")

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 again for your patience and your indepth help. I must close down for the day. I will be with y'all tomorrow.

Dobe