Home > OS >  MAX returns NULL
MAX returns NULL

Time:07-22

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]"); 
  • Related