UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Report Page Break Anomoly, Access 2010    
 
   
zaxbat
post Feb 21 2018, 12:18 PM
Post#1



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


I have a database with about 40 interconnected tables. So, not small. Whenever I run queries I get like 54,000 hits and that is ridiculous. I know there should only be a few hundred hits (not 54,000) but because of the way I have to join the 40 tables in the query....access returns bzillions of spurious hits. At report time that turns into a nightmare. I get an 80 page report when I know it should only be about 7 pages. For that reason, I programmatically run through the query results collecting up only the non-dupe data and then send it to the report. (I seem to spend 99% of my time finding work-arounds to Accesses quirky characteristics. Am I doing something wrong?) Now, on to my problem...I have a monster report that has given me fits but I believe I finally got all the data right only to find that the doggone report either throws away or scrambles a record (rather than printing it) if the record occurs at the bottom of the page but does not fit (should be continued at top of next page). Really weird. And before you decide that I need to say 'yes' to the keep together property or need to say keep header of footer together.....I have tried all of that in every conceivable pattern. No joy! If I say to keep together then that entire record is just thrown away, if i say do not keep together then that record starts at the bottom of the page with hashtags '######' in the first text box instead of data....and the remainder of that record that should be at the top of the next page is thrown away...just gone. I have reverse sorted the report as a test so that record does not fall on a page break. It formats that same record perfectly, with no hashtags, when it does not occur at the page break. I think that the problem may stem from the fact that I have no choice but to use the 'can grow' and 'can shrink' on the text boxes of the report since the data is highly variable in length. If I tell it to keep the headers and footers together with their groups, then the report prints a footer for each record but only actually prints the record data for the very last record of the report. Has anybody seen a similar problem and/or heard of a work around for it? Thanks in advance. I have attached a sanitized copy of a page of the report.
This post has been edited by zaxbat: Feb 21 2018, 01:02 PM
Attached File(s)
Attached File  prob.jpg ( 276.88K )Number of downloads: 4
 
Go to the top of the page
 
GroverParkGeorge
post Feb 21 2018, 12:43 PM
Post#2


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


While I actually didn't read the entire transcript of your problem, I think I might have an idea.

Please post the exact SQL from one of these queries returning spurious resultsets. Open it in SQL view and copy it out to post here. We can probably identify the problems better that way.

My guess would be, initially, that you have not joined tables in the query, which results in a Cartesian result set.
Go to the top of the page
 
zaxbat
post Feb 21 2018, 12:53 PM
Post#3



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Thank you for the speedy reply.....i will post what you ask in a bit...still putting together the image for attachment to my first post.
Go to the top of the page
 
zaxbat
post Feb 21 2018, 01:05 PM
Post#4



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Actually, the query is not so simple.....it is a rather lengthy chain of queries that I was forced to do to get around limitations of MS ACCESS. So I guess I will need to give all of this chain of queries...I think about 14 of them in this chain.



(this is the main call…done programmatically using the DoCmd.RunSQL MySqlTxt command)

Case "3" 'linelist reports (used with 31 and 32)
'Get all valid data that is within given date range...
MySqlTxt = _
" SELECT zCOMPLETE.*" & _
" INTO " & MyTmpTblName & _
" FROM zCOMPLETE" & _
" WHERE (" & _
" (IIf(IsDate([MedEndDate]),[MedEndDate],#" & AdjustedEndDate & "#) Between #" & GlobalStartDate & "# And #" & GlobalEndDate & "#)" & _
wheretag & wherestat & wherefacility & _
" )" & _
" ORDER BY facility & Station, Psort, AdmDate Desc, DTDATE Desc, MedStartDate Desc, LabDate Desc, RadDate Desc, CULDate Desc," & _
" GramDate Desc, BUGDate Desc, IntDate Desc, DevDate Desc" & _
";"



(this is related SQL for the WHERE clause)

If Date < CDate(GlobalEndDate) Then AdjustedEndDate = Date Else AdjustedEndDate = GlobalEndDate 'use todays date
If UseTagsButton = True Then wheretag = " AND (tag = true)" Else wheretag = ""
wherestat = " AND (status LIKE '" & MyStatComboBox2 & "') "
wherefacility = " AND (facility LIKE '" & MyFacilityComboBox2 & "') "






(zCOMPLETE)

SELECT Trim(Str([zdt].[id])) AS DTID, zDT.DTDate, zDT.DTMD, zDT.Diagnose, zDT.POAHAI, zDT.Isolation, zDT.Event, zDT.Careplan, zDT.DTNote, zMedLvl.MEDName, zMedLvl.Dose, zMedLvl.Freq, zMedLvl.Route, zMedLvl.MedStartDate, zMedLvl.MedEndDate, zMedLvl.MedDays, zMedLvl.Cultured, zMedLvl.TimeOut, zMedLvl.Criteria, zMedLvl.MedNote, zMedLvl.LVLDate, zMedLvl.LVLTime, zMedLvl.LVLType, zMedLvl.LVLValue, zMedLvl.LVLGoal, zMedLvl.LVLNote, zDEV.DevDate, zDEV.Device, zDEV.DevNote, zINT.IntDate, zINT.Intervention, zINT.IntNote, zLAB.LabDate, zLAB.WBC, zLAB.BUN, zLAB.PCT, zLAB.Scr, zLAB.Temp, zLAB.UADate, zLAB.UA, zLAB.LabNote, zCulGramBug.Culture, zCulGramBug.CULDate, zCulGramBug.GramDate, zCulGramBug.Gram, zCulGramBug.GramNote, zCulGramBug.BugDate, zCulGramBug.BUG, zCulGramBug.SR, zCulGramBug.BUGNote, zRAD.RadDate, zRAD.Radiology, zRAD.RadNote, [PatAdmSort] & '~' & Right('00000' & Trim(Str([zDT].[id])),6) AS PatAdmDtSort, zADMPAT.ADMDate, zADMPAT.Reason, zADMPAT.DisDate, zADMPAT.ADMDays, zADMPAT.FIN, zADMPAT.WghtLbs, zADMPAT.WghtKgms, zADMPAT.HghtIns, zADMPAT.HghtCms, zADMPAT.IBW, zADMPAT.RstctFluid, zADMPAT.ADMMD, zADMPAT.Facility, zADMPAT.Station, zADMPAT.Room, zADMPAT.Vent, zADMPAT.ADMNote, zADMPAT.PSort, zADMPAT.PatAdmSort, zADMPAT.PName, zADMPAT.Status, zADMPAT.DOB, zADMPAT.Age, zADMPAT.Gender, zADMPAT.Diabetic, zADMPAT.Tag, zADMPAT.PNote
FROM (zMedLvl RIGHT JOIN (zRAD RIGHT JOIN (zDEV RIGHT JOIN (zINT RIGHT JOIN (zLAB RIGHT JOIN (zCulGramBug RIGHT JOIN zDT ON zCulGramBug.DTKee = zDT.ID) ON zLAB.DTKee = zDT.ID) ON zINT.DTKee = zDT.ID) ON zDEV.DTKee = zDT.ID) ON zRAD.DTKee = zDT.ID) ON zMedLvl.DTKee = zDT.ID) LEFT JOIN zADMPAT ON zDT.AdmissionKee = zADMPAT.ADMID;

(zMedLvl)
SELECT zMED.DTKee, zMED.MEDName, zMED.Dose, zMED.Freq, zMED.Route, zMED.MedStartDate, zMED.MedEndDate, zMED.MedDays, zMED.Cultured, zMED.TimeOut, zMED.Criteria, zMED.MedNote, zLVL.LVLDate, zLVL.LVLTime, zLVL.LVLType, zLVL.LVLValue, zLVL.LVLGoal, zLVL.LVLNote
FROM zMED LEFT JOIN zLVL ON zMED.ID = zLVL.MEDKee;

(zRAD)
SELECT Radiology.DTKee, Radiology.RadDate, RadiologyTypes.RadTypes AS Radiology, Radiology.RadNote
FROM Radiology LEFT JOIN RadiologyTypes ON Radiology.RadType = RadiologyTypes.ID;

(zDEV)
SELECT Devices.DTKee, Devices.DevDate, DeviceTypes.DevTypes AS Device, Devices.DevNote
FROM Devices LEFT JOIN DeviceTypes ON Devices.DevType = DeviceTypes.ID;

(zINT)
SELECT Interventions.DTKee, Interventions.IStart AS IntDate, InterventionTypes.ITypes AS Intervention, Interventions.INote AS IntNote
FROM Interventions LEFT JOIN InterventionTypes ON Interventions.IType = InterventionTypes.ID;

(zLAB)
SELECT Labs.DTKee, Labs.LStart AS LabDate, Labs.WBC, Labs.BUN, Labs.PCT, Labs.Scr, Labs.Temp, Labs.UADate, UA_Types.UATypes AS UA, Labs.LabNote
FROM Labs LEFT JOIN UA_Types ON Labs.UAType = UA_Types.ID;

(zCulGramBug)
SELECT zCUL.DTKee, zCUL.Culture, zCUL.CULDate, zGramBug.GramDate, zGramBug.Gram, zGramBug.GramNote, zGramBug.BugDate, zGramBug.BUG, zGramBug.SR, zGramBug.BUGNote
FROM zCUL LEFT JOIN zGramBug ON zCUL.ID = zGramBug.CultureKee;

(zDT)
SELECT DiagnoseTreat.ID, DiagnoseTreat.AdmissionKee, DiagnoseTreat.DTDate, Physicians.ShortName AS DTMD, DX_types.DXTypes AS Diagnose, DiagnoseTreat.POAHAI, Isolation_Types.ITypes AS [Isolation], DiagnoseTreat.Event, DiagnoseTreat.Careplan, DiagnoseTreat.DTNote
FROM ((DiagnoseTreat LEFT JOIN DX_types ON DiagnoseTreat.Diagnosis = DX_types.ID) LEFT JOIN Physicians ON DiagnoseTreat.Physician = Physicians.ID) LEFT JOIN Isolation_Types ON DiagnoseTreat.Isolation = Isolation_Types.ID;
This post has been edited by zaxbat: Feb 21 2018, 01:56 PM
Go to the top of the page
 
GroverParkGeorge
post Feb 21 2018, 01:20 PM
Post#5


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


I think perhaps you are misinterpreting the complexities of your design with "limitations of Access"

Properly designed, things tend NOT to be all that difficult to do.

What you can do on your own, I think, is look for queries where you have failed to establish joins between tables.

Also, as you chain that many queries, there's a fairly reasonable probability that you are inadvertently returning redundant records.

Hard to know, or even guess, without actually seeing the queries.

For now, let's forgo further screen shots. I think the more useful data will be found in the SQL, which you can copy and paste.
Go to the top of the page
 
zaxbat
post Feb 21 2018, 01:31 PM
Post#6



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Most of the chained queries are small and simple but were required by ACCESS since it would not allow more than 9 joins per query. You probably will notice other query calls that I did not include for now. I can include them but they are very simple and most likely not part of the problem.

I am sure you can see that this access project is related to the medical field...tracking patient treatment.

each patient can have multiple admissions
each admission can have multiple diagnoses
each diagnosis can have multiple: Labs, devices, interventions, xrays, cultures, treatments, results, etc.

The data works great in the forms.....no problems at all....but the reports can be very difficult since they don't have interactive windows with scrolling datasheets..hahahaha.
This post has been edited by zaxbat: Feb 21 2018, 02:07 PM
Go to the top of the page
 
zaxbat
post Feb 21 2018, 02:30 PM
Post#7



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


I put the main SQL calls into my previous post for your perusal.
Go to the top of the page
 
GroverParkGeorge
post Feb 21 2018, 02:40 PM
Post#8


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


Thanks.

So the problem isn't missing joins, apparently.

The problem might be that you are using Outer Joins throughout, so that multiple records could be returned at any given point, some of which are redundant. Again, though, it's hard to know without data.

Where did you get that limitation on the number of joins? The Specifications for Access indicate you should be able to use up to 16.




Go to the top of the page
 
zaxbat
post Feb 21 2018, 02:44 PM
Post#9



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


would be nice if you can go up to 16 now....but i an using 2010 and definitely get nasty grams about conflicting joins when I make them too complex. It then suggests that I string a series of joins together.

I almost exclusively use RIGHT JOINS since that seems to be the best way not to miss data.

I have toyed with using the DISTINCT directive but found it only filters out a few (if any) spurious hits.


i'll be AFK for about an hour...
This post has been edited by zaxbat: Feb 21 2018, 02:45 PM
Go to the top of the page
 
zaxbat
post Feb 21 2018, 06:42 PM
Post#10



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Just a shout out to see if anybody else has seen this problem. I have a complicated report that is discarding records when they fall at the bottom of the page and would need to be continued on the top of the next page. This happens when i set keep together to yes. If i set keep together to no then it begins putting the record at the bottom of the page (thought it does put hashtags for data for some reason)....then you would think it would finish printing that record at the top of the next page, but it does not...it just throws it away and proceeds on to the next record. If anybody has seen anything like this please holler. Tanks!
Go to the top of the page
 
zaxbat
post Feb 22 2018, 09:05 AM
Post#11



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Nobody? Really? Wow, really need some help here.....
Go to the top of the page
 
zaxbat
post Feb 24 2018, 06:31 AM
Post#12



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


BREAKING NEWS!!!!!
This is interesting. I normally open the report for view before print with the following command:

DoCmd.OpenReport reportformname, acViewPreview, , , acDialog

I have changed the "acViewPreview" directive to "acViewReport"
With the acviewreport directive the entire report is put onto one very long page and displayed on the screen with no breaks.
Now the problem of scrambled and/or dropped records goes away. Well, no...it looks good on the screen display. But when sent to
printer it still scrambles and/or drops data at the page breaks. So this is not a solution but only a clue.

It indicates that the data is there and correct but simply that the Access function that attempts to paginate the report data can't handle what I am asking it to do.
But I do not understand why that would be so.

Any thoughts? Anything at all would be much appreciated. Thanks in advance!
Go to the top of the page
 
theDBguy
post Mar 3 2018, 11:09 AM
Post#13


Access Wiki and Forums Moderator
Posts: 73,276
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Pardon me for jumping in...

I don't really have an answer for you but based on your latest findings, I would probably try to play with the report and footer margins just in case the data is getting printed outside of the page margins.

However, in post #6 , you said "The data works great in forms..." Are you using a form/subform setup? If so, then I was just wondering if you have tried using a report/subreport setup as well rather than try to build a "monster" query to bring all the data into one whole record source for a report.

Just curious...
Go to the top of the page
 
zaxbat
post Mar 5 2018, 05:52 PM
Post#14



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Thank you for your input. I thought about that possiblity, but because of the way the report needs to combine multiple records into one column entry....the report would not adhere to the layout spec if I used form/subform.

Since the report looks perfect until Access puts in the page breaks.....I'm perplexed. If I call DoCmd.Openreport with acVeiwPreview access put in page breaks and I Lose data on some page breaks. If I call openreport with acViewReport access does not put in page breaks and no data is missing. However, if I send the acViewReport results to printer....then access puts in page breaks and data is lost again. If I send it to PDF...same page breaks are put in and data is lost.

I have seen some posts referring to a routine (maybe 3rd party) that allows the app to process through there report and put in the page breaks rather than relying on access builtins. I might have to go and find that.
Go to the top of the page
 
theDBguy
post Mar 6 2018, 12:27 PM
Post#15


Access Wiki and Forums Moderator
Posts: 73,276
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I haven't heard of any such third-party application. Good luck with your quest.

Did you try playing with the margins just in case?
Go to the top of the page
 
zaxbat
post Mar 6 2018, 12:45 PM
Post#16



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


I have looked at the margins. I have run into situations where inexplicable formatting occurs in the report margins during design mode so I always keep an eye on them. I do not notice anything odd on this report. But will keep working on it.

Here is a thought.....can I get control of the temporary form that the report sits in while it is being displayed to the user in acViewReport mode????? It certainly must have a temp table holding the data and the display is in a temp form....so it should be available if I can just find it in the object browser.

I am going to have to focus in that direction....because if nobody else has seen this problem MS certainly will not address it just for me. Ha a ha ha ha ha ha ha ha h
This post has been edited by zaxbat: Mar 6 2018, 12:50 PM
Go to the top of the page
 
zaxbat
post Mar 16 2018, 08:29 PM
Post#17



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Well, would be good to do this the right way...the access way...but access definitely does not like what I am handing over to it to format. Literally spent weeks on this bug. Finally just had to format the report manually and put it into a table of memo fields so that everything was where it belonged. Then finally passed that table to the report module and it put the page breaks where they belong without throwing away data records. Well, that's the least it could do since I did everything else.

Just for informational purposes...the reason that the report module could not handle this involved a textbox in the report detail area that had both grow and shrink enabled which was filled with data dynamically through a call to a global function which might concatenate an unlimited number of diverse fields together into a longtext string. The box has to disappear when no data is entered into it and it has to grow beyond the page boundary in some cases where many records are associated with it. Access simply could not handle it......
Go to the top of the page
 
GroverParkGeorge
post Mar 17 2018, 08:33 AM
Post#18


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


Hm. I thought "bug" in software referred to things that break, i.e. a problem causing a program to crash or produce invalid output.

You got valid results given your inputs. The problem is here: "...the box has to disappear when no data is entered into it and it has to grow beyond the page boundary in some cases where many records are associated with it"

Well, if it it has to grow beyond the page boundary, doesn't that imply it CAN'T be rendered with a page break at some arbitrary point within that text box? By arbitrary, I mean there's no programmatic way to make that decision for the internal contents of a control on a page, at least not without resorting to calculations that may or may not be worth the time invested. As always, though, go see Stephen Lebans. He often provided code that performs miracles of that sort..

A problem? Yes. A bug? No, not really.

Sometimes the solution to problems is a bit of creativity on our part. Which you have found here.
Go to the top of the page
 
zaxbat
post Mar 17 2018, 08:54 AM
Post#19



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


No, not at all. The program does all that I ask of it except that on some records it fails. On some it does fine. Could not determine why. If you looked through the previous posts you should see that it rendered in acViewReport perfectly.....all data in place. Not until it was sent to pdf file or to printer (where it tried to insert the page breaks) did it fail. Where it failed was not on a textbox that was larger than a page. Even where a textbox would extend beyond the page boundary the report module should just start it at the top of the next page.
This post has been edited by zaxbat: Mar 17 2018, 08:58 AM
Go to the top of the page
 
GroverParkGeorge
post Mar 17 2018, 09:41 AM
Post#20


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


"The box has to disappear when no data is entered into it and it has to grow beyond the page boundary in some cases where many records are associated with it. "

"Even where a textbox would extend beyond the page boundary the report module [sb]hould just start it at the top of the next page[/b]."

Hm. And does that mean you want to move the entire contents of the textbox in question onto the next page, leaving a blank space where it would have been on the previous page? And if you do that with a text box which, by itself still can't be printed on a single physical page, what would you expect to happen, should Access try to guess at a good breaking point?

You are aware, I'm sure, that you can manage page breaks by creating Groups on the report and setting the Page Break and Keep Together properties appropriately. I assume you have tried those options and failed to get the results you wanted?

Report view vs Print View: At some level, this is an apples vs oranges comparison.

Specifically, Report View is ONLY for on-screen display, which means "page breaks" don't come into play; a report is as tall as it needs to be because it can continue to scroll down your display until it ends, no breaks.

Print and Print Preview, on the other hand, involve a physical piece of paper that can't just grow or shrink dynamically in the printer, or a WYSIWYG representation of that same physical page which would logically follow the same rules.


Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 04:14 AM