wlwebb
asked on
Access Report - Calculates and Calculates and Calculates and.........
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_LVLDetailEODSinceL astPullEac hMach) 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
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_LVLDetailEODSinceL
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
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
If I change the command to:
DoCmd.OpenReport "sRpt_MachPull_Reconcile",
it prints instantly.
All you may need is to compile and run a compact and repair.
/gustav
ASKER
eghtebas & Gustav
Thank you for the responses. I think my problem is in the query
qry_LVLDetailEODSinceLastP ullEachMac h
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.
Thank you for the responses. I think my problem is in the query
qry_LVLDetailEODSinceLastP
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.
ASKER
The SQL code for that qry_LVLDetailEODSinceLastP ullEachMac h 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;
You should (always) remove any ordering from the report queries.
Use the sorting and grouping in the report itself.
/gustav
Use the sorting and grouping in the report itself.
/gustav
ASKER
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_LVLDet ailEODSinc eLastPullE achMach"," [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?
Originally (in the upload) my formula in those fields was "DSum("[1],"cTabqry_LVLDet
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the input....
You are welcome!
/gustav
/gustav
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