Query run crashes Access (1 Viewer)

mishash

Member
Local time
Today, 11:11
Joined
Aug 20, 2020
Messages
52
qrySalesCount counts products sales (the FO part of FIFO):
Code:
SELECT T1.ProductID, tblProduct.ProductName, T1.OrderID, tblOrder.OrderDate, T1.Quantity, T1.UnitCost,
           (SELECT Sum(tblOrderDetails.Quantity) AS Total FROM tblOrderDetails
            WHERE tblOrderDetails.OrderDetailsID <= T1.OrderDetailsID and tblOrderDetails.ProductID=t1.ProductID) AS Total
FROM tblProduct INNER JOIN (tblOrder INNER JOIN tblOrderDetails AS T1 ON tblOrder.OrderID = T1.OrderID) ON tblProduct.ProductID = T1.ProductID
Now I am tying to workout the FI part of FIFO. I created new query qryFIFO with qrySalesCount and tblPurchaseDetails linked on ProductID field.
But when I run it it crashes Access. Tried compacting and also importing all objects into a new blank database = did not help.
Without going into qryFIFO's code and logic (I have not finished it yet), what might be the problem? Are there in Access built-in query limits that cause crash when you try to query something not logical or to heavy?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:11
Joined
May 7, 2009
Messages
19,169
chop-chop your query into smaller queries.
later, join them into a single query.

use layout for sure shot, don't slamdunk!
 

mishash

Member
Local time
Today, 11:11
Joined
Aug 20, 2020
Messages
52
chop-chop your query into smaller queries.
later, join them into a single query.

use layout for sure shot, don't slamdunk!
Which query needs to be chopped and what "use layout' means in query designing?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:11
Joined
May 7, 2009
Messages
19,169
from the initial structure of your query, you are trying to do a RunningTotal.
summarize first the OrderDetail.
 

Isaac

Lifelong Learner
Local time
Today, 02:11
Joined
Mar 14, 2017
Messages
8,738
Generally you want to try anything you can for your SQL to use only set-based processing, rather than RBAR (row by agonizing row) processing. Your subquery is the latter. Doing a grouped query first, then joining another query to THAT is set-based.
 

mishash

Member
Local time
Today, 11:11
Joined
Aug 20, 2020
Messages
52
Generally you want to try anything you can for your SQL to use only set-based processing, rather than RBAR (row by agonizing row) processing. Your subquery is the latter. Doing a grouped query first, then joining another query to THAT is set-based.
New concepts for me, I will google it out.
I don't really know how to implement suggested approach for running total, would appreciate some tip.
In general, my query runs smoothly by itself, but crushes when used in another query. What is the technical reason?
 

Isaac

Lifelong Learner
Local time
Today, 02:11
Joined
Mar 14, 2017
Messages
8,738
Maybe you can do a Totals query (sum on required field), listing ID # and Sum.
Then in the other query, get rid of the subquery column, and instead, Show Table > your Totals query, and join to that on ID.
 

mishash

Member
Local time
Today, 11:11
Joined
Aug 20, 2020
Messages
52
Well, I appreciate the inputs of arnelgp and Isaac, but my tread remained unsolved: the running total solution and the reason for crushing when using my quite simple query. I'd really appreciate more to-the-point assistance.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:11
Joined
Feb 28, 2001
Messages
26,996
Do you get an obvious error message?

If not, then consider looking at your system logs. Get there via settings, search for "control panel" then from control panel >> administrative tools >> event viewer. From there search for any logs for the time of day of the crash (because system event logs are time-tagged.) From there, see if any log reports an error in MSACCESS.EXE. If so, see what it says, and if there is an error code, let us know what it is.
 

Isaac

Lifelong Learner
Local time
Today, 02:11
Joined
Mar 14, 2017
Messages
8,738
Are you using an accde, mde, or accdr ?
 

mishash

Member
Local time
Today, 11:11
Joined
Aug 20, 2020
Messages
52
Do you get an obvious error message?

If not, then consider looking at your system logs. Get there via settings, search for "control panel" then from control panel >> administrative tools >> event viewer. From there search for any logs for the time of day of the crash (because system event logs are time-tagged.) From there, see if any log reports an error in MSACCESS.EXE. If so, see what it says, and if there is an error code, let us know what it is.
Not obvious
 

Attachments

  • 111111.jpg
    111111.jpg
    23.6 KB · Views: 436
  • 222.jpg
    222.jpg
    31.9 KB · Views: 457

Isaac

Lifelong Learner
Local time
Today, 02:11
Joined
Mar 14, 2017
Messages
8,738
Those VAT fields, are any of them supported by VBA functions?
 

Isaac

Lifelong Learner
Local time
Today, 02:11
Joined
Mar 14, 2017
Messages
8,738
Can you post a copy (or sanitized copy) of your db?

On your end, if me, my next step would probably be to remove components of the scenario one by one to see when or if your overall experience with the error changes. The quickest thing might be to either 1) remove some of the queries being joined, or 2) remove all records from one or more source tables. See if you still get the result and if so it gives the chance to ponder further.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:11
Joined
Feb 19, 2013
Messages
16,553
I've presented your code in a different way

Code:
SELECT T1.ProductID, tblProduct.ProductName, T1.OrderID, tblOrder.OrderDate, T1.Quantity, T1.UnitCost, T2.Total
          
FROM
(
(tblOrder
    INNER JOIN tblOrderDetails AS T1 
        ON tblOrder.OrderID = T1.OrderID) 
    INNER JOIN tblProduct 
        ON tblProduct.ProductID = T1.ProductID
)
    INNER JOIN (SELECT ProductID, Sum(.Quantity) AS Total FROM tblOrderDetails GROUP BY ProductID) AS T2 
        ON T2.OrderDetailsID <= T1.OrderDetailsID and T2.ProductID=t1.ProductID

Because of the none standard join ( T2.OrderDetailsID <= T1.OrderDetailsID) it can't be viewed in the query builder. Change it to =, then you can, just remember to change it back before saving the query

with regards your error, I would check your data - it may be you have a corrupt record
 

mishash

Member
Local time
Today, 11:11
Joined
Aug 20, 2020
Messages
52
I've presented your code in a different way

Code:
SELECT T1.ProductID, tblProduct.ProductName, T1.OrderID, tblOrder.OrderDate, T1.Quantity, T1.UnitCost, T2.Total
         
FROM
(
(tblOrder
    INNER JOIN tblOrderDetails AS T1
        ON tblOrder.OrderID = T1.OrderID)
    INNER JOIN tblProduct
        ON tblProduct.ProductID = T1.ProductID
)
    INNER JOIN (SELECT ProductID, Sum(.Quantity) AS Total FROM tblOrderDetails GROUP BY ProductID) AS T2
        ON T2.OrderDetailsID <= T1.OrderDetailsID and T2.ProductID=t1.ProductID

Because of the none standard join ( T2.OrderDetailsID <= T1.OrderDetailsID) it can't be viewed in the query builder. Change it to =, then you can, just remember to change it back before saving the query

with regards your error, I would check your data - it may be you have a corrupt record
OrderDetailsID missing in the T2 setting and I am nor sure how to fix it (when adding to SELECT and GROUP BY it results in multiplying the same lines)
 

mishash

Member
Local time
Today, 11:11
Joined
Aug 20, 2020
Messages
52
Do you get an obvious error message?

If not, then consider looking at your system logs. Get there via settings, search for "control panel" then from control panel >> administrative tools >> event viewer. From there search for any logs for the time of day of the crash (because system event logs are time-tagged.) From there, see if any log reports an error in MSACCESS.EXE. If so, see what it says, and if there is an error code, let us know what it is.
ACECORE.DLL is the culprit. The question is whether to try and update this DLL or to workout my query.
Eventually I am looking for the simplest FIFO inventory solution that will track the count of sold products and replace the cost of purchase' value in order details accordingly, so I can calculate cost and profit of every transaction and cost of inventory in stock.

Event 1000, Application Error
General
Faulting application name: MSACCESS.EXE, version: 14.0.7256.5000, time stamp: 0x5f125152
Faulting module name: ACECORE.DLL, version: 14.0.7248.5000, time stamp: 0x5e7bdeb5
Exception code: 0xc0000005
Fault offset: 0x00122181
Faulting process id: 0xb34
Faulting application start time: 0x01d69e873855d371
Faulting application path: C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE
Faulting module path: C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\ACECORE.DLL
Report Id: 74194a2e-0a7c-11eb-83bc-d07e358b06a9
Faulting package full name:
Faulting package-relative application ID:

Details
- <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
- <System>
<Provider Name="Application Error" />
<EventID Qualifiers="0">1000</EventID>
<Level>2</Level>
<Task>100</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2020-10-09T22:12:01.000000000Z" />
<EventRecordID>2434212</EventRecordID>
<Channel>Application</Channel>
<Computer>Mishanya</Computer>
<Security />
</System>
- <EventData>
<Data>MSACCESS.EXE</Data>
<Data>14.0.7256.5000</Data>
<Data>5f125152</Data>
<Data>ACECORE.DLL</Data>
<Data>14.0.7248.5000</Data>
<Data>5e7bdeb5</Data>
<Data>c0000005</Data>
<Data>00122181</Data>
<Data>b34</Data>
<Data>01d69e873855d371</Data>
<Data>C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE</Data>
<Data>C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\ACECORE.DLL</Data>
<Data>74194a2e-0a7c-11eb-83bc-d07e358b06a9</Data>
<Data />
<Data />
</EventData>
</Event>
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 09:11
Joined
Feb 19, 2013
Messages
16,553
ah, my bad

Code:
SELECT T1.ProductID, tblProduct.ProductName, T1.OrderID, tblOrder.OrderDate, T1.Quantity, T1.UnitCost, T2.Total
        
FROM
(
(tblOrder
    INNER JOIN tblOrderDetails AS T1
        ON tblOrder.OrderID = T1.OrderID)
    INNER JOIN tblProduct
        ON tblProduct.ProductID = T1.ProductID
)
    INNER JOIN (SELECT ProductID, OrderDetailsID, Sum(.Quantity) AS Total FROM tblOrderDetails GROUP BY ProductID , OrderDetailsID) AS T2
        ON T2.OrderDetailsID <= T1.OrderDetailsID and T2.ProductID=t1.ProductID
 

mishash

Member
Local time
Today, 11:11
Joined
Aug 20, 2020
Messages
52
ah, my bad

Code:
SELECT T1.ProductID, tblProduct.ProductName, T1.OrderID, tblOrder.OrderDate, T1.Quantity, T1.UnitCost, T2.Total
     
FROM
(
(tblOrder
    INNER JOIN tblOrderDetails AS T1
        ON tblOrder.OrderID = T1.OrderID)
    INNER JOIN tblProduct
        ON tblProduct.ProductID = T1.ProductID
)
    INNER JOIN (SELECT ProductID, OrderDetailsID, Sum(.Quantity) AS Total FROM tblOrderDetails GROUP BY ProductID , OrderDetailsID) AS T2
        ON T2.OrderDetailsID <= T1.OrderDetailsID and T2.ProductID=t1.ProductID
As I said, it results in Cartesian multiplication (T2.OrderDetailsID times) of lines and does not give running total.
My DB is quite small - less than 3000 lines of OrderDetailsID - so my original running total query runs fine and fast. It only crashes when used in another query - and I don't understand why.
 

Users who are viewing this thread

Top Bottom