Operation must use an updateable query?

What does that error even mean? I've done some research, and the biggest problem was Permissions, but I've triple checked that, and permissions are NOT the problem. Anyone have any ideas?

I posted my code, just in case someone spotted an error

UPDATE
dbo_optionee AS opt,
TESTDTA_F060116 AS jde2,
TESTDTA_F0116 AS jde1,
QMaxDate
SET
opt.ADDRESS1 = UCASE(jde1.ALADD1),
opt.ADDRESS2 = UCASE(jde1.ALADD2),
opt.ADDRESS3 = UCASE(jde1.ALADD3),
opt.ADDRESS4 = UCASE(jde1.ALADD4),
opt.CITY = UCASE(jde1.ALCTY1),
opt.STATE = UCASE(jde1.ALADDS),
opt.ZIP = UCASE(jde1.ALADDZ)
WHERE
opt.SOC_SEC=jde2.YASSN And
jde2.YAAN8=jde1.ALAN8 And
jde1.ALAN8=QMaxDate.ALAN8 And
QMaxDate.MaxDate=ConvertJulian([jde1.ALEFTB]);

And here's the QMaxDate query (I guess the error is about this, but how?)

SELECT ALAN8, MAX(ConvertJulian([ALEFTB])) AS MaxDate
FROM TESTDTA_F0116
GROUP BY ALAN8;


Basically, the code is looking at a database, finding the most recent address for every person, and updating another database with that information.

Let me know if I can do anything to clarify my question... I really need help with this one

Thanks again guys,
Steve

check this out http://support.microsoft.com/defaul...b;en-us;q175168

I actually have- I made sure the first three aren't the case, so there's a problem with the 4th condition. But I'm not really sure how to fix it, I don't know what a crosstab is, SQL-pass through, etc. I am doing an Update, but I don't really know where to fix my problem. I saw a help document online that said to put the aggregate functions in the update statement itself, but doing so would have me but it in the WHERE part in this case. I don't know if there is a way to fix it...

sql server does not have stored queries like access does, in sql server you have to use a view for that

in this case a correlated subquery seems to be what you want

That actually looks like exactly what I want! But when I try to run it, it saying there is a syntax error (missing operator) at

UCASE(jde1.ALADDZ)
from dbo_optionee AS opt
inner
join TESTDTA_F060116 AS jde2
on opt.SOC_SEC...

The FROM statemtent is almost exactly what I was using when I was trying to get the right data, so I don't know what the problem is.

That's a great help though, I didn't know I could use FROM statements in UPDATE statements.

Anyway, if someone can help me fix this error, it would be HUGELY appreciated I'll be working on it too, so if I fix it, I shall let ya know.

Alright, got it working- I don't think Access lets me use FROM in the Update statement.

Here's what I ended up with

UPDATE
dbo_optionee AS opt,
TESTDTA_F060116 AS jde2,
TESTDTA_F0116 AS jde1
SET
opt.ADDRESS1 = UCASE(jde1.ALADD1),
opt.ADDRESS2 = UCASE(jde1.ALADD2),
opt.ADDRESS3 = UCASE(jde1.ALADD3),
opt.ADDRESS4 = UCASE(jde1.ALADD4),
opt.CITY = UCASE(jde1.ALCTY1),
opt.STATE = UCASE(jde1.ALADDS),
opt.ZIP = UCASE(jde1.ALADDZ)
WHERE
opt.SOC_SEC=jde2.YASSN And
jde2.YAAN8=jde1.ALAN8 And
(SELECT
MAX(ConvertJulian([ALEFTB]))
FROM TESTDTA_F0116
WHERE ALAN8 = jde1.ALAN8)
=ConvertJulian([jde1.ALEFTB]);


However, I still need some help, but it's a much different question. I'll post it in the Database development forum, titled 'Optimization'

tip: if you are really running access, and not sql server, don't post in the sql server forum

Ah, it said MS SQL, I thought that was just MicroSoft SQL implementations in general, not SQL Server specifically (which I would asssume would be a different abbreviation...)

Sorry about that...

no prob

you are not the first to do it, and i'm sure you won't be the last

perhaps the MS SQL forum should be renamed, but i doubt it will be, since the name was carefully chosen so that it turns up in search engines in response to certain targetted keywords