Solved

Access - Query with Cumulative Field - Performance question

Posted on 2014-12-02
11
175 Views
Last Modified: 2014-12-03
This is a follow on question to my earlier question today about creating a Cumulative Amt Field in a Query where I needed it to include all [Shifts] and all [TransTypeID]'s even if a subsequent Shift's reporting doesn't include a particular TransTypeID that was used in an earlier [Shift] on the business day.

My question now is the performance of that query.  Based upon how I have it, it churns for about 20-30 seconds before I get results and this is with my limited number of records thus far.

I am attaching the DB.  The query in question is [qry_XCumul].  

Since I want all TransTypes possible to be included I created a Cartesian qry for Shifts and TransTypes.... that query is [qry_AllTransTypeAllShifts]

I suspect that the issue is in my Cumulative calculation using DSum
the code is
SELECT qry_X.ShiftID, qry_X.BusDay, qry_X.TransTypeID, Val(nz(DSum("Amt","qry_X","ShiftID<=" & [qry_x].[ShiftID] & " AND TransTypeID=" & [qry_x].[TransTypeID] & " AND BusDay=#" & [qry_x].[BusDay] & "#"),0)) AS CumulAmt
FROM qry_X
GROUP BY qry_X.ShiftID, qry_X.BusDay, qry_X.TransTypeID, Val(nz(DSum("Amt","qry_X","ShiftID<=" & [qry_x].[ShiftID] & " AND TransTypeID=" & [qry_x].[TransTypeID] & " AND BusDay=#" & [qry_x].[BusDay] & "#"),0));

Open in new window


So, Is there a better way (ie: faster processing) that I could write that query???? Or would it be based on the fact that I have a Cartesian query being:
SELECT tblCtl_Shifts.ShiftID, tblCtl_Shifts.BusDay, sCtl_TransType.TransTypeID, sCtl_TransType.TransType
FROM tblCtl_Shifts, sCtl_TransType;

Open in new window


My qry_X code which the Cumulative query is based on is: (in case you don't want to open the DB....
SELECT qry_AllTransTypeAllShifts.ShiftID, qry_AllTransTypeAllShifts.BusDay, qry_AllTransTypeAllShifts.TransTypeID, Val(nz(Sum([LineItemNoTx]),0)) AS Amt, Val(nz(Sum([CSTax]),0)) AS TTx
FROM ((qry_AllTransTypeAllShifts LEFT JOIN (tbl_OtherPdOuts RIGHT JOIN tbl_CashFundActivityNoDenom ON tbl_OtherPdOuts.CashFdActNoDenomID=tbl_CashFundActivityNoDenom.CashFdActNoDenomID) ON (qry_AllTransTypeAllShifts.TransTypeID=tbl_CashFundActivityNoDenom.TransTypeID) AND (qry_AllTransTypeAllShifts.ShiftID=tbl_CashFundActivityNoDenom.ShiftID)) INNER JOIN sCtl_TransType ON qry_AllTransTypeAllShifts.TransTypeID=sCtl_TransType.TransTypeID) LEFT JOIN tbl_OtherPdOutsDetails ON tbl_OtherPdOuts.OtherPdOutsID=tbl_OtherPdOutsDetails.OtherPdOutsID
GROUP BY qry_AllTransTypeAllShifts.ShiftID, qry_AllTransTypeAllShifts.BusDay, qry_AllTransTypeAllShifts.TransTypeID;

Open in new window


THANK YOU IN ADVANCE for the suggestions!
Cumulative-TransType-By-Day.accdb
0
Comment
Question by:wlwebb
11 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40477603
Any of the Domain Aggregate functions are performance killers to be avoided whenever possible.  ESPECIALLY in the WHERE clause.
You are already using query results in the subsequent query from the look of things.  Can you not create queries that will avoid the use of such things?
Cartesian queries are performance killers as well.
I have one query that is actually the cascaded result of 4 queries.
It runs well.

I can't open an accdb here as I run Access 2003.
I may have a gander later on A2010

Are you adverse to having this done with VBA, and not in queries?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40477881
The Cartesian product I recommended in the other thread will be slow for more than a few dozen records.  But before blaming that I would get rid of the domain functions.  In all cases, you can create totals queries to replace the domain functions and then join to the totals query.  This allows the query engine to optimize the query.  Domain functions will run a separate query for each row so if the query is returning a thousand rows, it is running the domain function query a thousand times.  That is a lot of overhead.
0
 

Author Comment

by:wlwebb
ID: 40478291
Pat
To clarify .... Are you saying to first run a query that totals or Sums each Shifts data and then after that query have another query that joins as Cartesian to TransTypeIDs ???
0
 

Author Comment

by:wlwebb
ID: 40478377
Nick
Can you not create queries that will avoid the use of such things?

Thank you for your input.  The reason that I'm doing the query is this.  The results are used in reporting on a form and in a report not only that certain Shift's info but also for the entire selected business day through the end of that certain Shift.  The problem I had is that every shift doesn't have all TransTypeID's in their Shift's Data.  However, to report a Day-to-ShiftEnd column for Shift 2 of the day or Shift 3 of the day etc, if for example SHift 1 had a TransType 5 but Shift 2 or 3 didn't then Somehow I have to get a total of TransType 5 I need the Cumulative Amount of all TransType 5s from the Prior Shift.

I have saved this stripped down DB as a A2002-03 version and attached for your conveienence if you want to see.
Cumulative-TransType-By-Day-A2002-03-Ver
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40478391
hi wlwebb

just change the queries as shown in the attachment to get rid of the domain functions.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:wlwebb
ID: 40478395
bonjour-aut

No attachment....
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40478398
just for the case the upload does not work:

change qry_X to:

SELECT qry_X.ShiftID, qry_X.BusDay, qry_X.TransTypeID
FROM qry_X
GROUP BY qry_X.ShiftID, qry_X.BusDay, qry_X.TransTypeID;

Open in new window


and qry_XCumul to:

SELECT qry_AllTransTypeAllShifts.ShiftID, qry_AllTransTypeAllShifts.BusDay, qry_AllTransTypeAllShifts.TransTypeID, qry_X2.Amt, qry_X2.TTx
FROM qry_AllTransTypeAllShifts LEFT JOIN qry_X2 ON (qry_AllTransTypeAllShifts.BusDay = qry_X2.BusDay) AND (qry_AllTransTypeAllShifts.TransTypeID = qry_X2.TransTypeID);

Open in new window

0
 
LVL 18

Accepted Solution

by:
bonjour-aut earned 500 total points
ID: 40478408
0
 

Author Comment

by:wlwebb
ID: 40478421
bonjour-aut.
Copy and pasted .... get following

I'm getting "Circular Reference" when I changed qry_X

Then on qry_XCumul I get the error MS engine cannot find the input table or query 'qry_X2.
0
 

Author Comment

by:wlwebb
ID: 40478439
Just got the upload bonjour-aut.... looking at now....


I assume I dont need the Module for Date and Time Zones.....
0
 

Author Closing Comment

by:wlwebb
ID: 40478451
MUCH faster than my version!!!!!!  Thank you... Now I'll try to apply that theory to mine to see if I can understand and get it to work when I do it....

Thank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access query with left expression 9 31
Direct Mail software 4 43
Operation must use an updatable query 4 24
Qry for calculation on form, on click of Checkbox 3 5
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now