Need query results to include records with no match in joined table

[kattatonic1]

Hello,

I have two tables: tblPositions and tblEmployees.
The field pkPosition# is the unique key in tblPositions.
I use fkPosition# in tblEmployees to join the tables in queries.
Most positions have at least one employee, but some positions are vacant.
Most Employees have an fkPosition#, but some employees don't have a Position# yet. (Not yet assigned by another department.)


When I choose the option "Include ALL records from tblPositions and only those records from tblEmployees where the joined fields are equal", Access appears to only be returning the records that have matches. I have several scenarios where I require ALL the position data plus any employee data that goes with it, including positions that match to no employee data right now.

Any help would be appreciated!

Kay


from frozen Toronto

[orange]

Does your data model match your business rules/needs?
In general, you have Employee, Position and a junction table such as EmployeeOccupiesPosition.
Where EmployeeOccupiesPosition has either a compound PK EmployeeId + PositionID, or its own PK EmpOccPosID and a unique composite index on
EmployeeId + PositionID.

I suggest you review your table and relationship structure.

Good luck from Ottawa -20C

[kattatonic1]

Hey Orange in Ottawa,

I discovered my query criteria was not right. Was filtering out certain statuses and I had done it backwards!

However, your advice helped me fix another relationship that would have been an issue; so reviewing the relationships again at this point was helpful.

Many thanks!
Stay warm,

Kay

[orange]

Happy to help.
You have your power back !!!!

[kattatonic1]

Yes! Thank goodness. Four days without for us, 5 days for my poor ol' parents, 7 for my brother's house. Oh well. Now it's just another good Christmas story.