How do we get multiple blank records appended to a record set in multiples of a given

[zmbd]

A Z Saga:

You'd think that something like this would be so easily available on the net - and yet, it's not.
Do a search here on bytes and this same topic turns up over and over; however, with some surprisingly different solutions.
In one, VBA is used to draw the lines in based on the record count.

SO what Prompted my little walk thru fire today: A "problem" popped up where in we used to have 25 rows on a pre-printed that used to have the "template" grid and text pre-printed in the details section is now blank - cheaper I'm sure. However, without these extra rows, the sprawl all over the page looks, well, not so professional.

I'm thinking... HEY, we just solved this problem like just last week - whewho-and-fist-pump!

Find the thread: 946504-adding-empty-records

Made the 25 row table ([ID]={1,2,3,...24,25}), altered the SQL we have here... and it works like a dream for the 25 row pre-printed document.

YEA!

Now, thinking "green" (I know... thinking again) we have these 3 x 1 - Row labels on an 8-1/2x11 paper that I'm constantly having one or two blank labels that go to waste because we'll only print what is in the database que. These unprinted labels go to waste as they can only go thru the thermal printer once.

And when we need a few "blank" labels for those one off hand entries, we have a word document to print a few blank forms. (back to that green thing) I'm like, hey, just use that ole-add the blank record to the end and we're no longer wasting the label and we shouldn’t ever need to “hand” print the extras again!

(OK, NeoPa and Rabbit: get out your girdles, you'll have a chuckle with this)

So, tweak the 25 to 3 in the SQL and What Worked wonderfully for the 25 row... wouldn't work for the 3 per page.

.... puzzled expression ....

Extra labels, or not enough labels and so forth... it didn't help that I've three other people and instruments wanting my attention for other projects too! Print Preview is a wonderful thing!

So after a day of interruptions and doing the math, wouldn't have taken so long if I could have just worked out the math in peace, I finally hit on the solution to make another table with only three blank rows for the 3 per page - and that worked!

However, I don't want two tables with blank records out there and possibly yet a third or fourth depending on customer and lab needs... so what to do?

My final solution has to deal with how the [ID] field is numbered in tbl_blank.
I have a set of [ID]={3001,3002,3003}; [ID]={25001,25002,25003,...25024,25025}]
So if I needed 100 rows... {100001, 100002,...100100}

SO using the code block from the above thread that solved the 15 row issue:

SELECT Null AS [IDField] 
     , * 
FROM   [tblEvidence] 
UNION ALL 
SELECT [IDField] 
     , Null 
     , Null 
     , ... etc 
FROM   [tblBlank] 
WHERE  ([IDField] Between ((((SELECT Count(*) 
                             FROM   [tblEvidence]) - 1) Mod 15) + 2) And 15) 
 

We modify as needed to use those [ID] ranges thus:

(first part of SQL omitted)
(for the 3x3):
UNION ALL  
   SELECT [blank_pk] as F1, 
          "", 
          "" 
   FROM   [tbl_Blank]  
   WHERE  ([blank_pk] Between 
      ((((SELECT Count(*) 
          FROM [tbl_data]) - 1) Mod 3) + 3002) And 3003)
 
(for the 25 row form:)
(first part omited...)
          FROM [tbl_data]) - 1) Mod 25) + 25002) And 25025)

And there’s the end of my tale
-z

[Rabbit]

That's odd, I don't see why there would be a need for multiple sets of IDs. If it works for 15, it should work for 3. There must be something else going on. My first thought is that it was counting the wrong table.

[zmbd]

That was my first thought too....
- and a few hours of head against the wall!!!!! @(

So, going back to the "if you're banging your head against the wall... you've missed something simple...." lesson from Dr. P.

I said to myself: "OK, let's see how this works:
I am going to break the SELECT portion of the conditional down into steps - working for the 3x1 setup"

I know the columns don't quite line up... however from left to right we take the action in the header and apply to the result of the immediate left column.

Lets take the equation: ((((SELECT Count(*) FROM [tbl_data]) - 1) Mod 3) + 2) And 3)                                    
 Record                                    
 Count   Count(*)    -1     mod 3    +2     To give the conditional of
0          0        -1        2      4    [ID] BETWEEN 4 AND 3
1          1         0        0      2    [ID] BETWEEN 2 AND 3
2          2         1        1      3    [ID] BETWEEN 3 AND 3
3          3         2        2      4    [ID] BETWEEN 4 AND 3
4          4         3        0      2    [ID] BETWEEN 2 AND 3
5          5         4        1      3    [ID] BETWEEN 3 AND 3
6          6         5        2      4    [ID] BETWEEN 4 AND 3
7          7         6        0      2    [ID] BETWEEN 2 AND 3
8          8         7        1      3    [ID] BETWEEN 3 AND 3
9          9         8        2      4    [ID] BETWEEN 4 AND 3
10        10         9        0      2    [ID] BETWEEN 2 AND 3
11        11        10        1      3    [ID] BETWEEN 3 AND 3
12        12        11        2      4    [ID] BETWEEN 4 AND 3

So when the "blank table" has more than three records where [ID]={1,2,3,...(n>=4)}, the "data table/query" record count is zero or MOD3=0; we have the conditional[ID] BETWEEN 4 AND 3; thus, returning Two records from the blank table to be appended to the the record set via UNION ALL when we're expecting nothing to be returned and other weirdness occurs.

Looking at the above, you'll see other weirdness too.
Reducing the [ID] to [ID]={1,2,3} either by eliminating records or via the groups then when Mod3=0 or RC=0 then no [ID] fall within the conditional.

and there you have it.


Oh, just noticed that I didn't change the Primary Key field name in my posted SQL....
[Blank_PK] is the equivalent to the [ID] field and I have used the two field names interchangeably (very poor oversight on my part sorry)... please don't let that confuse anyone. They are both the same reference to the Primary Key of the table containing the blanks. I dislike using the generic [ID] and opt for a more descriptive field name...

[Rabbit]

How come you're adding 2? Shouldn't you add just 1?

[zmbd]

you can certainly try with adding just one, however, when you do so:

Record Count    To give the conditional of
0    [ID] BETWEEN 3 AND 3 (now returns one blank record)
1    [ID] BETWEEN 1 AND 3 (now returns three blank records)
2    [ID] BETWEEN 2 AND 3
3    [ID] BETWEEN 3 AND 3 (now returns one blank record)
4    [ID] BETWEEN 1 AND 3
5    [ID] BETWEEN 2 AND 3
6    [ID] BETWEEN 3 AND 3
7    [ID] BETWEEN 1 AND 3
8    [ID] BETWEEN 2 AND 3
9    [ID] BETWEEN 3 AND 3
10    [ID] BETWEEN 1 AND 3
11    [ID] BETWEEN 2 AND 3
12    [ID] BETWEEN 3 AND 3
 

[Rabbit]

I should pay closer attention. Try:

ID >= 1
AND
ID <= (2 - (((
   SELECT COUNT(*)
   FROM tableName
) - 1) MOD 3))

[zmbd]

I'll have to try that when I get back to the lab Monday or Tuesday (provided someone hasn't pressed the self destruct button again!)

I should have used the "range method" as you've given instead of the BETWEEN method - I've done so in the past when joining subqueries of lab data together for reports. Guess I started down that road with the BETWEEN and like a dog-with-a-bone couldn't let it go... besides, with the day I was having, it was a puzzle to solve to relieve the stress of the day! It just bugged me that it wasn't a generic construct!

(I know, Chemist programs/designs databases and solves logic puzzles to relieve stress... never said I was sane)

Now a thought, how does the performance between the two methods compare?

[Rabbit]

I wouldn't expect the performance to be any different. I do expect the results to be different.

This can never return results.
>= 1 AND <= 0

This will flip the numbers and be able to return results.
BETWEEN 1 AND 0