Chapter 2: How do we get multiple blank records appended ... a z-saga

[zmbd]

Chapter 2 of the Z’s Saga or:
The Curious Difference Between what the Union Query Returns and what the Report Prints
Prolog:
This is where I started as there was what I thought a good solution for a similar problem: 946504-adding-empty-records
Chapter One of the Saga – in which the original solution is reworked.
(Very quickly, we’re trying to fill a page with a number of records in a multiple of 3 by appending enough blank records to the report to make three records per page so as not to waste the labels on the page).

Chapter 2:
So let’s start with the final solution using Rabbit’s “Range” method given in the prior thread instead of the “Between” we started out with resulting in the following SQL in my Database (we’ll just move things over to my field names at this point as I’m starting to get confused and it’s certainly much easier for me to keep track of things if I’m using my own thoughts (?) ):
(Notice here that MSAccess has used the Dot instead of the Bang (!?) between the table name and the related field name - curious. Other than stepping the SQL, I’ve cut and pasted directly from the query):

SELECT 
   tbl_printque.printque_pk, 
   tbl_printque.printque_fk_inventory, 
   tbl_printque.printque_printdate, 
   tbl_Inventory.Inventory_ItemTrackingID, 
   tbl_Inventory.Inventory_Serial, 
   z_product_text, 
   tbl_printque.printque_reportname
FROM [qry_for_rpt_newinventorytag]
UNION ALL SELECT [blankrow_pk] 
     , Null 
     , Null 
     , Null 
     , Null
     , Null
     , Null 
FROM   [tbl_blankrow] 
WHERE  (([blankrow_pk]>=1) 
   AND ([blankrow_pk]<=
      (2- ((
         (SELECT Count(*) 
          FROM [qry_for_rpt_newinventorytag]) - 1) 
      mod 3))));

(keep track of that “UNION ALL” ) [z_product_text] is a calculated field in the query [qry_for_rpt_newinventorytag] provided here for reference:

 SELECT 
   tbl_printque.printque_pk, 
   tbl_printque.printque_fk_inventory, 
   tbl_printque.printque_printdate, 
   tbl_Inventory.Inventory_ItemTrackingID,, 
   tbl_Inventory.Inventory_Serial, 
   [tbl_products]![product_model] & "_" & [tbl_products]![product_name] 
       AS z_product_text, 
   tbl_printque.printque_reportname
FROM tbl_Products
   INNER JOIN (tbl_printque 
      INNER JOIN tbl_Inventory 
           ON tbl_printque.printque_fk_inventory = tbl_Inventory.Inventory_pk) 
      ON tbl_Products.Product_PK = tbl_Inventory.Inventory_FK_Products
WHERE (((tbl_printque.printque_printdate) Is Null) 
    AND ((tbl_printque.printque_reportname)="rpt_newinventorytag")); 

So… the Union Query in the first code block returns exactly the correct number of records from the blank table to make the record set count a multiple of three – as expected.
However, when used as the record source for the report, there are appended three empty records. To clarify, if there is one Inventory Item pending report, when the UQ is ran there are only three records, if there are seven items, then the query returns nine records (7+2blank), and so forth. The report however returns the number of items plus three blank records (at least it append the entire table of 25 blanks! :-) )
Going back to that first code block, changing “UNION ALL” to simply “UNION” returns the expected number of records both within the query and for the report. If the query has 2 assets it appends 1 blank and the report only has 2 assets and 1 blank… instead of 2 assets and 3 blanks as in the case with the “ALL”.
So what is up with that? I thought I understood how the UNION/UNION-ALL clauses worked, and yet I just cannot seem to make sense of this… there are no duplicated values and so forth… and why would the Query run and return the expected results using either method and yet the report based on this very same query will only return the expected result in only one case.

[Rabbit]

If there's no duplication in the data, then I would expect the union and union all to return the same results.

I don't, however, understand why it would act differently when used in a report. Can you attach the database?

[zmbd]

As far as the query goes, the result set is the same.
Just when it goes to the report.
I'll see what I can do about posting the database. They took my zip program with the last round of security updates. sigh.
One thing to note is that the database is in v2010.

[Rabbit]

Oh, I have 2007. If you are unable to attach it, I'll see if I can find some time to mock it up.