Adding empty records

[zmbd]

Rabbit:
I thought I try that... using a very simple select query against one field in my test db, I'm getting that same error if you even do something like
SELECT TOP (14 - 1)....
Appears the engine does not like calculated values.


<EDIT>
ALL, DISTINCT, DISTINCTROW, TOP Predicates

The value that follows TOP must be an unsigned Integer.

</EDIT>

[Rabbit]

I've used it in SQL Server but not Access, if you can't use top, move it the WHERE clause on a field that numbers the records from 1 to 15.

[NeoPa]

Rabbit:
You didn't specify which fields to select in your bottom query and so it thinks you're trying to select a field named from.

That's because my interpretation of your suggestion omitted that from the SQL (OOOoooops)! My bad there.

SELECT TOP (formula)

This doesn't work in Jet SQL! Well, that certainly sucks big time.

How about this :

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

[tblBlank] needs records with just the [IDField] set and numbered from 1 to 15 (at least).

[frankiejj98]

I am trying but still am not having any luck. Maybe I we can find a manual solution? I tried to add the blank records manually and it did work...however, it is printing the blank records first and then the actual records with data. If I could figure out a way to sort it the opposite way I could make this work manually...Any ideas?

[Rabbit]

You need to describe what's not working. What did you try? What code did you use? What went wrong? Did you get error messages?

Why go manual when you can just set up an automated process? Just because you failed to set it up correctly a few times shouldn't discourage you from doing it the more efficient way.

[NeoPa]

Have you tried including the [IDField] field as the first field for sorting by in your report? If Nulls appear in the wrong position you can easily change the SQL to return a zero (0) instead for the records returned by [tblEvidence].

[frankiejj98]

Thanks Rabbit,
The error that is returned is 'The select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.'
Here is the code that I am using:

SELECT *
,*
FROM [tblEvidence]
UNION ALL SELECT TOP (14 - (((SELECT Count(*)              
          FROM [tblEvidence]) - 1) MOD 15))
          FROM [tblBlank]

Thanks again!

[Rabbit]

1) You have SELECT *,*. There's no need to select all the columns twice. That's certainly going to cause conflict as there will be two of every column with the same name.

2) You need to reread the posts because we determined that you can not use an expression in the TOP predicate and proposed an alternate solution. (Refer to NeoPa's post, #18). It's not quite right, but it gets you most of the way there, he forgot to select the rest of the columns in the blank table.

[frankiejj98]

Okay, I followed the instructions from post# 18. I ran the query and get the following error msg: 'The number of columns in the two selected tables or queries of a union query do not match'. So just in case, I copied my tblEvidence and saved it as tblBlank. I already had the IDField created in tblEvidence. I'm not sure what else to do. I am really frustrated and I hope that it is not too frustrating for you...I am clearly over my head with this stuff. I understand if you do not want to continue with this thread.
Thanks,
Frank

[zmbd]

That is a fluke of MS Access UNION queries.
The number of fields in each has to match:
SO lets back up a tad and use an example (forgive an old Chemist and TA, it's the only way I can make sense of my thoughts - I have to get them on "paper" sometimes :-), wait till you get old - it'll happen to you too (chuckle) )

OK
tbl_data
[data_pk] autonumber
[data_field1] (doesn't matter, just example)
[data_field2] (doesn't matter, just example)
[data_field3] (doesn't matter, just example)

tbl_blank
[blank_pk] number(long) (in this case records with values 1 thru 15)
[blank_field1] text(1) (in this case leave null, or enter a " " space)

Now, lets use the information provided in post #18 tweaked for this generic example - and I'm going to use hard coded fields for the example:

SELECT [data_pk],
   [data_field1],
   [data_field2] 
FROM   [tbl_data] 
UNION ALL 
   SELECT [blank_field1] as F1,
      [blank_field1] as F2,
      [blank_field1] as F3
   FROM   [tbl_Blank] 
   WHERE  ([blank_pk] Between
      ((((SELECT Count(*)
          FROM [tbl_data]) - 1) Mod 15) + 2) And 15)

(please note, this SQL has been vetted against my test DB using the tables and records as indicated for MS Access 2010: [data_pk]=autnumber; [data_field1]=date; [data_field2]=text(50); [blank_field1]=text(1) are all null/empty value; [blank_pk]=number(long))

The main thing to notice here is that the number of fields in each "SELECT" portion are the same.
[edit - toddler playing with enter key :) - sigh]
So if you use the code from #18 as is, then you need to make sure that within the second "SELECT" following the "UNION ALL" has the same number of fields as the first one does in order for the query to work.

[NeoPa]

Rabbit:
It's not quite right, but it gets you most of the way there, he forgot to select the rest of the columns in the blank table.

Not so much forgot, as misinterpreted your comment from post #13. i read that as meaning the number of fields didn't need to match. Now I reread it I see that wasn't what you said.

@FrankieJJ
Try the following, but include Null entries for all the fields in the second query that occur in the first (IE. [tblEvidence]).

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)