June 27, 2015 at 2:19 pm
First, a little background info. I am using SQL Server 2008 as a back end for a Microsoft Access front end. I have created a report that is essentially a Bill Of Lading. The detail section lists all the purchase orders that are being shipped on a single load. The problem with the Access Report is that I always need a set number of records (8) so that the layout is consistent. So, if the query returns 5 records, I need an additional 3 blank records returned with the recordset. If there are 2 records, I need an additional 6, and so on. For simplicity sake the query is:
SELECT tblBOL.PONumber FROM tblBOL WHERE tblBOL.BOLNumber=@BOLNumber;
Now, I can get the results I want by using a union query for the "extra" records. For instance, if there are 6 records returned for BOLNumber '12345', I can get the expected results by this query:
SELECT tblBOL.PONumber FROM tblBOL WHERE tblBOL.BOLNumber='12345'
UNION ALL SELECT '12345',Null
UNION ALL SELECT '12345',Null;
Another solution would be to create a temporary table with the "extra" records and then have only one Union statement. Not sure which is better, but I'm not really sure how to programmatically do either of these. I'm guessing I need to do it in a stored procedure. How do I programmatically create these extra records? One other note.... If there are more than 8 records, I need to return 8 of these "blank" records and none of the real records (hard to explain the reason behind this, but it has to do with the report being only a summary when there are more than 8 records while the actual records will go on a different supplemental report).
June 27, 2015 at 5:51 pm
UNION your real result set with something like
SELECT 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.
June 28, 2015 at 5:14 pm
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;
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 29, 2015 at 10:03 am
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
June 29, 2015 at 6:29 pm
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.
June 29, 2015 at 8:23 pm
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
June 29, 2015 at 10:00 pm
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.
June 30, 2015 at 1:49 am
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
June 30, 2015 at 5:02 am
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply