Showing empty records in a query

[chrisd49]

Hi All
I have a feeling that this is an absolute newbie question.
I have three queries, qryVisitsDue, qryVisitsOverDue, qrySiteView.
The query qrySiteView uses SiteID to produce a listing of all sites which includes a count of visits for those sites. If there is no visit data the site does not display. To display the counts I'm using the other two queries. In the first two I'm querying a table, tblVisits, to find what visits are due or overdue based on the current date. They work fine where there is visit data. My problems start when there is no data for a site. I need to be able to show the sites where there is no visit data.
I'm not very experienced so basic explanations are probably the best.
Any help much appreciated.
ChrisD

 

[MarkK]

In the query design grid note that you can right click on a relationship between tables and alter the nature of the relationship. You can return all records from one table, and only matching records from a related table. This is an Outer, or in access, a left or right join. Note that if the related table contains no records that match the 'one' table, then all the fields from the related table evaluate to null.

 

[chrisd49]

Hi Lagbolt
I think I may not have explained the problem to well. I need to show all the records for SiteID in the query qrySiteView. Where there are no records in the two queries, qryVisitDue and qryVisitOverDue, it doesn't return an entry.
The query qrySiteView gets data from the table tblSites and the other two queries above. Any ideas most welcome.
ChrisD

 

[MarkK]

Chris:
You just said the same thing.
Did you right click on a relationship line in query design? There are two options, "Join Properties" and "Delete". Select Join Properties, and select the Radio Button that says "Include ALL records from 'tblSites' and only ...."
In a one-to-many relationship this is how you return ALL records in the "one" table even if no related records exist in the "many" table.
If that fails, or doesn't eventually lead to a solution, then you might consider posting the SQL text for your queries. That'll offer much more opportunity for people here to see the details of the problem.
Cheers,

 

[chrisd49]

Hi Lagbolt
Your absolutely right, I didn't read your reply thoroughly enough and it now all works well.
Thanks for your time.
Obviously in my case less speed more progress.
ChrisD

 

[MarkK]

Ah, my reply was cryptic at best. Good job hanging in there.
Cheers,