To calculate & update column using 2 fields from 2 different tables? 1

[rpangel]

Hello,
What I'm trying to do it calculate the number of keys available
(KEYS_AVAIL) after subtracting the NEXT_VALUE, in the
CTRL_KEY_VALUE table, from MAX_KEY_VALUE, in the OFFICE_CODES
table, to find out how many keys are left and to return the value in the
KEYS_AVAIL field in the OFFICE_CODES table for the corresponding
ELEMENT_NAME.

This is the code that I've gotten so far:
UPDATE OFFICE_CODES
SET OFFICE_CODES.KEYS_AVAIL = OFFICE_CODES.MAX_KEY_VALUE
- CTRL_KEY_VALUE.NEXT_VALUE
FROM OFFICE_CODES INNER JOIN CTRL_KEY_VALUE
ON OFFICE_CODES.ELEMENT_NAME =
CTRL_KEY_VALUE.ELEMENT_NAME ;

And this is the error I keep getting:
Syntax error (missing operator) in query expression '
OFFICE_CODES.MAX_KEY_VALUE - CTRL_KEY_VALUE.NEXT_VALUE
FROM OFFICE_CODES INNER JOIN CTRL_KEY_VALUE
ON OFFICE_CODES.ELEMENT_NAME =
CTRL_KEY_VALUE.ELEMENT_NAME ;

Does anyone have any suggestions or happen to know why it's doing
that or what I'm doing wrong?

Thanks in advance,
Angel

 

[tlbroadbent]

Angel,

Are you doing this query in Access or SQL Server. You've cross-posted this question so many times that I'm confused. Please stick to one thread so we have a chance to answer.

Thanks, Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[rpangel]

I'm testing it in Access but I'm planning on executing it on SQL Server that uses DB2. But, I didn't know I wasn't supposed to post in different forums. Sorry about the confusion. I hope I didnt' just confuse you more.

 

[tlbroadbent]

You can certainly post in different forums when necessary. However, as you can see, cross-posting causes a few problems.

The query syntax in Access (Jet) differs from SQL Server. When you posted to the SQL Server forum, there was no indication that the syntax error occurred in Access. I for one was struggling to find an answer because the syntax looked OK for SQL Server.

I've tried to create equivalent SQL and Access queries. Hopefully, you can get them to work in their respective environments.

SQL Syntax:
Update Office_Codes Set Keys_Avail =
(Office_Codes.Max_Key_Value - Ctrl_Key_Value.Next_Value)
From Office_Codes Inner Join Ctrl_Key_Value
On Office_Codes.Element_Name=Ctrl_Key_Value.Element_Name

Access Syntax:
Update Office_Codes Inner Join Ctrl_Key_Value
On Office_Codes.Element_Name=Ctrl_Key_Value.Element_Name
Set Office_Codes.Keys_Avail =
(Office_Codes.Max_Key_Value - Ctrl_Key_Value.Next_Value

It is very late and I'm tired so please forgive any errors that may have crept in. Good luck. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes