Fixed number of records in a query

[Fernando]

Hey all, i just would like to know if there is a way to have a fixed number of records in a query.
Let's say, example (a), a query returns 2 records (qry1); i would like to have my query (qry2) to have 5 records, so the first two records will appear and then 3 blank records. (b) A query (qry1) returns 4 records, then this query(qry2) should return the 4 records plus a blank record

.........qry1.......qry2
a)|>record1....|>record1
...|>record2....|>record2
...|*______....|>______
....................|>______
....................|>______
....................|*______

.........qry1.......qry2
b)|>record1....|>record1
...|>record2....|>record2
...|>record3....|>record3
...|>record4....|>record4
...|*______....|>______
....................|*______

Why do i want to do this?
Because i want my report to show 5 records so when i print it i have those blank spaces to fill if necessary.

I want to have this:
______
lrecord1l
lrecord2l
l______l
l______l
l______l

Instead of:
______
lrecord1l
lrecord2l

What im doing now is creating a temporary table and add the records to it plus blank records.
What i used to do was add blank records in the same table meting the criteria of the report, so they will appear. After close the report, those records would be deleted.

I would like to know if theres another way, like a command in sql, or maybe some property on the report. What ever is most efficient and a good database practice. Thanks in advance

 

[The_Doc_Man]

What ever is most efficient and a good database practice.

There you go, putting all sorts of constraints on this item. There is NO good database practice for this for general databases.

In Access, however, you could try to create a fixed-size Details section in your report and set the thing to CanGrow = false and CanShrink = false. This is an Access-only solution and only applies to reports. SQL explicitly does not do what you want because to do so, it might sometimes have to violate the rules of set theory on which it is founded. Supplying "fake" records changes the underlying recordset, which is a pragmatic approach that works if you aren't worried about "good database practice."

 

[Fernando]

Thanks for your response doc_man
However, the details' section cangrow and canshrink properties were already False. But that gave me an idea, i made a subreport that can grow in the size-fixed details section.Yet, the details section grew.

I have to mention that i had my report from a joint query, the tables are:

TblJob
JobID as PK
Jobname
etc...

tblClient
ClientID as PK
ClientName
etc...

tblPurchaseOrder
POID as PK
JobID as FK
ClientID as FK
etc...

tblPurchaseOrderDetails
POID as FK
etc...

the query is like:
tblJob one to many with tblPurchaseOrder
(so i can show the JobName)
tblClient one to many with tblPurchaseOrder
(to get the client's info)
tblPurchaseOrder one to many with tblPurchaseOrderDetails

My report's record source is this query.

But now that i had the idea of subreport, would it be better if i separate this into two queries (one for the report and the other for the subreport) and add a subreport linking them with the field POID? Is it better without a subreport? Which of them would be more efficient?

Going back to my original question.

Here i attach images of the report so it will be easier to understand. I want to go from purchase1.jpg to purchase2.jpg without creating a temporary table (as i'm doing now) or creating fake records

Again thanks for ur response doc_man
Edit: the number of fake records are meant cover the entire page

 

[The_Doc_Man]

If all you wanted was to force a page feed after a details section, create a footer and put a "Page feed after section" in it. Or if there is another page, create a header and put a "Page feed before section" in it.

 

[Fernando]

Doc_man I appreciate your time, thanks again.
I couldn't find any "Page feed" Property, all i could find were "Force new page" and "New row or col". I created a footer (not shown in image.jpg) for field [ID] and set "New row or col" to After section. It showed me a big blank space between that section and the report footer. Here i attach the design view of my report, maybe that can help a little.