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
query with subtract value...
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: query with subtract value...
In the first record in TAB2 in your example, F2 = 50000. Should that be 5000? Otherwise, I don't understand the result F2 = 4000.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4402
- Joined: 26 Apr 2010, 17:36
Re: query with subtract value...
Sorry....HansV wrote:In the first record in TAB2 in your example, F2 = 50000. Should that be 5000? Otherwise, I don't understand the result F2 = 4000.
F2 = 5000
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: query with subtract value...
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#
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#
Best wishes,
Hans
Hans