Currently I have a access report and the recordsource is the result of a query which joins on tableA and tableB. TableA is the main table and TableB is like a sub table.

TableA has the main details like the proj#, proj name, leadname, leadcomments, resourcename etc.
TableB has the sub details like the proj#, projname and the status for a particular week for that resourcename.

The join of these two table gives the result of status of a particular project for a particular resource for that particular week.



Sometimes TableA may contain a row with leadcomments alone and tableB may not have a row with the resourcename for that particular week. These records also should appear on the report.

Therefore I thought I would first get all the records from tableA, loop through, use the proj# from TableA to get records from TableB for a particular week.

The options I have is
1. Combine two recordset into one recordsource. I am not sure how to do this, is there a way to combine details from two records sets into one recordsource for the access report?
2. The other is to put all these details in a table. But this would impact the performance of the report since the table would have to be created at runtime of the report.

Please let me know if option 1 is feasible or if there are any other options
Thanks.