CODEWITHSUNDEEP
ms-accessms-access-2010ms-access-2013
user2652375
user2652375

Reputation: 103

Update field in Table when conditions fulfilled in MS Access 2013

I have two tables in MS Access 2013.

Table of SERVICE:

(ID is the primary key) 

ID PART_ID SERVICE_DATE REMARK WITHDRAWN
1   A0001   01/04/2014 
2   A0002   01/04/2014
3   A0003   01/04/2014  SCRAP
4   A0004   01/04/2014
5   A0001   01/05/2014
6   A0002   01/05/2014

Table of WITHDRAW:

(ID is the primary key) 

ID PART_ID DRAW_DATE
1   A0001  02/04/2014
2   A0002  02/04/2014
3   A0001  02/05/2014

I'd like to put a "NO" into the field of WITHDRAWN in Table of SERVICE to indicate the part has not been withdraw yet, if:

  1. Part is first time in Table of SERVICE and never appear in Table of WITHDRAW.
  2. Part appears few times in Table of SERVICE and Table of WITHDRAW, already serviced this time, not withdraw yet.
  3. Part is not under category of "SCRAP".

Targetted Result:

Table of SERVICE 

ID PART_ID SERVICE_DATE REMARK WITHDRAWN
1   A0001   01/04/2014 
2   A0002   01/04/2014
3   A0003   01/04/2014  SCRAP
4   A0004   01/04/2014            NO
5   A0001   01/05/2014
6   A0002   01/05/2014            NO

But I just know how to write the code for the first condition:

UPDATE SERVICE LEFT JOIN WITHDRAW ON SERVICE.PART_ID = WITHDRAW.PART_ID SET SERVICE.WITHDRAWN = "NO" WHERE WITHDRAW.PART_ID Is Null;

Can somebody amend my code so that I can achieve the targetted result? Thanks!

Upvotes: 0

Views: 113

Answers (1)

Yoh
Yoh

Reputation: 688

I think what you are looking for is the following query:

UPDATE service 
       LEFT JOIN withdraw 
              ON service.part_id = withdraw.part_id 
                 AND service.service_date <= withdraw.draw_date 
SET    service.withdrawn = "no" 
WHERE  withdraw.part_id IS NULL 
       AND Nz(service.remark, "") <> "scrap";

Your rules where a bit hard to understand, but I think I got it. I threw rule 1 and 2 together as follows:

1.Part is first time in Table of SERVICE and never appear in Table of WITHDRAW.

2.Part appears few times in Table of SERVICE and Table of WITHDRAW, already serviced this time, not withdraw yet.

==> The service_date of the part must be greater than the last withdraw_date for every part.

Upvotes: 1

Related Questions