Query to Return Non Existant Combinations (1 Viewer)

[GrahamK]

Hi All,

I have a database of our company's mobile phone users. In it are several tables, one - tblEmployeeList a complete employee list with each employee's perpetual Project code and task code. a 2nd - tblJobList table with 2 columns for all the possible combinations of project code and task code, and a 3rd table - tblUser related to the Employee Listing of who the mobile phone users are. In this table is the opportunity to add an alternative project and task code for charging to a different budget.

Now, tblEmployeeList and tblJobList are updated weekly and the new data imported, which, if the user has not chosen an alternative budget, and uses their own one, that's fine - it'll be updated. However, if the user has previously chosen to select an alternative project and task code their combination could be illegal at a point in the future against the values given in tblJobList, and I need to run a query that will pull out these people.

I have tried the query:

SELECT tblEmployeeList.Name, tbluser.User, tblEmployeeList.[Home Job], tblEmployeeList.[Home Task]
FROM (tblEmployeeList INNER JOIN tbluser ON tblEmployeeList.[No] = tbluser.User) LEFT JOIN tblJobList ON (tblEmployeeList.[Home Task] = tblJobList.[Task Code]) AND (tblEmployeeList.[Home Job] = tblJobList.[No])
WHERE (((tblEmployeeList.[Home Job])<>[tblJobList].[No]) AND ((tblEmployeeList.[Home Task])<>[tblJobList].[Task Code]));

But when i hardcoded an illegal combination of project and task code into the fields in tbluser it was not pulled out, no rows are returned.

I hope someone can help.

Many Thanks
Graham

 

[namliam]

You are allready joining on the fields for anything valid combinations

LEFT JOIN  tblJobList ON (tblEmployeeList.[Home Task] = tblJobList.[Task Code]) 
                     AND (tblEmployeeList.[Home Job] = tblJobList.[No])

What you want to do
tblEmployeeList.[Home Job])<>[tblJobList].[No])
Is understandable but wrong.... The problem with this is that in a non matching situation the tblJoblist. No will be NULL and anything you with with NULL allways returns FALSE, unless you use "IS NULL" or a function like ISNULL()

To get what you want you have to do this:
WHERE tblJobList.[No] is Null
This will show you everything where NO match can be made to the tblJobList.

Good luck!

 

[GrahamK]

Hi,

Many Thanks For Your Suggestion, however the query still returned no results when I hard coded data. Please see the attached file for my test data. You will see that in tblUser I have hard coded an alternative task and budget combination that do not exist in tblJobList, so in the results of the Query I would expect to see my record from tblUser.

Many Thanks
Graham

 

[namliam]

This is because your query is the 'wrong way around'.
WHERE (((tblEmployeeList.[Home Job]) Is Null)) OR (((tblEmployeeList.[Home Task]) Is Null));

You want to retrieve every record where your tblEmployeeList. is Null, you will agree that your tblEmployeeList is NEVER Null because you are Inner joining it to user.
You have to 'is null' your 'left join'-ed table, in this case tblJobList.

Please if you post code or append it in a text file please please please dont just copy/paste it from the sql pane in the DB. Please please please format it, to make it more readable to the 'outside' persons on this forum, the SQL may be clear to you, but we have to read and interpet it, which is hard enough with formated SQL, but near impossible to do with unformated SQL.

Something like so: (including the solution to your -current- problem)

SELECT     tblEmployeeList.Name
,          tbluser.User
,          tblEmployeeList.[Home Job]
,          tblEmployeeList.[Home Task]
FROM (     tblEmployeeList  
INNER JOIN tbluser          ON tblEmployeeList.[No]         = tbluser.User)  
LEFT JOIN  tblJobList       ON (tblEmployeeList.[Home Job]  = tblJobList.[No]) 
                           AND (tblEmployeeList.[Home Task] = tblJobList.[Task Code])
WHERE      tblEmployeeList.[Home Job]  Is Null 
   OR      tblEmployeeList.[Home Task] Is Null
;

Also you really shoudnt use space or special characters (_*&^%$# etc) in any names anywhere anytime. This will cause you problems some time in the future and you will wish you had not used them.

 

[GrahamK]

Hi Mailman.

Points noted about formatting of SQL and use of special characters. I copied the code above and it's no different to what I had in the notepad document, that I could see anyway, and returned no results, not picking out the one result I am expecting.

Many Thanks
Graham

 

[namliam]

Sorry, i only reformatted but not fixed it...

Tho the change from the Inner join table to the Left join table should not be that hard...

Here it is:

SELECT     tblEmployeeList.Name
,          tbluser.User
,          tblEmployeeList.[Home Job]
,          tblEmployeeList.[Home Task]
FROM (     tblEmployeeList  
INNER JOIN tbluser          ON tblEmployeeList.[No]         = tbluser.User)  
LEFT JOIN  tblJobList       ON (tblEmployeeList.[Home Job]  = tblJobList.[No]) 
                           AND (tblEmployeeList.[Home Task] = tblJobList.[Task Code])
WHERE      tblJobList.[No]  Is Null 
;

 

[GrahamK]

Hi Mailman,

Thanks for that - it's great in that it pulled a record from the Phone Users with a project and task code combiniation that does not exist on their employee listing, but I'd like it to also look at AltBudget and AltTask in tblUser to see that if they are not null (ie user has selected an alternative budget) are the two a valid combination in tblJobList... I've just tried to mimic the query above and Access threw a fit! Saying it had ambiguous joins... Any Assistance appreciated!

SELECT tblEmployeeList.Name, 
           tbluser.User, 
           tblEmployeeList.[Home Job], 
           tblEmployeeList.[Home Task], 
           tbluser.AltBudget, tbluser.AltTask
FROM (tblEmployeeList INNER JOIN tbluser ON tblEmployeeList.[No] = tbluser.User) 
LEFT JOIN tblJobList ON (tbluser.AltTask = tblJobList.[Task Code]) 
   AND (tbluser.AltBudget = tblJobList.[No]) 
   AND (tblEmployeeList.[Home Task] = tblJobList.[Task Code]) 
   AND (tblEmployeeList.[Home Job] = tblJobList.[No])
WHERE (((tblJobList.[No]) Is Null) 
   AND ((tblJobList.[Task Code]) Is Null));

 

[namliam]

After reformating your SQL...

FROM (       tblEmployeeList  
INNER JOIN   tbluser         ON  tblEmployeeList.[No] = tbluser.User) 
LEFT JOIN    tblJobList      ON (tbluser.AltTask             = tblJobList.[Task Code]) 
                            AND (tbluser.AltBudget           = tblJobList.[No]) 
                            AND (tblEmployeeList.[Home Task] = tblJobList.[Task Code]) 
                            AND (tblEmployeeList.[Home Job]  = tblJobList.[No])
WHERE (((tblJobList.[No])        Is Null) 
   AND ((tblJobList.[Task Code]) Is Null));

You cannot do this.... Or atleast not do this and expect a proper answer. You want access to pull tblJobList.TaskCode and make that equal to two seperate fields. This cannot be done.
You have to split up the join to two tables.

FROM (       tblEmployeeList  
INNER JOIN   tbluser           ON  tblEmployeeList.[No] = tbluser.User) 
LEFT JOIN    tblJobList as JL1 ON (tbluser.AltTask             = JL1.[Task Code]) 
                              AND (tbluser.AltBudget           = JL1.[No]) 
LEFT JOIN    tblJobList as JL2 ON (tblEmployeeList.[Home Task] = JL2.[Task Code]) 
                              AND (tblEmployeeList.[Home Job]  = JL2.[No])
WHERE  JL1.[No] Is Null
   OR  JL2.[No] IS NULL
;

Note1
Only one of the join columns need be checked for Null values, if one is Null, the other will be null as well.

Note2
If your originating columns/table can be empty/Null as well (i.e. no need to always fill in an AtlTask) you will allways find a Null value in the related table as well.
But you probably dont want to see these "empty" records. If so... change your where clause:

WHERE  ( JL1.[No] Is Null and tbluser.AltTask is Not null )
   OR  JL2.[No] IS NULL

 

[GrahamK]

Many Thanks MailMan,

Access QBE is throwing a missing operator error in the part

 (tbluser.AltTask  = JL1.[Task Code]) 
                                    AND (tbluser.AltBudget = JL1.[No]) 
LEFT JOIN tblJobList AS JL2 ON (tblEmployeeList.[Home Task] = JL2.[Task Code])                           
                                   AND (tblEmployeeList.[Home Job]  = JL2.[No])

I've had a look a couple of times and compared it to others quiries in my projects but I can't obviously see any error. Have you any suggestions?

Many THanks
Graham

 

[namliam]

What is the error it is throwing?

I think the basic SQL is correct....

 

[GrahamK]

It's throwing just a generic "Syntax Error, Missing Operator" when I try and switch from SQL view to datasheet view.

 

[namliam]

I recreated your tables (I think) in access.... This is the SQL it created:

SELECT [tblEmployeeList].[No], 
       [tblEmployeeList].[Home Task], 
       [tblEmployeeList].[Home Job], 
       tblUser.AltTask, 
       tblUser.AltBudget
FROM (tblEmployeeList 
LEFT JOIN   tblJobList AS JL1 ON ([tblEmployeeList].[Home Task]=JL1.[Task code]) 
                           AND ([tblEmployeeList].[Home Job]=JL1.[No])) 
INNER JOIN (tblUser 
LEFT JOIN   tblJobList AS JL2 ON (tblUser.AltTask=JL2.[Task code]) 
                             AND (tblUser.AltBudget=JL2.[No])) 
                              ON [tblEmployeeList].[No]=tblUser.User
WHERE       JL2.[Task code] Is Null 
   OR       JL1.[Task code] Is Null;

It looks a bit wierd to me... but it seems to work....

 

[GrahamK]

Hi, THat's Great! THankyou. I appended on the end of it the where clause as you stated in your "note 2". The output from the query was all the records in the tbluser table - at a guess becaue all the altbudget and altTasks were null, except mine which I would expect to see, and the one record from tblEmployee that it pulled out this morning individually would have had a null AltBudget and AltTask too, so probably again masked by the showing of the nulls... Is that right??

 

[namliam]

No... If you applied the where properly the null values should be allowed...

I am just goint to be lazy now:

SELECT tblEmployeeList.[No], tblEmployeeList.[Home Task], tblEmployeeList.[Home Job], tblUser.AltTask, tblUser.AltBudget
FROM (tblEmployeeList LEFT JOIN tblJobList AS JL1 ON (tblEmployeeList.[Home Job] = JL1.[No]) AND (tblEmployeeList.[Home Task] = JL1.[Task code])) INNER JOIN (tblUser LEFT JOIN tblJobList AS JL2 ON (tblUser.AltBudget = JL2.[No]) AND (tblUser.AltTask = JL2.[Task code])) ON tblEmployeeList.[No] = tblUser.User
WHERE (((tblUser.AltTask) Is Not Null) AND ((JL2.[Task code]) Is Null)) OR (((JL1.[Task code]) Is Null));

Is that full SQL... Which works

BUT because you have an Inner Join from Employee to User, there does have to be a user record present in the user table. Otherwize NO record will ever show... Or.. you have to Left join that table as well.
For which This is the full SQL: (again LAZY! you format it to see how it works )

SELECT tblEmployeeList.[No], tblEmployeeList.[Home Task], tblEmployeeList.[Home Job], tblUser.AltTask, tblUser.AltBudget
FROM (tblEmployeeList LEFT JOIN tblJobList AS JL1 ON (tblEmployeeList.[Home Job] = JL1.[No]) AND (tblEmployeeList.[Home Task] = JL1.[Task code])) LEFT JOIN (tblUser LEFT JOIN tblJobList AS JL2 ON (tblUser.AltBudget = JL2.[No]) AND (tblUser.AltTask = JL2.[Task code])) ON tblEmployeeList.[No] = tblUser.User
WHERE (((tblUser.AltTask) Is Not Null) AND ((JL2.[Task code]) Is Null)) OR (((JL1.[Task code]) Is Null));

 

[GrahamK]

Thank you!

 

[namliam]

So what was the problem?
Did you not do the where properly OR did you have no record in the user table?

AND AND AND... Do you understand whats goings on?? I mean could you do this yourself next time?