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.
DMC-Backup-Backup.accdb
seamus9909Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
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
DMC-Backup-Backup.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.