I think a much better approach is to update your Access report. It's been awhile, but there should be settings in your Access report to set minimum and maximum heights for your report/sub-report or alternatively not to adjust the height. I can give you better details when I check from my home computer.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
pietlinden (6/27/2015)
UNION your real result set with something likeSELECT Null, Null, Null
FROM Tally
WHERE n<=@NumRows
you would need to figure out the count of missing rows to do it, but that would be trivial.
Sorry for the delay in replying. This worked the way I wanted it to. I've never used a tally table before, but now I can see how they are useful. I'm used to procedural programming and am trying to think more in set based logic. It's a little hard to get used to, or to come up with solutions when you are used to loops! I appreciate the help, and have added the tally table to my "toolbox".
mister.magoo (6/28/2015)
another possibility is to join to a set of 8 rows, using a row number
create table #tblBOL(PONumber int);
insert #tblBOL(PONumber) values(100001),(100002),(100004),(100006);
select coalesce(tBrn.PONumber,0) as PONumber
from (values(1),(2),(3),(4),(5),(6),(7),(8)) Tally(N)
left outer join
(
select tB.PONumber, row_number() over(order by tB.PONumber) as rn
from #tblBOL tB
) tBrn
on TBrn.rn = Tally.N;
drop table #tblBOL;
This solution worked as well. It works well with a small number of rows, but I don't think is easy as the above solution to adopt to a bigger number of rows (although my question specifically didn't say I'd ever have to adopt to more rows). I did learn something from the syntax though with the Tally(N) that I wasn't familiar with.
drew.allen (6/29/2015)
I think a much better approach is to update your Access report. It's been awhile, but there should be settings in your Access report to set minimum and maximum heights for your report/sub-report or alternatively not to adjust the height. I can give you better details when I check from my home computer.Drew
Thanks Drew. However it's not as simple as that. I don't need to set the minimum and maximum heights for the report, but I need to always have the same number of rows in the detail section. You are correct that the space would be "reserved" if I set the maximum height (or set it to not grow/shrink), but I also need to print blank lines in the space. It's hard to explain, but I've attached 2 pictures. The one with the white space is what I don't want (this is what would happen by changing the size as you described). The one with the blank lines is what I'm looking for. Make sense? Sorry for the confusion.
skilly2 (6/29/2015)
This solution worked as well. It works well with a small number of rows, but I don't think is easy as the above solution to adopt to a bigger number of rows (although my question specifically didn't say I'd ever have to adopt to more rows). I did learn something from the syntax though with the Tally(N) that I wasn't familiar with.
Actually there's nothing that would keep it working with any number of rows. The following is a variation that will produce a 1M row tally table.
WITH n (n) AS (
SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)
), Tally (n) AS (
SELECT -- add a TOP N clause here, to limit the number of rows produced.
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM n n1, n n2, n n3, n n4, n n5, n n6
)
SELECT n FROM Tally
Jason A. Long (6/29/2015)
skilly2 (6/29/2015)
This solution worked as well. It works well with a small number of rows, but I don't think is easy as the above solution to adopt to a bigger number of rows (although my question specifically didn't say I'd ever have to adopt to more rows). I did learn something from the syntax though with the Tally(N) that I wasn't familiar with.
Actually there's nothing that would keep it working with any number of rows. The following is a variation that will produce a 1M row tally table.
WITH n (n) AS (
SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)
), Tally (n) AS (
SELECT -- add a TOP N clause here, to limit the number of rows produced.
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM n n1, n n2, n n3, n n4, n n5, n n6
)
SELECT n FROM Tally
Sorry, I think I misinterpreted how the statement is actually working. I will have to dig in a little bit more tomorrow to understand it.
just a thought......move the "grand total" into the report footer section and not in the detail section.
google will be your friend if you are not sure how to do this in your access version
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
J Livingston SQL (6/30/2015)
just a thought......move the "grand total" into the report footer section and not in the detail section.google will be your friend if you are not sure how to do this in your access version
Although that would get me close, it's still not what I was looking for. It would push the grand total to the bottom below all the white space, but I'm trying to eliminate the white space. Essentially simulating blank records is the only way I know of to achieve this. When doing it that way, it fills the white space with gridlines, which is what I'm looking for. Think of it as being able to handwrite a record below the ones that are printed. It's a lot easier with the gridlines.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply