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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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