By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,256 Members | 1,700 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,256 IT Pros & Developers. It's quick & easy.

determining if a value is not present conditionally?

P: 49
I am trying to create a query that will tell us if a shift forgot to log info...

We have 3 shifts that enter verification of tasks which happen to be the same on each shift. We have some forgetful folks who can't seem to remember to log their tasks as they complete them, so I have to go back thru and figure out which shift forgot to input their task as complete.

so the table records the date, user name, shift, task name and completion status.

I am sure this is simple... and I am just missing it... but simple is often my hardest task!

I have tried to query using the task and then shift, not 1 or not 2 or not 3

but it doesn't work, still brings up every record... i only want it to pull in the task where a shift failed to enter data, and tell me which shift was missing

even tried doing an iif statement... iif(shift not like "1", "missed 1", iif(shift not like "2", "missed 2", iif(shift not like "3", "missed 3"))) and that didn't work either.


Any ideas?
May 26 '15 #1
Share this Question
Share on Google+
11 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
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.
May 26 '15 #2

P: 49
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT MonitoringCheck.Solution AS Task, MonitoringCheck.CDate AS [Date], MonitoringCheck.Shift
  3. FROM MonitoringCheck LEFT JOIN shiftchecker ON MonitoringCheck.[Shift] = shiftchecker.[shift]
  4. WHERE (((shiftchecker.shift) Is Null));
  5.  
Jun 5 '15 #3

Seth Schrock
Expert 2.5K+
P: 2,951
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_expansiondigits 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT CDate([tbl_expansiondigits_1]![Digit]
  2.    +[tbl_expansiondigits_2]![Digit]*10
  3.    +[tbl_expansiondigits_3]![Digit]*100
  4.    +[tbl_expansiondigits_4]![Digit]*1000
  5.    +[tbl_expansiondigits_5]![Digit]*10000) 
  6.    AS Expand
  7.    , Shift
  8.    , Task
  9. FROM tbl_expansiondigits AS tbl_expansiondigits_1
  10.    , tbl_expansiondigits AS tbl_expansiondigits_2
  11.    , tbl_expansiondigits AS tbl_expansiondigits_3
  12.    , tbl_expansiondigits AS tbl_expansiondigits_4
  13.    , tbl_expansiondigits As tbl_expansiondigits_5
  14.    , Required
  15. WHERE ((([tbl_expansiondigits_1]![Digit]
  16.    +[tbl_expansiondigits_2]![Digit]*10
  17.    +[tbl_expansiondigits_3]![Digit]*100
  18.    +[tbl_expansiondigits_4]![Digit]*1000
  19.    +[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.
Jun 5 '15 #4

P: 49
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. FROM tbl_expansiondigits, MonitoringCheck;
  3. SELECT CDate([tbl_expansiondigits_1]![Digit]
  4.    +[tbl_expansiondigits_2]![Digit]*10
  5.    +[tbl_expansiondigits_3]![Digit]*100
  6.    +[tbl_expansiondigits_4]![Digit]*1000
  7.    +[tbl_expansiondigits_5]![Digit]*10000) 
  8.    AS Expand
  9.    , Shift
  10.    , Task
  11. FROM tbl_expansiondigits AS tbl_expansiondigits_1
  12.    , tbl_expansiondigits AS tbl_expansiondigits_2
  13.    , tbl_expansiondigits AS tbl_expansiondigits_3
  14.    , tbl_expansiondigits AS tbl_expansiondigits_4
  15.    , tbl_expansiondigits As tbl_expansiondigits_5
  16.    , Required
  17. WHERE ((([tbl_expansiondigits_1]![Digit]
  18.    +[tbl_expansiondigits_2]![Digit]*10
  19.    +[tbl_expansiondigits_3]![Digit]*100
  20.    +[tbl_expansiondigits_4]![Digit]*1000
  21.    +[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.
Jun 5 '15 #5

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Jun 5 '15 #6

P: 49
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
Jun 8 '15 #7

Seth Schrock
Expert 2.5K+
P: 2,951
You would have to post what you did in order for us to know what needs changed.
Jun 8 '15 #8

P: 49
Here is the unmatched query:

Expand|Select|Wrap|Line Numbers
  1. SELECT shiftchecker.shift, shiftchecker.task
  2. FROM shiftchecker LEFT JOIN MonitoringCheck ON shiftchecker.[shift] = MonitoringCheck.[Shift]
  3. WHERE (((MonitoringCheck.Shift) Is Null));
  4.  
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_expansiondigits
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
MonitoringComplete - Yes/No
Incident - short text
Server - short text
Comments - short text
IncidentsWorkedorAssigned - Number
LogFile - LongText
ProductionVerification - Yes/No
TestVerification - Yes/No
MIPPatchingVerification - Yes/No
CAECoreCopied - Yes/No
MachineAddCreation - Yes/No
MachineDesubCreation - Yes/No
MachineDesubResolution - Yes/No
SEPHomeUse(NoNew) - Yes/No
SEPHomeUse(Posted) - 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...
Jun 9 '15 #9

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Jun 9 '15 #10

P: 49
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_digits table, but I only need 3 shifts.
Jun 12 '15 #11

Seth Schrock
Expert 2.5K+
P: 2,951
Got it. It is actually my "Required" table. Replace the Required table in the query with "Shiftchecker". Then replace the Shiftchecker in your Find Unmatched Query with the name that you gave the query that I gave you.
Jun 12 '15 #12

Post your reply

Sign in to post your reply or Sign up for a free account.