Help! Dmax taking forever in continuous form

[Sj8]

I am currently making a leave approval system in ms access. I have one table ALMASTER which pulls data of each users 6 choice end and start dates and in separate columns (1st choice start) and a column for each choice approval (1stapproval). I have a continuous form that is linked to the table and displays all six choice start and end dates and has a check box linked to the respective approval for each choice. I have a field called CC1 which I need to calculate the number of times the choice date range has already been approved (so the user cannot approve over a certain amount of ppl the same leave). Currently I am using a dmax expression which pulls the value according to a join query I created. The problem being it takes forever and I need it to be quick. It also needs to.be update when the user checks or unchecks an approval checkbox for each choice and each continuous form..any ideas?

My code is

=Dmax ("startcount", "ctapprovedfull", "[1cs] between [start] and [end] or [1ce] between [start] and [end]")

Where startcount is the count(), from the join query ctapprovedfull, that the start date on the form 1cs and end date on the form 1ce, are between the start and end dates from the joined query ctapprovedfull.

Any help would be appreciated as I am new to ms access and racking my brain on this.

[aytee111]

Create a query that does what you are looking for and puts the data on a local table. Then your query for the subform can link to that table.

[ssanfu]

Having a hard time visualizing the query, but maybe try a Totals query; it is way faster than the DMax() function. Might require two queries....

[Sj8]

I apologize in advance for my lack of knowledge on this, as I am new to ms access..
Every time the user checks/unchecks an approval for a leave choice I will need the count() of the similar leave choices to update in the query and local table. Will that still work and do you think it will be quicker than a dmax expression?

[Sj8]

My query works by creating a union on my ALMASTER table

SELECT [User],  [1stApproval] As Approved, [1st choice start] As Start, [1st choice start]+6 As End
 FROM [ALMaster]
where [1stApproval]=true;
 
 
UNION SELECT [User],  [1stApproval] As Approved, [1st choice start]+7 As Start, [1st choice start]+13 As End
 FROM [ALMaster]
where [1stApproval]=true and [1st choice end] - [1st choice start] >7 and [1st choice end] - [1st choice start] < 14;
 
 
UNION SELECT [User],  [1stApproval] As Approved, [1st choice start] +14 As Start, [1st choice start]+20 As End
 FROM [ALMaster]
where [1stApproval]=true and [1st choice end] - [1st choice start] > 14;
 
 
UNION SELECT [User],  [1stApproval] As Approved, [1st choice start] As Start, [1st choice start]+6 As End
 FROM [ALMaster]
where [1stApproval]=true and [1st choice end] - [1st choice start] > 14;
 
 
UNION SELECT [User],  [1stApproval] As Approved, [1st choice start]+7 As Start, [1st choice start]+13 As End
 FROM [ALMaster]
where [1stApproval]=true and [1st choice end] - [1st choice start] > 14;
 
 
 
 
 
 
UNION SELECT [User],  [2ndApproval] As Approved, [2nd choice start] As Start, [2nd choice start]+6 As End
 FROM [ALMaster]
where [2ndApproval]=true;
 
 
UNION SELECT [User],  [2ndApproval] As Approved, [2nd choice start]+7 As Start, [2nd choice start]+13 As End
 FROM [ALMaster]
where [2ndApproval]=true and [2nd choice end] - [2nd choice start] >7 and [2nd choice end] - [2nd choice start] < 14;
 
 
UNION SELECT [User],  [2ndApproval] As Approved, [2nd choice start] +14 As Start, [2nd choice start]+20 As End
 FROM [ALMaster]
where [2ndApproval]=true and [2nd choice end] - [2nd choice start] > 14;
 
 
UNION SELECT [User],  [2ndApproval] As Approved, [2nd choice start] As Start, [2nd choice start]+6 As End
 FROM [ALMaster]
where [2ndApproval]=true and [2nd choice end] - [2nd choice start] > 14;
 
 
UNION SELECT [User],  [2ndApproval] As Approved, [2nd choice start]+7 As Start, [2nd choice start]+13 As End
 FROM [ALMaster]
where [2ndApproval]=true and [2nd choice end] - [2nd choice start] > 14;
 
 
UNION SELECT [User],  [3rdApproval] As Approved, [3rd choice start] As Start, [3rd choice start]+6 As End
 FROM [ALMaster]
where [3rdApproval]=true;
 
 
UNION SELECT [User],  [3rdApproval] As Approved, [3rd choice start]+7 As Start, [3rd choice start]+13 As End
 FROM [ALMaster]
where [3rdApproval]=true and [3rd choice end] - [3rd choice start] >7 and [3rd choice end] - [3rd choice start] < 14;
 
 
UNION SELECT [User],  [3rdApproval] As Approved, [3rd choice start] +14 As Start, [3rd choice start]+20 As End
 FROM [ALMaster]
where [3rdApproval]=true and [3rd choice end] - [3rd choice start] > 14;
 
 
UNION SELECT [User],  [3rdApproval] As Approved, [3rd choice start] As Start, [3rd choice start]+6 As End
 FROM [ALMaster]
where [3rdApproval]=true and [3rd choice end] - [3rd choice start] > 14;
 
 
UNION SELECT [User],  [3rdApproval] As Approved, [3rd choice start]+7 As Start, [3rd choice start]+13 As End
 FROM [ALMaster]
where [3rdApproval]=true and [3rd choice end] - [3rd choice start] > 14;
 
 
 
 
 
 
UNION SELECT [User],  [4thApproval] As Approved, [4th choice start] As Start, [4th choice start]+6 As End
 FROM [ALMaster]
where [4thApproval]=true;
 
 
UNION SELECT [User],  [4thApproval] As Approved, [4th choice start]+7 As Start, [4th choice start]+13 As End
 FROM [ALMaster]
where [4thApproval]=true and [4th choice end] - [4th choice start] >7 and [4th choice end] - [4th choice start] < 14;
 
 
UNION SELECT [User],  [4thApproval] As Approved, [4th choice start] +14 As Start, [4th choice start]+20 As End
 FROM [ALMaster]
where [4thApproval]=true and [4th choice end] - [4th choice start] > 14;
 
 
UNION SELECT [User],  [4thApproval] As Approved, [4th choice start] As Start, [4th choice start]+6 As End
 FROM [ALMaster]
where [4thApproval]=true and [4th choice end] - [4th choice start] > 14;
 
 
UNION SELECT [User],  [4thApproval] As Approved, [4th choice start]+7 As Start, [4th choice start]+13 As End
 FROM [ALMaster]
where [4thApproval]=true and [4th choice end] - [4th choice start] > 14;
 
 
 
 
UNION SELECT [User],  [5thApproval] As Approved, [5th choice start] As Start, [5th choice start]+6 As End
 FROM [ALMaster]
where [5thApproval]=true;
 
 
UNION SELECT [User],  [5thApproval] As Approved, [5th choice start]+7 As Start, [5th choice start]+13 As End
 FROM [ALMaster]
where [5thApproval]=true and [5th choice end] - [5th choice start] >7 and [5th choice end] - [5th choice start] < 14;
 
 
UNION SELECT [User],  [5thApproval] As Approved, [5th choice start] +14 As Start, [5th choice start]+20 As End
 FROM [ALMaster]
where [5thApproval]=true and [5th choice end] - [5th choice start] > 14;
 
 
UNION SELECT [User],  [5thApproval] As Approved, [5th choice start] As Start, [5th choice start]+6 As End
 FROM [ALMaster]
where [5thApproval]=true and [5th choice end] - [5th choice start] > 14;
 
 
UNION SELECT [User],  [5thApproval] As Approved, [5th choice start]+7 As Start, [5th choice start]+13 As End
 FROM [ALMaster]
where [5thApproval]=true and [5th choice end] - [5th choice start] > 14;
 
 
 
 
 
 
UNION SELECT [User],  [6thApproval] As Approved, [6th choice start] As Start, [6th choice start]+6 As End
 FROM [ALMaster]
where [6thApproval]=true;
 
 
UNION SELECT [User],  [6thApproval] As Approved, [6th choice start]+7 As Start, [6th choice start]+13 As End
 FROM [ALMaster]
where [6thApproval]=true and [6th choice end] - [6th choice start] >7 and [6th choice end] - [6th choice start] < 14;
 
 
UNION SELECT [User],  [6thApproval] As Approved, [6th choice start] +14 As Start, [6th choice start]+20 As End
 FROM [ALMaster]
where [6thApproval]=true and [6th choice end] - [6th choice start] > 14;
 
 
UNION SELECT [User],  [6thApproval] As Approved, [6th choice start] As Start, [6th choice start]+6 As End
 FROM [ALMaster]
where [6thApproval]=true and [6th choice end] - [6th choice start] > 14;
 
 
UNION SELECT [User],  [6thApproval] As Approved, [6th choice start]+7 As Start, [6th choice start]+13 As End
 FROM [ALMaster]
where [6thApproval]=true and [6th choice end] - [6th choice start] > 14;
SELECT [User],  [1stApproval] As Approved, [1st choice start] As Start, [1st choice start]+6 As End
 FROM [ALMaster]
where [1stApproval]=true;
 
 
UNION SELECT [User],  [1stApproval] As Approved, [1st choice start]+7 As Start, [1st choice start]+13 As End
 FROM [ALMaster]
where [1stApproval]=true and [1st choice end] - [1st choice start] >7 and [1st choice end] - [1st choice start] < 14;
 
 
UNION SELECT [User],  [1stApproval] As Approved, [1st choice start] +14 As Start, [1st choice start]+20 As End
 FROM [ALMaster]
where [1stApproval]=true and [1st choice end] - [1st choice start] > 14;
 
 
UNION SELECT [User],  [1stApproval] As Approved, [1st choice start] As Start, [1st choice start]+6 As End
 FROM [ALMaster]
where [1stApproval]=true and [1st choice end] - [1st choice start] > 14;
 
 
UNION SELECT [User],  [1stApproval] As Approved, [1st choice start]+7 As Start, [1st choice start]+13 As End
 FROM [ALMaster]
where [1stApproval]=true and [1st choice end] - [1st choice start] > 14;
 
 
 
 
 
 
UNION SELECT [User],  [2ndApproval] As Approved, [2nd choice start] As Start, [2nd choice start]+6 As End
 FROM [ALMaster]
where [2ndApproval]=true;
 
 
UNION SELECT [User],  [2ndApproval] As Approved, [2nd choice start]+7 As Start, [2nd choice start]+13 As End
 FROM [ALMaster]
where [2ndApproval]=true and [2nd choice end] - [2nd choice start] >7 and [2nd choice end] - [2nd choice start] < 14;
 
 
UNION SELECT [User],  [2ndApproval] As Approved, [2nd choice start] +14 As Start, [2nd choice start]+20 As End
 FROM [ALMaster]
where [2ndApproval]=true and [2nd choice end] - [2nd choice start] > 14;
 
 
UNION SELECT [User],  [2ndApproval] As Approved, [2nd choice start] As Start, [2nd choice start]+6 As End
 FROM [ALMaster]
where [2ndApproval]=true and [2nd choice end] - [2nd choice start] > 14;
 
 
UNION SELECT [User],  [2ndApproval] As Approved, [2nd choice start]+7 As Start, [2nd choice start]+13 As End
 FROM [ALMaster]
where [2ndApproval]=true and [2nd choice end] - [2nd choice start] > 14;
 
 
UNION SELECT [User],  [3rdApproval] As Approved, [3rd choice start] As Start, [3rd choice start]+6 As End
 FROM [ALMaster]
where [3rdApproval]=true;
 
 
UNION SELECT [User],  [3rdApproval] As Approved, [3rd choice start]+7 As Start, [3rd choice start]+13 As End
 FROM [ALMaster]
where [3rdApproval]=true and [3rd choice end] - [3rd choice start] >7 and [3rd choice end] - [3rd choice start] < 14;
 
 
UNION SELECT [User],  [3rdApproval] As Approved, [3rd choice start] +14 As Start, [3rd choice start]+20 As End
 FROM [ALMaster]
where [3rdApproval]=true and [3rd choice end] - [3rd choice start] > 14;
 
 
UNION SELECT [User],  [3rdApproval] As Approved, [3rd choice start] As Start, [3rd choice start]+6 As End
 FROM [ALMaster]
where [3rdApproval]=true and [3rd choice end] - [3rd choice start] > 14;
 
 
UNION SELECT [User],  [3rdApproval] As Approved, [3rd choice start]+7 As Start, [3rd choice start]+13 As End
 FROM [ALMaster]
where [3rdApproval]=true and [3rd choice end] - [3rd choice start] > 14;
 
 
 
 
 
 
UNION SELECT [User],  [4thApproval] As Approved, [4th choice start] As Start, [4th choice start]+6 As End
 FROM [ALMaster]
where [4thApproval]=true;
 
 
UNION SELECT [User],  [4thApproval] As Approved, [4th choice start]+7 As Start, [4th choice start]+13 As End
 FROM [ALMaster]
where [4thApproval]=true and [4th choice end] - [4th choice start] >7 and [4th choice end] - [4th choice start] < 14;
 
 
UNION SELECT [User],  [4thApproval] As Approved, [4th choice start] +14 As Start, [4th choice start]+20 As End
 FROM [ALMaster]
where [4thApproval]=true and [4th choice end] - [4th choice start] > 14;
 
 
UNION SELECT [User],  [4thApproval] As Approved, [4th choice start] As Start, [4th choice start]+6 As End
 FROM [ALMaster]
where [4thApproval]=true and [4th choice end] - [4th choice start] > 14;
 
 
UNION SELECT [User],  [4thApproval] As Approved, [4th choice start]+7 As Start, [4th choice start]+13 As End
 FROM [ALMaster]
where [4thApproval]=true and [4th choice end] - [4th choice start] > 14;
 
 
 
 
UNION SELECT [User],  [5thApproval] As Approved, [5th choice start] As Start, [5th choice start]+6 As End
 FROM [ALMaster]
where [5thApproval]=true;
 
 
UNION SELECT [User],  [5thApproval] As Approved, [5th choice start]+7 As Start, [5th choice start]+13 As End
 FROM [ALMaster]
where [5thApproval]=true and [5th choice end] - [5th choice start] >7 and [5th choice end] - [5th choice start] < 14;
 
 
UNION SELECT [User],  [5thApproval] As Approved, [5th choice start] +14 As Start, [5th choice start]+20 As End
 FROM [ALMaster]
where [5thApproval]=true and [5th choice end] - [5th choice start] > 14;
 
 
UNION SELECT [User],  [5thApproval] As Approved, [5th choice start] As Start, [5th choice start]+6 As End
 FROM [ALMaster]
where [5thApproval]=true and [5th choice end] - [5th choice start] > 14;
 
 
UNION SELECT [User],  [5thApproval] As Approved, [5th choice start]+7 As Start, [5th choice start]+13 As End
 FROM [ALMaster]
where [5thApproval]=true and [5th choice end] - [5th choice start] > 14;
 
 
 
 
 
 
UNION SELECT [User],  [6thApproval] As Approved, [6th choice start] As Start, [6th choice start]+6 As End
 FROM [ALMaster]
where [6thApproval]=true;
 
 
UNION SELECT [User],  [6thApproval] As Approved, [6th choice start]+7 As Start, [6th choice start]+13 As End
 FROM [ALMaster]
where [6thApproval]=true and [6th choice end] - [6th choice start] >7 and [6th choice end] - [6th choice start] < 14;
 
 
UNION SELECT [User],  [6thApproval] As Approved, [6th choice start] +14 As Start, [6th choice start]+20 As End
 FROM [ALMaster]
where [6thApproval]=true and [6th choice end] - [6th choice start] > 14;
 
 
UNION SELECT [User],  [6thApproval] As Approved, [6th choice start] As Start, [6th choice start]+6 As End
 FROM [ALMaster]
where [6thApproval]=true and [6th choice end] - [6th choice start] > 14;
 
 
UNION SELECT [User],  [6thApproval] As Approved, [6th choice start]+7 As Start, [6th choice start]+13 As End
 FROM [ALMaster]
where [6thApproval]=true and [6th choice end] - [6th choice start] > 14;

Having a hard time visualizing the query, but maybe try a Totals query; it is way faster than the DMax() function. Might require two queries....

[aytee111]

Of course if your tables were normalized none of this would be an issue!

[Sj8]

From what I just researched about normalized tables, (as I mentioned before I am new to access), I can create two tables: one for employee and another for leave choice; however I still run into the same issue when trying to find the count() of the number of choices that are approved and within the same date range. I am not sure if I am missing a step to accomplish this.

Of course if your tables were normalized none of this would be an issue!

[aytee111]

This is a complicated one! I am sure that if you are getting it to work the way you want then go with it.

[Sj8]

my sentiments exactly, I don't know another solution, but really need to make the form load faster every time a checkbox is changed. It works fine as is, but I only have 1/7 the amount of data I will eventually have and it is already taking anywhere from 15 to 30 seconds to load the continuous form. Thank you for your help though, I truly appreciate it!

This is a complicated one! I am sure that if you are getting it to work the way you want then go with it.

[aytee111]

If you want to you can post a database, just with the pertinent bits (the one table with only these fields), that we can play around with.

[ssanfu]

I agree with aytee111, it would help if you were to post your dB.


My recommendation was for the form record source; I didn't know you had a union query.



BTW,
"Start" and "End" are reserved words in Access and shouldn't be used as object names. "dteStart" and "dteEnd" would be better names.
Shouldn't have spaces in object names. (nor special characters except the underscore or punctuation)
Shouldn't begin a name with a number ("1stApproval" should be "Approval1")

[rpeare]

The problem is that domain functions take forever to run and chew up memory as your dataset gets larger. You really need to avoid using it in a query altogether.

Just as an example.

Let's say your dataset is 5 records:

tblMyDataSet
ID  DateField  QtyField  
1   1/1/2016   5
2   1/2/2016   10
3   1/3/2016   7 
4   1/4/2016   4
5   1/5/2016   3

If you use a DSUM function (or any domain function) to sum the QTYFIELD for all dates on or prior to the date in the DATEFIELD you would be processing your entire dataset at least 6 times, 1 time for the main query and 5 additional times for each domain function on each record, you are effectively multiplying your processing time by a factor of 5. If you have 1000 records, you're multiplying it by 1000 for each domain function. As others have mentioned, I would really strongly suggest you look at changing your structure.

In the absence of that you will likely want a series of subqueries to do the totalling for you then link those queries back to your 'main' query as ssnafu suggested.

[Sj8]

Once I normalize the data how can I display a count for the date range that updates every time a checkbox is changed? I'm not sure how to go about using subqueries to accomplish this.Thank you all for the other tips, not being familiar with proper naming etiquette is another downfall to not having a background in access, but now that I know, I will ensure to follow those rules! Thanks!

The problem is that domain functions take forever to run and chew up memory as your dataset gets larger. You really need to avoid using it in a query altogether.

Just as an example.

Let's say your dataset is 5 records:

tblMyDataSet
ID  DateField  QtyField  
1   1/1/2016   5
2   1/2/2016   10
3   1/3/2016   7 
4   1/4/2016   4
5   1/5/2016   3

If you use a DSUM function (or any domain function) to sum the QTYFIELD for all dates on or prior to the date in the DATEFIELD you would be processing your entire dataset at least 6 times, 1 time for the main query and 5 additional times for each domain function on each record, you are effectively multiplying your processing time by a factor of 5. If you have 1000 records, you're multiplying it by 1000 for each domain function. As others have mentioned, I would really strongly suggest you look at changing your structure.

In the absence of that you will likely want a series of subqueries to do the totalling for you then link those queries back to your 'main' query as ssnafu suggested.