UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Upodate Query Based On Unmatched Query, Access 2016    
 
   
ordnance1
post Mar 1 2018, 09:52 PM
Post#1



Posts: 541
Joined: 7-May 11



I created an unmatched query to find EmployeeID's that appear in tbl_DaysOff but do not appear in WTA_CURRENT_OPS_EMPLOYEES. how can I then update field Change (in tbl_DaysOff) to 6. When I have an unmatched Employee ID(s) that indicates the employee has resigned or has been terminated.

CODE
SELECT tbl_DaysOff.RequestDate, tbl_DaysOff.EmployeeID
FROM tbl_DaysOff LEFT JOIN WTA_CURRENT_OPS_EMPLOYEES ON tbl_DaysOff.[EmployeeID] = WTA_CURRENT_OPS_EMPLOYEES.[BadgeNum]
WHERE (((tbl_DaysOff.RequestDate)>=Date()) AND ((WTA_CURRENT_OPS_EMPLOYEES.BadgeNum) Is Null));



Attached File  Untitled_picture.png ( 16.62K )Number of downloads: 2

Go to the top of the page
 
MadPiet
post Mar 1 2018, 10:25 PM
Post#2



Posts: 2,494
Joined: 27-February 09



I threw this together... it's in SQL Server, but should work fine in Access too. The CREATE TABLE and INSERT statements are just so I would have some data to test on. (Yeah, those tables need some PRIMARY and FOREIGN keys...)

Create tables...
CODE
CREATE TABLE DaysOff (
    EmployeeID INT NOT NULL,
    Change TINYINT DEFAULT 2
    );

CREATE TABLE WTA_CURRENT_OPS_EMPLOYEES (
   EmployeeID INT PRIMARY KEY
);
GO


Populate;
CODE
INSERT INTO DaysOff(EmployeeID) VALUES (100),(101),(102);
INSERT INTO WTA_CURRENT_OPS_EMPLOYEES (EmployeeID) VALUES (100), (101);


Test...
CODE
-- find EmployeeIDs in DaysOff that are not in WTA...
SELECT do.EmployeeID
FROM DaysOff do
WHERE NOT EXISTS (SELECT 1 FROM WTA_CURRENT_OPS_EMPLOYEES we
                    WHERE we.EmployeeID = do.EmployeeID);

-- update the DaysOff records belonging to that employee to Change=6
UPDATE DaysOff
SET Change = 6
WHERE NOT EXISTS (SELECT 1 FROM WTA_CURRENT_OPS_EMPLOYEES we
                    WHERE we.EmployeeID = DaysOff.EmployeeID);

This post has been edited by MadPiet: Mar 1 2018, 10:44 PM
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 03:44 AM