Cannot retrieve all records

[Peter]

We are developing an Property Management System and there
is a linked table between Property & Employee.

The linked tables is
PropertyEmployeeID - Autonumber
PropertyID - Linked to Property Table
OwnerID - Linked to Employee Table
ControllerID - Linked to Employee Table

The OwnerID and ControllerID field can be empty. Usually,
the OwnerID field has value but the ControllerID is
optional.

I have tried the following query but are not able to get
the Employee Name unless both OwnerID and Controller field
are not null.

SELECT Employees.[FirstName] & " " & Employees.[LastName]
AS Owner, Employees_1.[FirstName] & " " & Employees_1.
[LastName] AS Controller
FROM Employees AS Employees_1, Employees, [Property
Employee] WHERE ((([Property Employee].OwnerID)=
[Employees].[EmployeeID]) AND (([Property
Employee].MaintaqnceID)=[Employees_1].[EmployeeID]));


OR should I assign a default value to both OwnerID and
ControllerID so that they point to someone named "Not
Assigned" ?

Thank you for your help.

 

[Jeff Boyce]

Peter

If your junction/relation/resolver table is supposed to show valid "pairs"
of Property & Employee, why would it be valid to have a record in that table
that has no employee?

Or have I misunderstood?

Jeff Boyce
<Access MVP>

 

[Van T. Dinh]

Try:

****
SELECT PE.PropertyID,
EO.[FirstName] & " " & EO.[LastName] AS Owner,
EC.[FirstName] & " " & EC.[LastName] AS Controller

FROM
( [Property Employee] AS PE LEFT JOIN
[Employee] AS EO
ON PE.OwnerID = EO.EmployeeID ) LEFT JOIN
[Employee] AS EC
ON PE.MaintenanceID = EC.EmployeeID
****

Note: I am guessing some names as it looks like you have typing mistake(s)
in your SQL.

 

[Peter]

Dear Jeff,

Thank you for your reply. It is the first time I deal
with M:M relationship.

Since I believe that the relationship between Property and
Employee is M:M, I create the link table as mentioned
before. And I am not aware that a link table is not
supposed with any ID is empty AND I learn from it.

On the other hand, what should be the most suitable way
for me to solve this kind of problem ? Should I still use
a Link table ? Can I still regard this kind of
relationship as M:M between Employee and Property tables ?

Your advice is highly appreciated.

 

[Van T. Dinh]

Jeff

I am gussing that Peter set it up as a "_double_ M-to-M relationship", one
Employee acting as "Owner" and (another) Employee as "Controller".

Personally, I would set this up as _2_ One-to-Many realtionships from
[Employee] to [Property] without the use of [Property Employee]. Unless
Peter actually has double Many-to-Many relationship but then I would set up
a Field for RelationType with possible values Owner and Controller rather
than 2 Fields OwnerID and ControllerID.

 

[Peter]

Dear Van,

Thank you for your reply.

I have no idea of what _2_ M:M is but as mentioned in my
previous message, I just want to allow 1 Controller and 1
Owner for a property. And I believe the relationship
between Property and Employee tables are M:M. Am I
correct ?

Should I still use the Link Table ?

Your advice is highly appreciated.

-----Original Message-----
Jeff

I am gussing that Peter set it up as a "_double_ M-to-M relationship", one
Employee acting as "Owner" and (another) Employee as "Controller".

Personally, I would set this up as _2_ One-to-Many realtionships from
[Employee] to [Property] without the use of [Property Employee]. Unless
Peter actually has double Many-to-Many relationship but then I would set up
a Field for RelationType with possible values Owner and Controller rather
than 2 Fields OwnerID and ControllerID.

--
HTH
Van T. Dinh
MVP (Access)

Peter

If your junction/relation/resolver table is supposed to show valid "pairs"
of Property & Employee, why would it be valid to have a

record in that
table

that has no employee?

Or have I misunderstood?

Jeff Boyce
<Access MVP>

.

 

[Van T. Dinh]

As I posted previously in this thread, you have 2 One-to-Many relationships
from [Employee] to [Property].

No, you don't need the Link / Resolver Table. You only need 2 ForeignKey
Fields in the Table [Property], one pointing to the Employee acting as
"Owner" and one pointing to (possibly another) Employee acting as
"Controller". Both Fields can point to the same Employee (who will be
acting as both "Owner" and "Controller"), of course.

 

[Peter]

Dear Van,

Thank you for your advice and it seems that I can more
understanding of the table relationships.

However, I would like to know when will my case be a M:M
relationship ? When 1 property has more than 1 owner and
more than 1 controller ?

I have deleted the Link Table and add the linkage between
Employee and Property Tables.

When I creat a query, no record is selected, is there
anything wrong ?

SELECT Property.PropertyID, Property.EmployeeID,
Property.EmployeeIDM, Employees.FirstName,
Employees.LastName
FROM Employees INNER JOIN Property ON
(Employees.EmployeeID = Property.EmployeeIDM) AND
(Employees.EmployeeID = Property.EmployeeID);

Pleae note that EmployeeID is OwnerID and EmployeeIDM is
ControllerID in the Property Table.

Your advice is highly appreciated.

-----Original Message-----
As I posted previously in this thread, you have 2 One-to- Many relationships
from [Employee] to [Property].

No, you don't need the Link / Resolver Table. You only need 2 ForeignKey
Fields in the Table [Property], one pointing to the Employee acting as
"Owner" and one pointing to (possibly another) Employee acting as
"Controller". Both Fields can point to the same Employee (who will be
acting as both "Owner" and "Controller"), of course.

--
HTH
Van T. Dinh
MVP (Access)

Dear Van,

Thank you for your reply.

I have no idea of what _2_ M:M is but as mentioned in my
previous message, I just want to allow 1 Controller and 1
Owner for a property. And I believe the relationship
between Property and Employee tables are M:M. Am I
correct ?

Should I still use the Link Table ?

Your advice is highly appreciated.

.

 

[Peter]

Dear Van,

I have worked out as follow:

SELECT ASSETS.AssetID,
EO.[FirstName] & " " & EO.[LastName] AS Owner,
EC.[FirstName] & " " & EC.[LastName] AS Controller

FROM
( [Assets] LEFT JOIN
[EMPLOYEES] AS EO
ON ASSETS.EmployeeID = EO.EmployeeID ) LEFT JOIN
[EMPLOYEES] AS EC
ON ASSETS.EmployeeIDM = EC.EmployeeID

Thanks!

 

[Van T. Dinh]

Isn't that what I posted?

(except for the Table / Field names since I didn't know your actual Table /
Field names)

 

[Van T. Dinh]

See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)

Dear Van,

Thank you for your advice and it seems that I can more
understanding of the table relationships.

However, I would like to know when will my case be a M:M
relationship ?

When 1 property has more than 1 owner and more than 1 controller?
====> Yes

 

[Peter]

Yes. Thank you a lot for your help.

However, there is another requirement is to produce a
report showing the following information:

Employee Name
List of Properties he is the owner
List of Properties he is the controller

Like:

Stephen Chung
OWNS:
Main Building
Knowles Building
CONTROLS:
Chemical Building

Roger Pan
OWNS:
Chemical Building
CONTROLS:
Main Building

Bertha Lee
CONTROLS:
Knowles Building

Is it possible for me to group by the EmployeeID ? There
are two of them, one is OwnerID and the other is
ControllerID ?

Your advice is deeply appreciated.

-----Original Message-----
Isn't that what I posted?

(except for the Table / Field names since I didn't know your actual Table /
Field names)

--
HTH
Van T. Dinh
MVP (Access)

Dear Van,

I have worked out as follow:

SELECT ASSETS.AssetID,
EO.[FirstName] & " " & EO.[LastName] AS Owner,
EC.[FirstName] & " " & EC.[LastName] AS Controller

FROM
( [Assets] LEFT JOIN
[EMPLOYEES] AS EO
ON ASSETS.EmployeeID = EO.EmployeeID ) LEFT JOIN
[EMPLOYEES] AS EC
ON ASSETS.EmployeeIDM = EC.EmployeeID

Thanks!

.

 

[Van T. Dinh]

Something like:

****
SELECT E.[FirstName] & " " & E.[LastName] AS FullName,
"OWNER" AS Responsiblity,
A.AssetName
FROM [EMPLOYEES] AS E INNER JOIN
[ASSETS] AS A ON E.EmployeeID = A.EmployeeID

UNION

SELECT E.[FirstName] & " " & E.[LastName] AS FullName,
"CONTROLLER" AS Responsiblity,
A.AssetName
FROM [EMPLOYEES] AS E INNER JOIN
[ASSETS] AS A ON E.EmployeeID = A.EmployeeIDM
****

Use the above as the RecordSource for the Report and specify appropriate
Sorting / Grouping in the Report.

 

[Peter]

Dear Van,

Thank you for your advice.

Peter