I must be thick or something?

[Sam Summers]

Hi,

I really just don't get queries at all? Just when i think i do, i get caught out again?????

I have this:
______________________________________________________

SELECT Employee.EmployeeID, Employee.FirstName, Employee.Surname, Employee.National, Employee.Contract, Employee.RopeAccess, Employee.Level, Employee.MobDate, Employee.DeMobDate, EmpJob.JobID, Job.JobName

FROM Employee

INNER JOIN (Job INNER JOIN EmpJob ON Job.JobID = EmpJob.JobID) ON Employee.EmployeeID = EmpJob.EmployeeID

ORDER BY Employee.FirstName;
_______________________________________________________

But i want to also display the personnel who dont have either:

National or Contract or RopeAccess or Level or MobDate or DeMobDate or JobID or JobName

Everything else they always have but the others they may or may not have any combination of?

I have tried "" and Is Null etc but am completely stumped?

Many many thanks in advance if you can point me in the right direction?

 

[namliam]

Something along the lines of this??

SELECT Employee.EmployeeID
     , Employee.FirstName
     , Employee.Surname
     , Employee.National
     , Employee.Contract
     , Employee.RopeAccess
     , Employee.Level
     , Employee.MobDate
     , Employee.DeMobDate
     , EmpJob.JobID
     , Job.JobName

FROM        Employee 
INNER JOIN (Job 
INNER JOIN  EmpJob     ON Job.JobID = EmpJob.JobID
           )           ON Employee.EmployeeID = EmpJob.EmployeeID
Where NZ(Employee.National, "") = ""
   OR NZ(Employee.Contract, "") = ""
ORDER BY Employee.FirstName;

 

[Sam Summers]

Thanks for your reply.

I tried that but got a totally blank result?

Basically, they will always have EmployeeID, FirstName and Surname but all the rest are optional and they may have all or any combination or none of them at all?

 

[namliam]

Well if you dont care about the results at all, why put a where clause on it at all?

If you can try posting your database with a few records, probably something going on with the joins...

Try changing one, the other or both to LEFT JOIN, if that dont work, RIGHT JOIN...

 

[Sam Summers]

Thank you,

Looking at it again after you said makes me think its a join error but i cant seem to figure it out?

The records are displayed in a form that shows all the fields even if they are empty?

Here is a cut down version

 

[CazB]

try using this as your FROM statement

 FROM Employee LEFT JOIN (Job RIGHT JOIN EmpJob ON Job.JobID = EmpJob.JobID) ON Employee.EmployeeID = EmpJob.EmployeeID

Is that what you meant?

 

[The_Doc_Man]

INNER JOIN always gives you records with something in the JOINed fields. But if any of them are optional, you cannot use INNER JOIN. You must use one or more outer JOINs, which in Access are brought about by LEFT or RIGHT JOIN as the case may be.

Basically, they will always have EmployeeID, FirstName and Surname but all the rest are optional and they may have all or any combination or none of them at all?

If, for example, Employee.JobID is optional, then an INNER JOIN that involves Job ID cannot return a record for that employee who has no job ID. When you use INNER JOIN you are telling Access Jet (or any other SQL engine) to only return those records that match the given criteria. But... if the employee does not have a job ID then it will be either blank or null, and that will not work for INNER JOIN. Null, if you have it, will NEVER match anything. There is the hidden implication that you are filtering out anything that does not match your selection criteria including partial records - because of that INNER JOIN.

I would have built this in two stages using a query grid where you can build an implied relationship between corresponding fields in the two tables that are your source. Then, I would reset the properties by right-clicking on that relationship line and making it the "All employees and any matching records from EmpJob" (or something like that) and then take THAT query into another query to build the second part. Again, edit the relationship to the "All x and any matching y" case. If you wish, you could then switch to SQL view to see how Access would have done it.

When you successfully build this query, you have one more issue. For those cases where the employee does not have a matching entry in one of the secondary tables, you WILL get records - but the returned fields will all be null. When you use LEFT/RIGHT JOIN, learn the NZ function quickly. It will become your best friend.

 

[Sam Summers]

Wow! Thank you all!

I would have taken a very long time to work all that out!

I will try and sort it out and reply here.