Trying to query missing records (1 Viewer)

Hello all,

I am trying to build a query that shows all of our active employees (29) and to see who has an outstanding training session required.

I can do this by exporting the data into excel and utilizing pivot tables but my question would be how do I do this in access?

I can easily pull everyone who has received the training but I know that 3 people are missing the training.

All the training data for all employees are located in one main data table.

This is my SQL data
SELECT [Active Factory Employees].Active, tblTrainingRecordsMain.[Last Name], tblTrainingRecordsMain.[First Name], tblTrainingRecordsMain.[Course name], tblTrainingRecordsMain.[Start Date]
FROM [Active Factory Employees] INNER JOIN tblTrainingRecordsMain ON [Active Factory Employees].[Employee ID] = tblTrainingRecordsMain.EmployeeID
GROUP BY [Active Factory Employees].Active, tblTrainingRecordsMain.[Last Name], tblTrainingRecordsMain.[First Name], tblTrainingRecordsMain.[Course name], tblTrainingRecordsMain.[Start Date]
HAVING ((([Active Factory Employees].Active)=Yes) AND ((tblTrainingRecordsMain.[Course name])=[Enter Course Name]));

Not sure why you are getting name from the training record - it should be stored in the employees table. But to get what you require you use a left join - something like this


Note the use of code tags (the # button) to highlight code - please use them in the future, it makes for easier reading for those trying to help, particularly VBA.

Also recommended not to use spaces in field and table names

Unfortunately, I was not able to explain myself clearly as that wasn't the outcome I was looking for.

I will do that from now on about spaces and regards to # as well. I removed the join as well as it was unnecessary.

Actually, a better explanation of what I am trying to accomplish is a list of all active employees (that's the reason for the join) as the main table has all (past & present employees)

I'd like to see everyone who received training let's call it Heat Stress.
It displays both the individual that received the training (which I can show) and ones that did not. My issue is that if I leave that field as "Is Null" it doesn't return any values as that field is always populated.

I don't even know if this is possible any longer to be honest

My issue is that if I leave that field as "Is Null" it doesn't return any values as that field is always populated.

Click to expand...

you misunderstand how the query works

suggest you try my suggestion, it is the standard way of doing it. You should just be able to copy and paste into a new query.

However it may not work if your tables are not structured correctly and I've taken a guess with [Active Factory Employees].[Last Name].

If it doesn't work, show your table design and some example data

First and foremost thank you for your time, patience, effort and assistance with my knowledge of access and this forum.

I have uploaded images to imgur as I didn't know of a better way to show design and examples of data. Again just me not knowing.

'https://imgur.com/a/UUaUFEm' <-- I can't post links or pictures as my post count is too low

If it doesn't work I will continue to use my work around and utilize the pivot table no big deal just worth a shot. Though I would love to have this working

put them in a zipped file - that can be uploaded