?
Solved

MS Access and self reference table

Posted on 2016-11-02
4
Medium Priority
?
129 Views
Last Modified: 2016-11-03
I have a table of employees.  There is a employee supervisor column which points to the employee record of the supervisor.  So, the table references itself.  The table has a dept column.   if the dept of the supervisor changes, I want to change the dept for all employees that have that supervisor.   I would know how to do this using SQL server, but I can't get it to work using MS Access.
0
Comment
Question by:HLRosenberger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 41871031
try this query

UPDATE tblEmployees INNER JOIN tblEmployees AS tblEmployees_1 ON tblEmployees.Supervisor = tblEmployees_1.EmployeeID SET tblEmployees.Dept = [tblEmployees_1].[Dept]
WHERE (((tblEmployees_1.EmployeeID)=5));

you have to place the EmployeeID of the supervisor in the criteria
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 1000 total points
ID: 41871187
In general:

UPDATE Employee As e INNER JOIN Employee As s ON e.SupervisorID = s.EmployeeID
SET e.Dept = s.Dept
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41871194
;-)
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 41872024
Thanks!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question