person in multiple departments

[bal_sagoth]

I am trying to get a DB together that includes information about employees.
Some of the employees are in multiple departments. I am wondering how to set
up the DB so I can write a query that will list just employees and their
respective departments?

 

[John Vinson]

I am trying to get a DB together that includes information about employees.
Some of the employees are in multiple departments. I am wondering how to set
up the DB so I can write a query that will list just employees and their
respective departments?

Sounds like you have a many (people) to many (departments)
relationship. Any many to many requires a *new table* to contain the
relationship:

Employees
EmployeeID
LastName
FirstName
<other bio info>

Departments
DepartmentID
DepartmentName
<other info about the department itself>

DepartmentAssignments
EmployeeID << link to Employees
DepartmentID << link to Departments
<any other info about *this* employee in *this* department, e.g. the
employee's role in the department, percent time allocated, date
assigned, etc.>


John W. Vinson[MVP]

 

[bal_sagoth]

Thank you John, But how do you I write a query to pull up the names of
employees for a certain department?

 

[John Vinson]

Thank you John, But how do you I write a query to pull up the names of
employees for a certain department?

Create a query joining all three tables. Select the employee name
fields from the employee table, and the department name field from the
department table. Put a criterion on the latter and open the query!

On a Form, it's convenient to have a Form based on (say) the
Department table, with a subform based on the DepartmentAssignment
table; put a combo box on this subform, storing the employeeID but
displaying their names.

John W. Vinson[MVP]

 

[bal_sagoth]

Ok, thank you. Now, next question.

I have 4 tables now.
Computer Info
departments
employees
department assignments
As you know, there are some people in more than one department in the
database. I am trying to put together a query that shows who has a certain
model of computer. The query has fields name, department, model, computerID.
I would like to know how to exclude mutiple names in the query. The problem
is that it will return results with John Doe 3 times because he is in 3
different departments.

Thanx John!

 

[John Vinson]

Ok, thank you. Now, next question.

I have 4 tables now.
Computer Info
departments
employees
department assignments
As you know, there are some people in more than one department in the
database. I am trying to put together a query that shows who has a certain
model of computer. The query has fields name, department, model, computerID.
I would like to know how to exclude mutiple names in the query. The problem
is that it will return results with John Doe 3 times because he is in 3
different departments.

Don't include any of the records from the [Department Assignments]
table in the query - just use the table for joining - and set the
query's Unique Values property to True.

How is the [Computer Info] table related? If you're only interested in
names and computer info, and if they are related tables, just leave
the Department and Department Assignments out of the query altogether;
if you want to see the department, then... which of the three
departments do you want to se???

John W. Vinson[MVP]

 

[bal_sagoth]

John,

Even with the unique value set to 'yes' I am still getting duplicate names
in the query. The query is suppose to show which employees use a dimension
computer, from which department, and that computer's ID. I get duplicate
entries for, say, John and Travis. Help! In the tables are

Computer Info:
computerID
type
date
model
employeeID

Department:
Department
departmentID

Employee:
employeeID
First name
Last name
extension

DepartmentAssignments:
EmployeeID
DepartmentID

Ok, thank you. Now, next question.

I have 4 tables now.
Computer Info
departments
employees
department assignments
As you know, there are some people in more than one department in the
database. I am trying to put together a query that shows who has a certain
model of computer. The query has fields name, department, model, computerID.
I would like to know how to exclude mutiple names in the query. The problem
is that it will return results with John Doe 3 times because he is in 3
different departments.

Don't include any of the records from the [Department Assignments]
table in the query - just use the table for joining - and set the
query's Unique Values property to True.

How is the [Computer Info] table related? If you're only interested in
names and computer info, and if they are related tables, just leave
the Department and Department Assignments out of the query altogether;
if you want to see the department, then... which of the three
departments do you want to se???

John W. Vinson[MVP]

 

[John Vinson]

John,

Even with the unique value set to 'yes' I am still getting duplicate names
in the query. The query is suppose to show which employees use a dimension
computer, from which department, and that computer's ID. I get duplicate
entries for, say, John and Travis. Help! In the tables are

Please post the SQL of the query.

If you include the department in the query, and John is in Accounting,
Sales, and Datacenter, you *will* see three records - because that's
what you're asking for. John's in three departments; you see those
three departments. What do you want to see? An arbitrary department?
or is the computer related to the department rather than to the
employee?

John W. Vinson[MVP]

 

[bal_sagoth]

SELECT DISTINCT Employees.LastName, Departments.Department, [Computer
Information].[Computer ID]
FROM ([Computer Information] INNER JOIN (Departments INNER JOIN
DepartmentAssignments ON Departments.DepartmentID =
DepartmentAssignments.DepartmentID) ON [Computer Information].EmployeeID =
DepartmentAssignments.EmployeeID) INNER JOIN Employees ON [Computer
Information].EmployeeID = Employees.EmployeeID
WHERE ((([Computer Information].Model)="Dimension 4300c"));

There is the code for the query. I want to see just the person(s), their
department, and the ComputerID. If a person is in more than one department.
I just want him/her listed once with just the first department they are in.

thanx!

 

[John Vinson]

There is the code for the query. I want to see just the person(s), their
department, and the ComputerID. If a person is in more than one department.
I just want him/her listed once with just the first department they are in.

In a Table there IS NO "first". A table is an unordered heap of data.

The First TOTALS operator gets the first record in disk storage order,
but that order is basically arbitrary and uncontrollable - so it will
just pick one of the departments.

Try using a Totals query and use First for the department:

SELECT DISTINCT Employees.LastName, First(Departments.Department) AS
FirstOfDepartment, [Computer Information].[Computer ID]
FROM ([Computer Information] INNER JOIN (Departments INNER JOIN
DepartmentAssignments ON Departments.DepartmentID =
DepartmentAssignments.DepartmentID) ON [Computer
Information].EmployeeID = DepartmentAssignments.EmployeeID) INNER JOIN
Employees ON [Computer
Information].EmployeeID = Employees.EmployeeID
GROUP BY Employees.LastName, [Computer Information].[ComputerID]
WHERE ((([Computer Information].Model)="Dimension 4300c"));

Since it's just one arbitrary department, I wonder why it's needed in
the query at all, but that will do it for you.

John W. Vinson[MVP]

 

[bal_sagoth]

John,

Thank you for posting your query, however, when i go to save the query it
give me "a syntax errorin JOIN operation" and highlights a certain part of
the code. I am trying to learn as best I can off of your code example. If
you could help me out again I would greatly appericate it. Below is the code
you sent me with the syntax error code in "" .

SELECT DISTINCT Employees.LastName, First(Departments.Department) AS
FirstOfDepartment, [Computer Information].[Computer ID]
FROM ([Computer Information] INNER JOIN (Departments INNER JOIN
DepartmentAssignments ON Departments.DepartmentID =
DepartmentAssignments.DepartmentID) ON "[Computer
Information]".EmployeeID = DepartmentAssignments.EmployeeID) INNER JOIN
Employees ON [Computer
Information].EmployeeID = Employees.EmployeeID
GROUP BY Employees.LastName, [Computer Information].[ComputerID]
WHERE ((([Computer Information].Model)="Dimension 4300c"));

There is the code for the query. I want to see just the person(s), their
department, and the ComputerID. If a person is in more than one department.
I just want him/her listed once with just the first department they are in.

In a Table there IS NO "first". A table is an unordered heap of data.

The First TOTALS operator gets the first record in disk storage order,
but that order is basically arbitrary and uncontrollable - so it will
just pick one of the departments.

Try using a Totals query and use First for the department:

SELECT DISTINCT Employees.LastName, First(Departments.Department) AS
FirstOfDepartment, [Computer Information].[Computer ID]
FROM ([Computer Information] INNER JOIN (Departments INNER JOIN
DepartmentAssignments ON Departments.DepartmentID =
DepartmentAssignments.DepartmentID) ON [Computer
Information].EmployeeID = DepartmentAssignments.EmployeeID) INNER JOIN
Employees ON [Computer
Information].EmployeeID = Employees.EmployeeID
GROUP BY Employees.LastName, [Computer Information].[ComputerID]
WHERE ((([Computer Information].Model)="Dimension 4300c"));

Since it's just one arbitrary department, I wonder why it's needed in
the query at all, but that will do it for you.

John W. Vinson[MVP]

 

[John Vinson]

John,

Thank you for posting your query, however, when i go to save the query it
give me "a syntax errorin JOIN operation" and highlights a certain part of
the code. I am trying to learn as best I can off of your code example. If
you could help me out again I would greatly appericate it. Below is the code
you sent me with the syntax error code in "" .

SELECT DISTINCT Employees.LastName, First(Departments.Department) AS
FirstOfDepartment, [Computer Information].[Computer ID]
FROM ([Computer Information] INNER JOIN (Departments INNER JOIN
DepartmentAssignments ON Departments.DepartmentID =
DepartmentAssignments.DepartmentID) ON "[Computer
Information]".EmployeeID = DepartmentAssignments.EmployeeID) INNER JOIN
Employees ON [Computer
Information].EmployeeID = Employees.EmployeeID
GROUP BY Employees.LastName, [Computer Information].[ComputerID]
WHERE ((([Computer Information].Model)="Dimension 4300c"));

Is there in fact a table named

Computer Information

with a field named EmployeeID?


John W. Vinson[MVP]

 

[John Vinson]

SELECT DISTINCT Employees.LastName, First(Departments.Department) AS
FirstOfDepartment, [Computer Information].[Computer ID]
FROM ([Computer Information] INNER JOIN (Departments INNER JOIN
DepartmentAssignments ON Departments.DepartmentID =
DepartmentAssignments.DepartmentID) ON "[Computer
Information]".EmployeeID = DepartmentAssignments.EmployeeID) INNER JOIN
Employees ON [Computer
Information].EmployeeID = Employees.EmployeeID
GROUP BY Employees.LastName, [Computer Information].[ComputerID]
WHERE ((([Computer Information].Model)="Dimension 4300c"));

Or should it be [Computer Info] per your previouse postings?

John W. Vinson[MVP]

 

[bal_sagoth]

THe table name is Computer Information with the field name of EmployeeID

Below are the accurate table names and fields. I did not put the (s) in
some tables.
Computer Information:
computerID
type
date
model
employeeID

Departments:
Department
departmentID

Employees:
employeeID
First name
Last name
extension

DepartmentAssignments:
EmployeeID
DepartmentID

and 2 queries I have done so far:

(this one is list people from their departments)

SELECT DISTINCT Departments.Department, Employees.LastName
FROM Employees INNER JOIN (DepartmentAssignments INNER JOIN Departments ON
DepartmentAssignments.DepartmentID = Departments.DepartmentID) ON
Employees.EmployeeID = DepartmentAssignments.EmployeeID;


(this one is to list people with Optiplex computers)

SELECT Employees.LastName, Departments.Department, [Computer
Information].Model
FROM Employees INNER JOIN ([Computer Information] INNER JOIN (Departments
INNER JOIN DepartmentAssignments ON Departments.DepartmentID =
DepartmentAssignments.DepartmentID) ON [Computer Information].EmployeeID =
DepartmentAssignments.EmployeeID) ON Employees.EmployeeID =
DepartmentAssignments.EmployeeID
WHERE ((([Computer Information].Model)="Optiplex GX260d"));

John,

Thank you for posting your query, however, when i go to save the query it
give me "a syntax errorin JOIN operation" and highlights a certain part of
the code. I am trying to learn as best I can off of your code example. If
you could help me out again I would greatly appericate it. Below is the code
you sent me with the syntax error code in "" .

SELECT DISTINCT Employees.LastName, First(Departments.Department) AS
FirstOfDepartment, [Computer Information].[Computer ID]
FROM ([Computer Information] INNER JOIN (Departments INNER JOIN
DepartmentAssignments ON Departments.DepartmentID =
DepartmentAssignments.DepartmentID) ON "[Computer
Information]".EmployeeID = DepartmentAssignments.EmployeeID) INNER JOIN
Employees ON [Computer
Information].EmployeeID = Employees.EmployeeID
GROUP BY Employees.LastName, [Computer Information].[ComputerID]
WHERE ((([Computer Information].Model)="Dimension 4300c"));

Is there in fact a table named

Computer Information

with a field named EmployeeID?


John W. Vinson[MVP]