Find missing record for a given criteria (1 Viewer)

[CynthiaDel]

Hello. I need a query that will result in a list of records for a specific criteria. For example.....

I have a table called "TBL_2015DataTracker" with all my data that I enter on a daily basis using a form. The data to be entered on the form is an employee name, the specific document they are submitting (there are a total of 3 documents they can submit at any given time), and the month they are submitting it for.

I want a query where I can enter a specific parameter/critiera such as "September" and I want a list of all the employees who did NOT submit a specific document for the month of September.

For example.....

Cynthia Delgado - Safety Roster - September

This will tell me that Cynthia Delgado does NOT have a "Safety Roster" submitted for the month of "September".

 

[James Deckert]

Can you provide the table names and field names. I'm hoping you have two tables, one for the employee and one for the roster info)

 

[CynthiaDel]

Can you provide the table names and field names. I'm hoping you have two tables, one for the employee and one for the roster info)

Hi James. Yes, here the following tables with all my data.

TBL_2015DataTracker: This table has all the data entry records from the form, which I want the query to look at ultimately.

TBL_CrewMapping: This table has a list of all the employee names.

TBL_Documents: This table has a list of all the documents each employee can submit at any given time of the month.

TBL_Months: This table has a list of all the calendar months in a year.

 

[spikepl]

You should not have table names like TBL_2015 ... it's like having a Monday-wallet, Tuesday-wallet etc. Like data all goes into one container, you just have a column telling you which is which. Penalty for denormalized data like yours is just eternal PITA.

 

[CynthiaDel]

You should not have table names like TBL_2015 ... it's like having a Monday-wallet, Tuesday-wallet etc. Like data all goes into one container, you just have a column telling you which is which. Penalty for denormalized data like yours is just eternal PITA.

Okay not a problem, I can change that and identify the data by year by using a column. Thanks!

 

[James Deckert]

Now can you provide the field names for each table?

 

[CynthiaDel]

Table: DataTracker
Field Names: Document, Supervisor, Month

Table: CrewMapping
Field Names: Supervisor

Table: Documents
Field Names: Document

Table: Months
Field Names: Month

 

[James Deckert]

Where are your primary key fields?
Why does CrewMapping not have field(s) for employee name (re: #3)?

Here's a basic query that may help you see how to do it.

SELECT [EmpName]
FROM [CrewMapping] LEFT JOIN [DataTracker] ON [CrewMapping].[EmpNum] = [DataTracker].[EmpNum]
WHERE [DataTracker].[Month]<>[Enter Month]

 

[CynthiaDel]

Where are your primary key fields?
Why does CrewMapping not have field(s) for employee name (re: #3)?

Here's a basic query that may help you see how to do it.

SELECT [EmpName]
FROM [CrewMapping] LEFT JOIN [DataTracker] ON [CrewMapping].[EmpNum] = [DataTracker].[EmpNum]
WHERE [DataTracker].[Month]<>[Enter Month]

I don't have any Primary Keys except for "ID (Auto Number)" in the DataTracker table. On my data entry form, when I choose a specific Employee (Supervisor) from the drop down it will automatically add all the employee information. From that point I choose the document from a drop down menu as well as the month the document will need to be credit to.

The CrewMapping table does not have a numbering system. It only uses the Supervisor name as the primary field.

I used the code you gave me with a few tweaks and it actually does not work. When I enter the "month" as the parameter, it gives me ALL the months except for the month I entered as the parameter value.

 

[Minty]

The CrewMapping table does not have a numbering system. It only uses the Supervisor name as the primary field.
.

This is why you should use a unique ID number linked to that persons name.
What happens when you have John Doe the 2nd join your existing John Doe in the supervisor group. Or one of them leaves then another one joins. You wouldn't know which John Doe did the supervising.

 

[CynthiaDel]

This is why you should use a unique ID number linked to that persons name.
What happens when you have John Doe the 2nd join your existing John Doe in the supervisor group. Or one of them leaves then another one joins. You wouldn't know which John Doe did the supervising.

Okay that makes sense, thank you, I will actually incorporate their Personnel Numbers. Maybe that will help.