determining if a value is not present conditionally?

**Seth Schrock** · May 26 '15, 02:55 PM

You might try looking at the following thread: Show numbers not used. It has the same general idea of displaying records that aren't there. Basically, you can't return records that aren't there, so we used smoke and mirrors to create the records and the only displayed the ones that didn't match up to the real records.

**sooli** · Jun 5 '15, 12:21 PM

I am trying to use the unmatched query wizard and having no luck...

I have a table that is populated by my users when they complete a task, the field in particular I am looking to validate is shift,
the tables data is as follows:
Task, Date, TechID, shift, complete

i made a "required table" named shiftchecker it has the following fields:
Shift and Task

if 3rd shift misses a task, there will be no record (obviously)
so I want to know when this happens.

the instructions for the unmatched query are:
create a join from table where the unmatched data is coming from to the table that has the comparison data

then put the fields you want displayed in the query params
on the field you wish to compare, you are supposed to put "is null" and uncheck the show button.

When I do this, nothing comes up at all, and I know there are 2 records that should. what am I doing wrong?

my sql looks like this:

Code:

SELECT MonitoringCheck.Solution AS Task, MonitoringCheck.CDate AS [Date], MonitoringCheck.Shift
FROM MonitoringCheck LEFT JOIN shiftchecker ON MonitoringCheck.[Shift] = shiftchecker.[shift]
WHERE (((shiftchecker.shift) Is Null));

**Seth Schrock** · Jun 5 '15, 01:19 PM

I see two options. You could add a Completed checkbox field to your data table and then prepopulated the tasks for each shift. Each shift would then just check the box that they have completed the task. You could then run your query for past dates that don't have the completed field checked.

The other option utilizes the help in the link that I gave in my previous post. Create a table called tbl_expansiondi gits with one field called Digit. Populate the table with the numbers 0 through 9 (total of ten records). Now copy the following SQL into a new query:

Code:

SELECT CDate([tbl_expansiondigits_1]![Digit]
   +[tbl_expansiondigits_2]![Digit]*10
   +[tbl_expansiondigits_3]![Digit]*100
   +[tbl_expansiondigits_4]![Digit]*1000
   +[tbl_expansiondigits_5]![Digit]*10000) 
   AS Expand
   , Shift
   , Task
FROM tbl_expansiondigits AS tbl_expansiondigits_1
   , tbl_expansiondigits AS tbl_expansiondigits_2
   , tbl_expansiondigits AS tbl_expansiondigits_3
   , tbl_expansiondigits AS tbl_expansiondigits_4
   , tbl_expansiondigits As tbl_expansiondigits_5
   , Required
WHERE ((([tbl_expansiondigits_1]![Digit]
   +[tbl_expansiondigits_2]![Digit]*10
   +[tbl_expansiondigits_3]![Digit]*100
   +[tbl_expansiondigits_4]![Digit]*1000
   +[tbl_expansiondigits_5]![Digit]*10000) Between CLng(DateAdd("ww", -1, Date())) And CLng(Date()) ));

This will get you the dates of the past seven days combined with the tasks that should have been completed. You can then run your unmatched query against this. To change the date range that it pulls, you would need to change the last line where it has the DateAdd function.

**sooli** · Jun 5 '15, 02:09 PM

i am confused. this isn't working, getting the following error:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

I created the new query attached the new table and my existing table, then pasted the code above below the code that is auto populated. it looks like this:

Code:

SELECT 
FROM tbl_expansiondigits, MonitoringCheck;
SELECT CDate([tbl_expansiondigits_1]![Digit]
   +[tbl_expansiondigits_2]![Digit]*10
   +[tbl_expansiondigits_3]![Digit]*100
   +[tbl_expansiondigits_4]![Digit]*1000
   +[tbl_expansiondigits_5]![Digit]*10000) 
   AS Expand
   , Shift
   , Task
FROM tbl_expansiondigits AS tbl_expansiondigits_1
   , tbl_expansiondigits AS tbl_expansiondigits_2
   , tbl_expansiondigits AS tbl_expansiondigits_3
   , tbl_expansiondigits AS tbl_expansiondigits_4
   , tbl_expansiondigits As tbl_expansiondigits_5
   , Required
WHERE ((([tbl_expansiondigits_1]![Digit]
   +[tbl_expansiondigits_2]![Digit]*10
   +[tbl_expansiondigits_3]![Digit]*100
   +[tbl_expansiondigits_4]![Digit]*1000
   +[tbl_expansiondigits_5]![Digit]*10000) Between CLng(DateAdd("ww", -1, Date())) And CLng(Date()) ));

pretty sure the 2 select statements are the cause...

when i take the first 2 lines out i get the following error:

The Microsoft Access database engine cannot find the input table or query 'Required'. Make sure it exists and that its name is spelled correctly.

**Seth Schrock** · Jun 5 '15, 02:13 PM

If you have built the table as described in the second paragraph, then you shouldn't have to change the query I provided at all.

**sooli** · Jun 8 '15, 10:50 AM

Ok, redid with a clear head... and it sort of works. it pulled any records were the shift was left blank. but it still did not tell me which shifts were not entered at all

**Seth Schrock** · Jun 8 '15, 12:32 PM

You would have to post what you did in order for us to know what needs changed.

**sooli** · Jun 9 '15, 09:47 AM

Here is the unmatched query:

Code:

SELECT shiftchecker.shift, shiftchecker.task
FROM shiftchecker LEFT JOIN MonitoringCheck ON shiftchecker.[shift] = MonitoringCheck.[Shift]
WHERE (((MonitoringCheck.Shift) Is Null));

Tables:

shiftchecker

ID - Auto number
shift - Number
complete - short text (yes)
task - short text (not sure I need this...)

populated with:

1 1 Yes
2 2 Yes
3 3 Yes

tbl_expansiondi gits

Digit - number

populated with:

0
1
2
3
4
5
6
7
8
9

table with data in it that is where I am trying to determine what was missed:

monitoringcheck

ID - autonumber
SolutionID - Number
Solution - short text
CDate - Date/Time
CTime - Date/Time (working on removing this)
TechID - number
Technician - short text
Shift - number
MonitoringCompl ete - Yes/No
Incident - short text
Server - short text
Comments - short text
IncidentsWorked orAssigned - Number
LogFile - LongText
ProductionVerif ication - Yes/No
TestVerificatio n - Yes/No
MIPPatchingVeri fication - Yes/No
CAECoreCopied - Yes/No
MachineAddCreat ion - Yes/No
MachineDesubCre ation - Yes/No
MachineDesubRes olution - Yes/No
SEPHomeUse(NoNe w) - Yes/No
SEPHomeUse(Post ed) - Yes/No
SRCount - Number
Purpose - short text
Status - short text

This table is generated by several append table query's to pull all data into one table for viewing by the boss.

we have 3 shifts, and each shift should do each task once per shift.

currently there are 76 tasks, so as you can imagine, eyeballing this is painful...

**Seth Schrock** · Jun 9 '15, 02:48 PM

In post #5, you haven't selected any fields to return. That is why it failed. Also, you have a semi-colon at the end of the second line which needs to be removed. Also, you had mentioned in post #3 that you had made a "Required table". I represented that with the table name "Required". So I guess that you would need to change this in my query.

But now I'm confused as to what the shiftchecker table is compared to the the monitoringcheck .

**sooli** · Jun 12 '15, 12:25 PM

the shiftchecker table is just a list of the shifts, so i would have something to compare shifts with... must have shifts 1 2 and 3 for each entry. it is the equivalent to your "Required" table. I think actually it is the same concept as the expansion_digit s table, but I only need 3 shifts.

**Seth Schrock** · Jun 12 '15, 12:59 PM

Got it. It is actually my "Required" table. Replace the Required table in the query with "Shiftcheck er". Then replace the Shiftchecker in your Find Unmatched Query with the name that you gave the query that I gave you.