foreign key null in query access (1 Viewer)

[nhorton79]

Hi Everyone,

So I have an issue.

For some reason I cannot get this to work. :banghead:

I am trying to design a query to populate a search form that lists all jobs in the system.

This will show the JobID (from tblJob) and the contact name (from tblContact), plus a number of other fields (which don't matter at the moment)...

I cannot get the query to show ALL jobs, it will only show jobs that have a contact assigned.

I have tried using Nz(JobContact_FK,0) to replace any NULL values with 0, but this doesn't work either, is still only shows the 2 jobs that have contacts assigned, but there are another 2 test jobs with no contact.

This has worked for me with other queries where it wasn't an FK, so wondering whether that is the issue.

See below the attached pic of my query design so far ( I haven't added the other fields as wanted to get this part working first).

 

[CJ_London]

you need to use a left join between clients and jobs and another between jobs and contacts and remove the join between clients and contacts

to do a left join, double click on the join line and (for clients>jobs) select the option 'include all records from client and only those records from jobs where the joind fields are equal'

 

[nhorton79]

Hi CJ,

You sir are a bloody legend!

You weren't quite there with the join method, it worked by saying "Include all records from tblJob and only those records from tblContact", but the premise was there.

I had forgotten about double-clicking on the relationship and setting other types of joins.....

Thank you, thank you, thank you!!!!

Cheers
Nick

 

[CJ_London]

I was assuming you needed two left joins -in case you had clients but no jobs - and just quoted the first one

 

[nhorton79]

No just needed a list of all jobs.

Every job must have a client but not necessarily a contact.

Cheers again for your help


Sent from my iPhone using Tapatalk

 

[nhorton79]

Btw it is now working


Sent from my iPhone using Tapatalk