423,336 Members | 1,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,336 IT Pros & Developers. It's quick & easy.

UPDATE with WHERE conditions reference to fields in other table

P: 68
In my access database there is a transaction table named “T_Inv_Transaction” that has records of ingredient quantity generated when a Job_ID is created.

The fields in this table related to my question includes followings. There is no primary key assigned to this table.

Job_ID
ProdID
ProdV
BatchQTY
UOM

Each time when user hits a production button, a Job_ID is created by program. BatchQTY and UOM associated to specific Job_ID, ProdID, and ProdV are inserted into this table for tracking purpose.

The next step I need to develop is to update inventory in table “T_Inventory”. Fields in this table include followings with two primary keys: Prod_ID and Prod_V. This inventory table serves as storage place for ingredient quantity and get updated every time when a batch of recipe is produced.

Prod_ID
Prod_V
Qty
UOM

I am stuck with the UPDATE query statement. Is there any way the UPDATE with WHERE condition statement can reference to fields in other table? In this case, update quantity (Qty) in inventory table (with certain math) where Prod_ID and Prod_V equal to fields ProdID and ProdV in transaction table and with specific group of Job_ID?
Nov 8 '17 #1

✓ answered by NeoPa

Actually, it's interesting that SQL is a generic standard, but not all implementations of SQL are the same (or indeed fully standard).

In this case there actually is a problem with using Frinny's SQL in Access as Access uses the Jet SQL engine which has a slightly different syntax when dealing with linked tables within an UPDATE query.

As a general rule you should find the full syntax by following the instructions in Finding Jet SQL Help. In this particular case the linked tables must be defined within the UPDATE clause itself in order to get Access (Jet) to follow the instructions. The Help on this particular feature is poor (It isn't properly covered except for individual tables :-( ) but I was able to find the syntax from some old queries I'd done in the past.

So, Frinny's suggestion, for when using Jet SQL, would be :
Expand|Select|Wrap|Line Numbers
  1. UPDATE T_Inventory 
  2.        INNER JOIN
  3.        T_Inv_Transaction
  4.     ON T_Inv_Transaction.Prod_ID = T_Inventory.ProdID
  5. SET ....
  6. WHERE ...put your condition here
Another restriction within Jet SQL is that updates will only work if the underlying recordset is updatable. That is more restrictive in Jet/Access than in most other flavours of SQL. See Reasons for a Query to be Non-Updatable for more on that.

Share this Question
Share on Google+
10 Replies


Frinavale
Expert Mod 5K+
P: 9,731
You should be able to accomplish this by joining on the two tables.

Here is a rough example:
Expand|Select|Wrap|Line Numbers
  1. UPDATE T_Inventory
  2. SET ....
  3. FROM T_Inventory JOIN T_Inv_Transaction ON T_Inv_Transaction.Prod_ID = T_Inventory.ProdID 
  4. WHERE ...put your condition here
  5.  
Nov 8 '17 #2

P: 68
Thanks Frinavale. I will try this approach. I am not a VBA expert so it will take a while to tell results.
Nov 8 '17 #3

NeoPa
Expert Mod 15k+
P: 31,004
That shouldn't be a problem Joe, nor is Frinavale. This is SQL code not VBA. She's great at SQL. Not so much at VBA ;-)
Nov 12 '17 #4

NeoPa
Expert Mod 15k+
P: 31,004
Actually, it's interesting that SQL is a generic standard, but not all implementations of SQL are the same (or indeed fully standard).

In this case there actually is a problem with using Frinny's SQL in Access as Access uses the Jet SQL engine which has a slightly different syntax when dealing with linked tables within an UPDATE query.

As a general rule you should find the full syntax by following the instructions in Finding Jet SQL Help. In this particular case the linked tables must be defined within the UPDATE clause itself in order to get Access (Jet) to follow the instructions. The Help on this particular feature is poor (It isn't properly covered except for individual tables :-( ) but I was able to find the syntax from some old queries I'd done in the past.

So, Frinny's suggestion, for when using Jet SQL, would be :
Expand|Select|Wrap|Line Numbers
  1. UPDATE T_Inventory 
  2.        INNER JOIN
  3.        T_Inv_Transaction
  4.     ON T_Inv_Transaction.Prod_ID = T_Inventory.ProdID
  5. SET ....
  6. WHERE ...put your condition here
Another restriction within Jet SQL is that updates will only work if the underlying recordset is updatable. That is more restrictive in Jet/Access than in most other flavours of SQL. See Reasons for a Query to be Non-Updatable for more on that.
Nov 12 '17 #5

P: 68
It works! However, I had to put the query in a string and execute the query with DoCmd.RunSQL in order to make it work.

Somehow, when I put the update statement directly in the VBA, Access keep popping up error message asking for “expect end statement”.
Below is the codes that worked for this case.

Thanks to both Frinavale and NeoPa for concept and VBA helps.

Expand|Select|Wrap|Line Numbers
  1. Dim InvSQL As String
  2. Dim strJID As String
  3.  
  4. strJID = Me.txtJob_ID
  5.  
  6. InvSQL = "UPDATE [T_Inventory] INNER JOIN [T_Inv_Transaction] " & _
  7. "ON ([T_Inventory].[Prod_V] = [T_Inv_Transaction].[ProdV]) AND ([T_Inventory].[Prod_ID] = [T_Inv_Transaction].[ProdID]) " & _
  8. "SET [T_Inventory].[Qty] = NZ([T_Inventory].[Qty]) - [T_Inv_Transaction].[BatchQTY], “ & _ 
  9. “[T_Inventory].[UOM] = [T_Inv_Transaction].[UOM] " & _
  10. "WHERE [T_Inv_Transaction].[Job_ID] =" & " '" & strJID & "' "
  11.  
  12. DoCmd.RunSQL InvSQL
Nov 12 '17 #6

Frinavale
Expert Mod 5K+
P: 9,731
Glad it worked out for you!


And this is a reminder of why I tend to shy away from Access questions...the SQL I know and work with daily doesn't apply!

-Frinny
Nov 13 '17 #7

NeoPa
Expert Mod 15k+
P: 31,004
You know you're always welcome to post here Frinny. Someone will generally pick up if there are syntax variations for Jet/ACE.

Joe Y:
It works! However, I had to put the query in a string and execute the query with DoCmd.RunSQL in order to make it work.
Exactly. This is SQL. The VBA interpreter won't handle SQL code as it's SQL. It handles VBA code only.

The normal way of executing SQL from within your VBA code is to prepare a string with the SQL code in it and pass it to either the DoCmd.RunSQL() or {DatabaseObject}.Execute() methods. The former prompts the user depending on your settings. The latter doesn't. A {DatabaseObject} can be retrieved for the current database by running the function CurrentDb().
Nov 13 '17 #8

P: 68
NeoPa,

You are right. I am evaluating if the prompt from Access of "## record updated, Yes/No" is a good or bad thing to user.

Thanks,
Joe
Nov 13 '17 #9

P: 68
Frinny,

You guided me to the right direction of using Join. The prompt response and helps is why I like to ask question in this forum.

Much appreciated!
Nov 13 '17 #10

NeoPa
Expert Mod 15k+
P: 31,004
Joe Y:
You are right. I am evaluating if the prompt from Access of "## record updated, Yes/No" is a good or bad thing to user.
Yes. I'm generally right one way or another Joe (j/k).

When using the {DatabaseObject}.Execute() method you have access to that information via the {DatabaseObject}.RecordsAffected property. That then becomes available to you as a value within your VBA code. Display or not display - You have that choice. It won't naturally give the user an opportunity to proceed or turn back though. You could do that with a Transaction of course, but that would be a whole 'nother question.
Nov 13 '17 #11

Post your reply

Sign in to post your reply or Sign up for a free account.