Data correct on report view but not when printed (1 Viewer)

ReneeB · Jan 8, 2014

Hey all,
I'm pretty sure this is easy for you

but I'm a relative newbee.
I'm tracking some charges and want to report them based on month/year and an Early/Late (yes/no) flag within the query I created. When I wrote the report I grouped on mon/year then on Early/Late, then calculate averages. I then want to calculate the difference in the charges between the Early & Late rows. Here....

Jun 13
32 868 27 (Early row #, charge, avg)
11 1279 116 (Late row)

Now I want to subtract the 27 from the 116. I figured out I could assign the 27 to a field in the header and the 116 in the footer, that's the only way I could make them "stick". I then calculate the difference and things are great. Until I try to print it. When I print the difference is 0 because the group header & footer fields are both populated with the 27.

I'm grateful for your assistance, Thanks
Renee

Mihail · Jan 8, 2014

Can you post a pic with your report ?

ReneeB · Jan 9, 2014

Wow, this was harder than it should have been, unable to cut & paste into here. And I have a feeling I may not have attached the files correctly.... hard to tell
So, I've attached 2 doc files. One is the Report view - all the numbers are correct and the hidden fields are made visible. The other is the Print Preview.
Thanks again!!
Renee

JHB · Jan 9, 2014

Difficult to see what goes wrong, post a stripped version of your database with some sample data in it, zip it because you haven't post 10 post yet). Also in which report you have the problem.

Mihail · Jan 9, 2014

And save it in 2003 version (I use 2007)

ReneeB · Jan 9, 2014

Thank you both!!
JHB - you asked which report -- the report view is correct but when it prints the value in the earlyavg and lateavg become the same, which isn't what I was attempting to do. I'm sure it's probably something simple.
Mihail - my access defaults to 2007

Mihail · Jan 9, 2014

First, your report seems to be corrupt.
While all other objects can be easily opened in design view or to be run, the report take a lot of time in order to open either in design view or to be run.

I have seen a lot of combos that have a calculated control source.
All this combos should be replaced with textboxes.

So, first of all, you should create a new report from scratch.

I don't understand what you try to calculate.
Post the mathematical formulas that solve your issue and I'm sure that I'll be able to implement this in your report.

JHB · Jan 9, 2014

ReneeB said:
JHB - you asked which report -- the report view is correct but when it prints the value in the earlyavg and lateavg become the same, which isn't what I was attempting to do.

It was only to get the name of the report, sometimes people post a database with many form/report in, and then it is difficult to know which form/report there have the problem.

ReneeB said:
I'm sure it's probably something simple.

Sorry but I don't think so, there is a difference how MS-Access reports works in "Report View" and "Print Preview.
For example if you have code behind some report's events, are these only executed in the "Report Preview" or when the report is printed.
You need to do some more calculation in the query you have or if it not give you the right result, a second query based on the first query.

ReneeB · Jan 9, 2014

Ok, let me recap what you guys are telling me:

- The report is corrupt --- probably not, the detail looks like it does on purpose, I redacted the data

- This is not a simple fix. (DANG!!!)

- You can see that I didn't use events, so those can't be wrong

- I've used combo boxes in which to do my calculations instead of text boxes and I should change those.

So.....my thinking about the difference between the displayed Report view and the printed version is in the passing.....when displayed it calculates as it goes, when printing it calculates on first pass then prints which would make sense as to why the lateavg is the same as the earlyavg because of when data was placed in the field...?????? Just a thought

And....if i'm going to have to write a new report anyway.....what would be the best way to group the data so that I can subtract the earlyavg from the lateavg? (because right now I'm not seeing it :banghead

Thanks again, Renee

Mihail · Jan 10, 2014

And....if i'm going to have to write a new report anyway.....what would be the best way to group the data so that I can subtract the earlyavg from the lateavg?

As JHB suggested, you should create more queries where to calculate the averages.
Keep in this query a field that will allow yo, at a later time, to link it together in the RowSource of the report.

What about my proposal ?

Post the mathematical formulas that solve your issue and I'm sure that I'll be able to implement this in your report.

JHB · Jan 10, 2014

I've made a small example for you in the attached database, open the report "EvL Charge ReportJHB"
Remember you can't fetch any value before it has been "shown" in the report, else you need some Dloockup or code.

ReneeB · Jan 10, 2014

Good morning fellas --
It's going to be a bit before I can devote a little time to checking out your solution JHB - I have to observe a procedure that could take all day.....
Mihail - sorry didn't mean to miss answering your question. Here's my math:
from the detail: (grouped on month/year then early flag)
totals on Pts, Pharm, Room, & Totals for early
totals on Pts, Pharm, Room, & Totals for late
Total/Pts = Earlyavg
Total/Pts = Lateavg
Lateavg-Earlyavg = AvgDiff
Pts * AvgDiff = ImpliedSavings

Hope this helps and thanks so much for your help

Renee

Mihail · Jan 11, 2014

@JHB (and not only)
I tried to design Reenee's report but I fall myself in difficulty

Have you any idea/answer ? Why the red calculation(s) (see the report) have wrong result(s) ? Thank you.

PS:
I have normalized the DB in order to allow referential integrity

@ReneeB
I think that you must show us the entire DB.
I am sure that your DB is not normalized:
Database Normalization and Table Structures - Microsoft Access / VBA
So is hard (if not impossible) to accomplish your task.

ReneeB · Jan 12, 2014

Thank you JHB!! I see that I was trying to have the report do too much work, I should leave that task to the queries and have the report do what it does best, just report the data. Thanks for everything.

Mihail - JHB solved my problem but I appreciate your hard work on my behalf. I read your link, according to the definition, my db is normalized. I cannot show you the entire db as I will not only lose my job but I will compromise a lot of people's privacy.

Thanks again,
Renee

Mihail · Jan 12, 2014

ReneeB said:
Thank you JHB!! I see that I was trying to have the report do too much work, I should leave that task to the queries and have the report do what it does best, just report the data. Thanks for everything.

Mihail - JHB solved my problem but I appreciate your hard work on my behalf. I read your link, according to the definition, my db is normalized. I cannot show you the entire db as I will not only lose my job but I will compromise a lot of people's privacy.

Thanks again,
Renee

I am happy for you because you have a solution.

Still I think that your DB is not normalized but if it is useful for you, is OK.
BTW: When someone from here ask you to show the DB he don't ask for the DB with real data. We are interested only in the DB structure.
If you have the necessary time, take a look to this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=258024
Here, the poster, showed me his DB in respect of all the privacy. And it is about the German army

.

@JHB
Have you an answer for me (post #13) ?
Still I can't find an answer for this.
Of course that I can imagine an work around, but why that calculations don't work ??????
For sure I'll open a new thread for this question if, even you, can't find an answer here.

ReneeB · Jan 12, 2014

Thanks again. Have a wonderful day.

JHB · Jan 13, 2014

Mihail said:
@JHB
Have you an answer for me (post #13) ?
Still I can't find an answer for this.
Of course that I can imagine an work around, but why that calculations don't work ??????
For sure I'll open a new thread for this question if, even you, can't find an answer here.

Hello Mihail.
I have taken a quick look at it, but I don't know what cause this, (I don't like the result is different of what I imagine it should be).
If you have time, then try with lesser data, (insert the result in a table and delete some of the data), maybe then it is easier to understand what is going on!

Mihail · Jan 13, 2014

Thank you, JHB.
I'll try what you suggested, but for the life of me, I can't understand what is happen here.

plog · Jan 20, 2014

I found the fix. I can't explain why your DSUM doesn't work, but I did get the right answer when I enclosed the field you were summing with quote marks:

=DSum("[Pharm]","qryEvlCharges","[early]=" & [early])

When I put quote marks around [Pharm] it returned the correct value.

Galaxiom · Jan 20, 2014

Explanation in plog's question

Data correct on report view but not when printed (1 Viewer)

Registered User.

Registered User.

Registered User.

Attachments

Have been here a while

Registered User.

Registered User.

Attachments

Registered User.

Have been here a while

Registered User.

Registered User.

Have been here a while

Attachments

Registered User.

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Have been here a while

Registered User.

Banishment Pending

Super Moderator

Similar threads

Users who are viewing this thread