Update Table with value from another table, else NULL

Question

I was trying to Update a query on MS-Access. Let's Assume, I have two Tables (Employee, Department). I have (ID, Name, FK_DepartmentID) in Employee Table. And, two Column in Department(ID, DepartmentName) Table, Where I want to check if department Name exists in Department Table, then Update in Employee.FK_DepartmentID, else Null. Currently, I am updating with

UPDATE Employee, Department SET 
Employee.Name = 'David', 
Employee.FK_DepartmentID = Department.ID 
WHERE (((Employee.ID)='55') AND ((Department.DepartmentName)='HR');

So this is updating my Employee Table. But Let's say User input Department.DepartmentName='IT', where 'IT' doesn't exist in Department Table (Or User input nothing). With the current query, it executes but doesn't update any row. But I want it to update Employee.Name alongside Employee.DepartmentID = Null. What can I do so I can reach what I want.

Really appreciate your time and thanks in advance.

What you describe makes no sense. Why are you updating Employee Name field? Why do you show Department.Name field in query but there is no Department.Name field in your table description (it's DepartmentName)? Show sample data and desired result. Can't use nested SQL this way. Possibly you need DLookup() domain aggregate function. — June7, Jan 04 '21 at 04:53
Thanks for the answer. Sorry, I should've clarified that it actually is a relational DB, where ```Employee``` Table holds the FK of ```Department```. Where I want to Update ```Employe``` Table Data, as well as, Foreign Key of Department(If It exist, else Null). — Kamrul Hasan Rafi, Jan 04 '21 at 05:16
I had taken for granted this is a relational database since ms-access tag shown. Still not making sense. Build a form and select department from combobox. — June7, Jan 04 '21 at 05:30
Got it where I did the mess. Changed the question so it clears things up. Thanks again. — Kamrul Hasan Rafi, Jan 04 '21 at 06:23
Still don't understand why you would update employee Name field. WHERE clause makes no sense. Again, show sample data and desired result. — June7, Jan 04 '21 at 06:40
What you are trying to do is an upsert which is shorthand for select the value see if it exists and if it doesn't exist insert it otherwise update. Access doesn't have any syntactic sugar to make upserts easier but you can look that up while I write up the answer. — mazoula, Jan 04 '21 at 08:07
If @mazoula is on right track, review https://stackoverflow.com/questions/6199417/upserting-in-ms-access — June7, Jan 04 '21 at 10:17

mazoula · Answer 1 · 2021-01-05T08:48:19.500

What you are describing is an upsert, which is short for update or insert based on a select. you can find plenty more examples using the keywords ACCESS and Upsert. Access doesn't have special syntax for upserts. You have to write the select, update, insert, and logic yourself. I haven't found a way to get around using VBA for upserts in Access as you have to call a select query then choose either to update or insert. My example ended up being with strings but it is otherwise similar to what I think you are trying to do. So assuming a one to many relationship between departments and employees:

Here is the code be careful to notice that the strings are enclosed in ' ':

Public Sub UpsertEmployee(EmployeeName As String, departmentName As String)
Dim db As dao.Database
Set db = CurrentDb
Dim sqlstring As String
Dim employeeID As Integer
Dim departmentID As Integer
employeeID = SelectEmployee(EmployeeName)
departmentID = SelectDepartment(departmentName)
If employeeID = -1 And departmentID = -1 Then 'no employeee or department insert employeename with null department'

sqlstring = "INSERT INTO Employees (Name, FK_DepartmentID) VALUES( '" & EmployeeName & "', NULL)"

db.Execute (sqlstring)
ElseIf employeeID > 0 And departmentID > 0 Then 'got both update both'

sqlstring = "UPDATE Employees SET Name = '" & EmployeeName & "', FK_DepartmentID = " & departmentID & " WHERE ID = " & employeeID

db.Execute (sqlstring)
ElseIf employeeID > 0 And departmentID = -1 Then 'got employee and no department update department to null'
sqlstring = "UPDATE Employees SET Name = '" & EmployeeName & "', FK_DepartmentID = NULL" & " WHERE ID = " & employeeID
db.Execute (sqlstring)
ElseIf employeeID = -1 And departmentID > 0 Then 'no employee so insert and set department to correct id'
sqlstring = "INSERT INTO Employees (Name, FK_DepartmentID) VALUES( '" & EmployeeName & "', " & departmentID & ")"
db.Execute (sqlstring)
End If
db.Close
Set db = Nothing
End Sub

Public Function SelectDepartment(departmentName As String) As Integer
'normally I would give users a combobox to select department avoiding this problem entirely'
Dim db As dao.Database
Set db = CurrentDb
Dim rs As dao.Recordset
Dim sqlstring As String
sqlstring = "SELECT ID FROM Departments WHERE DepartmentName Like '" & departmentName & "'"
Set rs = db.OpenRecordset(sqlstring)
If rs.RecordCount > 0 Then 'no records so must insert'
rs.MoveFirst
SelectDepartment = rs(0)  'only returning first record here for simplicity'
Else
SelectDepartment = -1 '-1 is an impossible autonumber were using to indicate no record found'
End If
Set rs = Nothing
db.Close
Set db = Nothing
End Function
Public Function SelectEmployee(EmployeeName As String) As Integer
Dim db As dao.Database
Set db = CurrentDb
Dim rs As dao.Recordset
Dim sqlstring As String
sqlstring = "SELECT ID FROM Employees WHERE Name = '" & EmployeeName & "'"
Set rs = db.OpenRecordset(sqlstring)
If rs.RecordCount > 0 Then
rs.MoveFirst
SelectEmployee = rs(0)  'note employees with same name are a problem, etc; this is just an example'
Else
SelectEmployee = -1
End If
'clean up'
Set rs = Nothing
db.Close
Set db = Nothing
End Function

Thanks for the detailed solution. Your solution led me to know it is not possible in a single query. For Insert, I was using ```Insert Into Employee (Name, FK_DepartmentID) Select Top 1 [@emplyName] As EmployeeName, IIf(((SELECT d.ID As Department FROM Department d WHERE d.DepartmentName = @dName) Is Null),[d].[ID]=Null,(Select d.ID From Department d Where d.DepartmentName = @dName)) AS Department From Department As d;```, basically, It's checking if ```Department.ID``` exist, then insert ID, else NULL. So I was curious if the same thing was possible with the update. — Kamrul Hasan Rafi, Jan 04 '21 at 21:48
Also, like the idea to ```normally I would give users a combobox to select department avoiding this problem entirely```. But what if the user doesn't select anything? It will not update as it will be NULL value, which is not exist in the table. But I want to make it optional not mandotory. — Kamrul Hasan Rafi, Jan 04 '21 at 21:55
as usual for special cases you code for them. either insist they select a department, add null as an option to the combo, or use an if statement and deal with the case with vba. — mazoula, Jan 05 '21 at 08:25

Update Table with value from another table, else NULL

1 Answers1