Query to show blank & data records, without duplicating the two

Anyone's help with this will be much appreciated:

Table1 has one-to-many relationship with Table2

I want a query to show those records from Table1 for whom nothing exists yet in Table2, or if anything did, it is not current any more (I have an "Active" yes/no field for this under Table2).
I also want to show all those records from Table1 which have records in Table2 and I also want these Table2 records listed.

Objective:
I need to have a report where Table1 data is there, might even be showing up in duplicates (or even more) depending on how many related records it might have in Table2, and Table2 data is to be represented in the same row.
But I want this report to also show all those records for whom absolutely no data exists under Table2, or if it did, it's expired; this means a part of the row will be blank where there is nothing from Table2 to populate it, and thats ok.

I cant have a duplication; meaning, if a record from Table1 has 10 in Table2, I am ok with seeing ten rows in the report, but I do not want it show me blank ones for this record.
I want only blank ones to show for those Table1 records, for whom no data ever existed under Table2, and even if it did, it is no longer current.

This is what I have done so far but it takes over 25 minutes to generate:
Query1: In one field I have done a complicated formula which counts Table2 records for each record in Table1, and represent them (CS: DCount("[lastname]","[qrsfp_childrenservedtoshowtotacctng]","[qrsfp_childrenservedtoshowtotacctng].[facility#]=" & [tblFosterFamily].[facility#])
In this field I created criteria; ">0"...now this query lists all records for which there are records under Table2 and lists the Table2 records also.

Query2: I have made the same thing, difference is, in criteria for that DCount field, I have specified; "<1" ...now this query indeed lists all records for which there are NO records under Table2.

UnionQuery: combines them both and gives me what I need. It is the end result of approximately 4 queries. However, in a shared WAN network environment, it takes 25 minutes to generate?!?!?. On my copy, it takes approximately 11 minutes.

There has to be a better solution, I have tried many ways including tricking a UnionQuery but it not only lists the first set of records but it also duplicates them and shows them again as if they never had any records in Table2. What could possibly be a better solution?

Thanks in advance for your help...

Regards, Martin

Counting should be done in the report. The problem is the DCount() functions. They run for EACH row of the table - so if you have 100 rows in tblA, the DCount() runs 100 queries. If you have 10,000 rows, 10,000 SEPARATE queries are run. Whereas, counting in a report requires only a single, sequential read through the joined recordset.

Change your join in the report's RecordSource query to be a left-join. That will return all rows from tableA regardless of whether they have a match in tableB in addition to returning all rows where there are matches. You can set the hide duplicates property to suppress what looks like repetitive data from tabldA as it prints for each record from tableB.

Thank you Pat!

I got the data I needed successfully by making Query1 for Table1 filtered records and Query2 for Table2 filtered records.
I then made Query3 in which I did the 1-to-many relationship between Query1 and Query 2. This worked.

From your statement this is what I understood from “left-join” and “hide duplicates property” verbiage, please tell me if I am correct:

“left-join”; in the one-to-many relationships settings, I chose the option to show all records from Query1 and only those from Query2 where the joining fields are equal.

“hide duplicates property”; in Query3’s property, change the settings for either “Unique Values” or “Unique Records” sections.

Am I correct in my understanding of your terminology, please advise.

Regards, Martin…and Happy Holidays to you!

The hide duplicates is done in the report. It cannot be done in the query. If you are selecting rows from the many-side table, the data from the one-side table will appear for each many-side row. For example, subject grades for a student would look like:
Carol,Math,B
Carol,Reading,A
Dan, Algegra,C
Dan, Biology, B
Dan, Reading, B
John, Algebra, A
John, Reading, A
John, Zoology, A

Notice how the student name repeats for each row. Student is the one-side table and grades is the many-side table in this example. In the report you want to hide the name repeats.

Thank you Pat!

I appreciate your example and thorough explanation. This has been a great help!

Regards,
Martin