Update table from two subquery

[Goondu]

Hi,

If anyone could help me with the correct SQL syntax for Updating a table.

Here’s the code.

UPDATE Table1 INNER JOIN Table2 ON Table1.ItemNo = Table2.ItemNo
SET Table1.QtyBal = (subquery1 – subquery2)

The two subqueries are

Select Sum([Qty]) From Table2 Where IssueDate Between #01-Jan-01# and #30-Jan-01#

Select Sum([Qty]) From Table2 Where IssueDate Between #15-Jan-01# and #30-Jan-01#

Thanks

 

[lespaul]

Before addressing your posted problem I'd like to see if you can still change your table structure. Storing calculated fields breaks the rules of database design...have you read Fundamentals of Relational Database Design?

Leslie

Have you met Hardy Heron?

 

[PHV]

You may consider the DSum function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Goondu]

Thanks for the suggestion PHV.
This may work with the VBA part but do you have any solution for a standard access query? I can't get the SQL syntax right.

UPDATE Table1 INNER JOIN Table2 ON Table1.ItemNo = Table2.ItemNo SET Table1.QtyBal = DSum("Qty","Table2","IssueDate Between " & "#1-jan-01#" & "  And " & "#15-jan-01#")-DSum("Qty","Table2","IssueDate Between " & "#16-jan-01#" & " And " & "#30-jan-01#");

Leslie, Table1.QtyBal is a temporary table for a report where all calculated values are stored.