• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

How to find all records

I have a query "qryUSAACLaims" that I am using as a record source for a report. I am trying to figure out how to use that output and include it in another record source for the report.

The table I am trying to include is called "tbldocuments" records are created in that table when the user creates a report ime_doctor  or RecordsReview.  The issue I am having is I have to use that table to determine another unbound field on the report called Duedate.  

THe issue is , that certain rows from QryUSAACLaims do not show up on the report because a report has not been created yet. I am trying to figure out how to include those records on the report. I have included the sample database which does not include any sensitive data.  

The record source I need help with is the query Statusreport.
1 Solution
You can't add any criteria to left-joined table/query. Add separate query with this criteria and use it in your query. Look at StatusReport1 query
Dale FyeCommented:
To expand on als's explanation,  when you use a left (or right) join,

Select a.*, b.*
From a left join b on a.[id]= b.[a_id]

what you are doing is telling Access that you want all records from A, ond only those records from B which match A on the selected field.  You can add a criteria to that query which restricts the recordset based on any field in table A

Where A.somefield = 3

And the result will still include those records in table A which don't have a match in table B.  However, if you add a criteria to restrict the records from table B,

Where B.someotherfield = "some text value"

This will remove all of the records from the result set which don't fulfill that criteria, including the nulls.  If you want all the records from table A, and only those from table B which meet a particular criteria, you either need to create and save a separate query to limit the records from table B, or you can create a sub query, and join table A to the subQuery.

Select A.*, Bsub.*
From A left join
(Select B.* from B where B.someotherfield ="some text value") as Bsub
OnA.id = Bsub.a_id
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now