query with subtract value...

[sal21]

I have 2 table:
TAB1, TAB2

TAB2
F1 F2
1234 50000
1245 100

TAB1
F1 F2 DATA1
1234 500 12/01/2014
1234 500 12/01/2014
1245 100 08/01/2014

in TAB2
F1 is the unique ID, no duplicates

in TAB1
F1 possible ID duplicates

In F2 for all two tables are the amount.

I need to subtract to TAB2 with in TAB1 DATA1 =12/01/2014 but in the same time subtract the amount of deleted records to the related value in TAB2.....

After query:

TAB2
F1 F2
1234 4000
1245 100

TAB1
F1 F2 DATA1
1245 100 08/01/2014

[HansV]

In the first record in TAB2 in your example, F2 = 50000. Should that be 5000? Otherwise, I don't understand the result F2 = 4000.

[sal21]

In the first record in TAB2 in your example, F2 = 50000. Should that be 5000? Otherwise, I don't understand the result F2 = 4000.

Sorry....
F2 = 5000

[HansV]

You need to run two queries:

First, an update query to subtract F2 in TAB1 from F2 in TAB2 where 1DATA equals 12/01/2014:

UPDATE TAB2 INNER JOIN TAB1 ON TAB2.F1 = TAB1.F1 SET TAB2.F2 = [TAB2].[F2]-[TAB1].[F2] WHERE TAB1.DATA1=#1/12/2014#

(Remember that SQL uses m/d/yyyy format)

Second, a delete query to remove the records from TAB1 where DATA1 equals 12/01/2014:

DELETE DATA1 FROM TAB1 WHERE DATA1=#1/12/2014#