I'm a newbie to Access. I have two tables (Tp and Temp). I want to update a field in the Temp table with the max of that field in the Tp table. Max([TP]. [QCTNO]) this returns null in an update query, but if I use it in a select query, it returns a value. TP table
id | TESTPACKAGE | QC SEND | QCTNO | QC RECIVE | QC RESULT |
---|---|---|---|---|---|
1 | AG-TP-520-AIR-0001 | 400.08.15 | 320 | 400.08.15 | UPDATE |
2 | AG-TP-520-AIRP-0001 | 400.01.24 | 250 | 401.01.28 | UPDATE |
3 | AG-TP-538-BUT-0001 | 400.04.14 | 254 | 401.01.28 | UPDATE |
4 | AG-TP-538-BUT-0002 | 400.04.14 | 251 | 401.01.28 | UPDATE |
5 | AG-TP-538-BUT-0003 | 400.07.05 | 152 | 401.01.28 | ACC |
6 | AG-TP-538-BUT-0004 | 400.07.05 | 254 | 401.01.28 | ACC |
7 | AG-TP-538-BUT-0005 | 400.05.17 | 245 | 401.01.28 | ACC |
8 | AG-TP-538-BUT-0006 | 400.05.17 | 142 | 401.01.28 | ACC |
9 | AG-TP-538-BUT-0007 | 401.01.28 | 175 | 401.01.28 | ACC |
10 | AG-TP-538-BUT-0008 | 401.01.28 | 198 | 401.01.28 | ACC |
11 | AG-TP-501-BUT-0001 | 401.01.28 | 250 | 401.01.28 | Return |
12 | AG-TP-537-BUT-0002 | 400.05.17 | 254 | 401.01.28 | Return |
13 | AG-TP-502-BUT-0003 | 401.01.28 | 574 | 401.01.28 | Return |
14 | AG-TP-502-BUT-0004 | 401.01.28 | 245 | 401.01.28 | Return |
15 | AG-TP-502-BUT-0005 | 400.05.17 | 283 | 401.01.28 | Return |
16 | AG-TP-502-BUT-0006 | 400.05.17 | 282 | 401.01.28 | Return |
17 | AG-TP-502-BUT-0007 | 400.05.17 | 247 | 401.01.28 | Return |
Temp table
id | TESTPACKAGE | QC SEND | QCTNO | QC RECIVE | QC RESULT |
---|---|---|---|---|---|
9 | AG-TP-538-BUT-0007 | 401.01.28 | 175 | 401.01.28 | ACC |
10 | AG-TP-538-BUT-0008 | 401.01.28 | 198 | 401.01.28 | ACC |
11 | AG-TP-501-BUT-0001 | 401.01.28 | 250 | 401.01.28 | Return |
what I need
id | TESTPACKAGE | QC SEND | QCTNO | QC RECIVE | QC RESULT |
---|---|---|---|---|---|
9 | AG-TP-538-BUT-0007 | 401.01.28 | 574 | ||
10 | AG-TP-538-BUT-0008 | 401.01.28 | 574 | ||
11 | AG-TP-501-BUT-0001 | 401.01.28 | 574 |
CodePudding user response:
Although saving calculated value is strongly discouraged (not recommended) but if you really interested to do it then use domain aggregate function DMAX()
to update query. Try-
UPDATE TempTable SET [QCTNO]=DMAX("[QCTNO]","[TP]");