table MOVIM_CB
DATA1 CB AN
25/05/2014 AAAA
27/06/2014 BBBB
...
table ANOM_CB
DATA1 CB
25/05/2014 AAAA
27/06/2014 BBBB
25/05/2014 AAAA
25/05/2014 BBBB
I need a sql to update field AN of table MOVIM_CB with the count CB of table ANOM_CB based the date=25/05/2014
final result:
table MOVIM_CB
DATA1 CB AN
25/05/2014 AAAA 2
27/06/2014 BBBB
UPPDATE fileds with count
-
- Administrator
- Posts: 78446
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: UPPDATE fileds with count
For example:
UPDATE MOVIM_CB INNER JOIN ANOM_CB ON (MOVIM_CB.CB = ANOM_CB.CB) AND (MOVIM_CB.DATA1 = ANOM_CB.DATA1) SET MOVIM_CB.AN = DCount("*","ANOM_CB","DATA1=#2014-05-25# AND CB='" & [MOVIM_CB].[CB] & "'")
WHERE (((ANOM_CB.DATA1)=#5/25/2014#));
UPDATE MOVIM_CB INNER JOIN ANOM_CB ON (MOVIM_CB.CB = ANOM_CB.CB) AND (MOVIM_CB.DATA1 = ANOM_CB.DATA1) SET MOVIM_CB.AN = DCount("*","ANOM_CB","DATA1=#2014-05-25# AND CB='" & [MOVIM_CB].[CB] & "'")
WHERE (((ANOM_CB.DATA1)=#5/25/2014#));
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4353
- Joined: 26 Apr 2010, 17:36
Re: UPPDATE fileds with count
sorry me but i can't test now...HansV wrote:For example:
UPDATE MOVIM_CB INNER JOIN ANOM_CB ON (MOVIM_CB.CB = ANOM_CB.CB) AND (MOVIM_CB.DATA1 = ANOM_CB.DATA1) SET MOVIM_CB.AN = DCount("*","ANOM_CB","DATA1=#2014-05-25# AND CB='" & [MOVIM_CB].[CB] & "'")
WHERE (((ANOM_CB.DATA1)=#5/25/2014#));