I have 2 tables:
tblAbsence
Name Start End Status John 4/2/18 4/5/18 Approved Sue 5/1/18 5/10/18 Denied
tblManning
Date Required 4/1/18 3 4/2/18 4 4/3/18 2
I would like to be able to update tblAbsence.Status to “Approved” as long as the Required value during the absence request doesn’t exceed an arbitrary limit (4 for example).
After looking at @Jeffrey’s proposal and experimenting I think I need an Update query which Sets [Status]= Approved when leave request is not found in query below. The below query will tell me if a request has any days that exceed the Limit.
SELECT tblAbsence.name, tblAbsence.start, tblAbsence.end FROM tblAbsence, tblManning WHERE tblManning.Date >= Int([tblAbsence].[Start]) And tblManning.Date <= [tblAbsence].[End] AND tblManning.[Req]>3 GROUP BY tblAbsence.name, tblAbsence.[Start], tblAbsence.end;
I renamed the above query as qryLv and tried this Left Join but it tells me I must use an updatable query????
Update tblAbsence a Left Join qryLv L ON L.name = a.name AND l.start = a.start SET a.Status = "Approved" WHERE l.name is null;
Advertisement
Answer
Your error is showing because you have to either equate or use IN/EXISTS with a variable when using subqueries in WHERE clauses. However, I’m not sure you need a subquery, I think something like this will work:
UPDATE tblAbsence SET tblAbsence.Status = "Approved" FROM tblAbsence, tblManning WHERE tblManning.Date > tblAbsence.Start AND tblManning.Date <= tblAbsence.End + #23:59:59# AND tblManning.Required < 4;
Ok, kinda missed that part, you will need a subquery. I think you need to use NOT EXISTS just to check if your subquery returns any values or not and that should work for you.
UPDATE tblAbsence SET tblAbsence.Status = "Approved" FROM tblAbsence WHERE NOT EXISTS ( SELECT NULL FROM tblManning WHERE tblManning.Date > tblAbsence.Start AND tblManning.Date <= tblAbsence.End + #23:59:59# AND tblManning.Required < 4);
I think you can use NULL in the subquery since you are not returning records, just a boolean, but if it doesn’t work you can replace it with *.