Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Report - Calculates and Calculates and Calculates and.........

Posted on 2014-11-15
9
Medium Priority
?
196 Views
Last Modified: 2014-11-16
Hello all
I've been away for awhile.  

I have a Report which will have several SubReports.  The primary SubReport is where I'm struggling.  It is based on a CrossTab query.  When I Open the Report it keeps recalculating and Recalculating and..... you get the idea.....

Anyway.

To see what I mean I've attached a stripped down db.  

To see what I mean and to open the Report.... Open frm_Testing.  Then in the "Select ShiftID" combobox select Shift Number 432.
Then click the Command Button to Open the Report.... You'll see what I mean......

I suspect that it has to do with the way I designed either the Crosstab query (which is cTabqry_LVLDetailEODSinceLastPullEachMach) OR it's one of the Queries that that CrossTab is based upon.....

I've opened and run each of the Queries and they all run fine but the Opening of the Report keeps calculating.... I'm stumped (the query doesn't keep calculating like that when I Run it??????????)

THANKING you Experts in Advance for any suggestions!!!
PullRecsSinceLastEvent.accdb
0
Comment
Question by:wlwebb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40445123
In the past, I had a similar project and the report was taking about 30 minutes to be populated. I shortened it to instant response by making a temp table where all the calculated values were ready to be consumed,

This partially depends on the nature of the business. In my case we were dealing with historical data. Once it was calculated and kept in a temp table, it was good and valid data. Of course we were able once in a while refresh it overnight or on demand.

Refresh could include all of the data or surgically it could include one customer or a single day, etc.

I have to end may 10 hour work since this morning and run to the gym for a while. See if my suggestion is applicable in your case.

Mike
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40445396
If I click the button, the report opens in 3-4 seconds.
If I change the command to:

    DoCmd.OpenReport "sRpt_MachPull_Reconcile", acViewNormal, "", "", acNormal

it prints instantly.

All you may need is to compile and run a compact and repair.

/gustav
0
 

Author Comment

by:wlwebb
ID: 40445566
eghtebas & Gustav
Thank you for the responses.  I think my problem is in the query
qry_LVLDetailEODSinceLastPullEachMach

I have a field that is named [RptgShiftID] .  Since the data being selected to report on was actually input by a previous shift(s) but is needed to reconcile this shift's info reporting I have to populate a contrived field that I named [RptgShiftID].  To populate that field in the query, within the query I am calling the Public Variable that's in LVLSysVariable_Module GetlngMyShiftID.

If I have Visual Basic open I can see it flashing "Running" over and over again and it's like it's caught in a loop.  The only thing It is calling from what I can tell is that GetlngMyShiftID.

Any thoughts?????

PS.... Gustav.... I have the option set to always compact and repair on close so that should be being done every time I'm closing.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:wlwebb
ID: 40445571
The SQL code for that qry_LVLDetailEODSinceLastPullEachMach query is

SELECT qry_LVLDetailsByShift.LVLInfoDtailID, qry_LVLDetailsByShift.LVLInfoID, qry_LVLDetailsByShift.LVLMachPollPositionID, qry_LVLDetailsByShift.LVLMachinePoll, qry_LVLDetailsByShift.AmtIn, qry_LVLDetailsByShift.AmtVal, qry_LVLDetailsByShift.ShiftID, qry_LVLDetailsByShift.ShiftTypeID, qry_LVLDetailsByShift.DayShiftSeqByType, qry_LVLDetailsByShift.BusDay, Val(GetlngMyShiftID()) AS RptgShiftID
FROM qry_LVLDetailsByShift
WHERE (((qry_LVLDetailsByShift.LVLInfoDtailID)>DLookUp("MaxofLVLInfoDtailID","qry_LastLVLDtailMachPullEachMach","LVLMachPollPositionID=" & [LVLMachPollPositionID])) AND ((qry_LVLDetailsByShift.ShiftTypeID)=3))
GROUP BY qry_LVLDetailsByShift.LVLInfoDtailID, qry_LVLDetailsByShift.LVLInfoID, qry_LVLDetailsByShift.LVLMachPollPositionID, qry_LVLDetailsByShift.LVLMachinePoll, qry_LVLDetailsByShift.AmtIn, qry_LVLDetailsByShift.AmtVal, qry_LVLDetailsByShift.ShiftID, qry_LVLDetailsByShift.ShiftTypeID, qry_LVLDetailsByShift.DayShiftSeqByType, qry_LVLDetailsByShift.BusDay, Val(GetlngMyShiftID())
HAVING (((qry_LVLDetailsByShift.DayShiftSeqByType)=DMax("DayShiftSeqByType","qry_LVLDetailsByShift","BusDay=#" & [BusDay] & "# AND LVLMachPollPositionID=" & [LVLMachPollPositionID])))
ORDER BY qry_LVLDetailsByShift.LVLMachPollPositionID;

Open in new window

0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40445577
You should (always) remove any ordering from the report queries.
Use the sorting and grouping in the report itself.

/gustav
0
 

Author Comment

by:wlwebb
ID: 40445581
Just made a change in the SubReport.......  In my footer I have Unbound Totals.....

Originally (in the upload) my formula in those fields was "DSum("[1],"cTabqry_LVLDetailEODSinceLastPullEachMach","[1]") etc...

I just changed those fields to =Sum([1]) etc....

It isn't recalculating all the time.....

Help me here.... I thought functions to access the data were faster than calculating based upon info on the form itself.??????? Am I wrong in what I understood?
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40445620
Yes, Sum is faster as it uses data already present while DSum will start from scratch.

/gustav
0
 

Author Closing Comment

by:wlwebb
ID: 40445698
Thanks for the input....
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40445725
You are welcome!

/gustav
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question