To Query or to VBA and threading?

[John T Ingato]

I have three tables: tblStores, tblWeeklyInventoryCounts, tblProductList,
qryReportDates

The tblStores contains a listing of all stores that are currently open and
which should be reporting data each week
The tblProductList contain a list of products that should be in every open
store
The qryReportDates is a list of distinct dates in the
tblWeeklyInventoryCounts table
The tblweeklyInventoryCounts is a compilation of weekly data that is
imported in from excel every week. It contains the inventory level for each
product and for every store at the close of the week. The key fields being:
ReportDate, StoreNumber, ProductID, OnHandQty

My goal was to create a table of items that were sold at each store for each
week by subtracting last weeks (this weeks inventory level) - (last weeks
inventory level). I could not figure out how to do this in a query so I
decided to write the VBA code. Is there a way to perform this in a query
environment.

I was successful in creating the code and creating the table. The code also
affords me the ability to check whether this weeks inventory is greater then
last week, showing that the store must have received some product. Instead
of sales reflecting a negative amount, I can assign 0 sales.

The one problem is that the code take approx 30-40 minutes to run on 800,000
records. while it is running, I can not do anything else on my computer.
It seems to have full control. If I do try to do something else, the code
seems to freeze up or the cursor in the code window turn to an hourglass. I
have to ctrl+break / debug / and F5 to start it again. This happen if the
laptop goes into sleep mode also.

Is there a way to run this procedure in it own thread? Or to allow it to run
in the background? Or to stop the computer from entering stand-by?

 

[taurus]

Hi, if you use the tblweeklyInventoryCounts to create a query
qryInventoryChangeLastWeek:

SELECT a.storeNumber, a.productId, a.reportDate AS [from], b.reportDate AS
[to], a.onHandQty-b.onHandQty AS change
FROM tblWeeklyInventoryCounts AS a INNER JOIN tblWeeklyInventoryCounts AS b
ON (a.storeNumber = b.storeNumber) AND (a.productId = b.productId)
WHERE (((b.reportDate) In (select Max([reportDate]) from
tblWeeklyInventoryCounts)))
GROUP BY a.storeNumber, a.productId, a.reportDate, b.reportDate, a.onHandQty-
b.onHandQty
HAVING ((.[reportDate]-[a].[reportDate]=7));

and / or if you want the historical changes a query qryInventoryChangeHistory:


SELECT a.storeNumber, a.productId, a.reportDate AS [from], b.reportDate AS
[to], [a].[onHandQty]-.[onHandQty] AS change
FROM tblWeeklyInventoryCounts AS a INNER JOIN tblWeeklyInventoryCounts AS b
ON (a.productId = b.productId) AND (a.storeNumber = b.storeNumber)
WHERE (((b.reportDate)>[a].[reportDate]));

should work but I have not checked it against that many records.

Why would you want to assign 0 sales when inventory increases?

Hope this helps.

 

[John T Ingato]

I will answer your question, "Why would you want to assign 0 when inventory
increases?" the best that I can.

If inventory increases during a period, it will obviously show a negative
sales figure. Since I have no purchase order information to track against, I
do not have the option of factoring out the quantity that was received by a
given store.

My main goal in all of this is to be able to calculate "average sale per
week, per store, per item", so I can see product performances and weigh
inventory levels against the movement data. e.g. If a given stores "average
sales per week" for a given item is 3, and the store only has 11 in stock, I
know that they have enough product for 3.8 weeks.

 

[taurus via AccessMonster.com]

Okay, I was just wondering what would happen if the previous inventory was 90
units, followed by a sale of same amount and a goods receival of same amount.
Did the queries run any faster than the vba?

 

[Smartin]

I have three tables: tblStores, tblWeeklyInventoryCounts, tblProductList,
qryReportDates

The tblStores contains a listing of all stores that are currently open and
which should be reporting data each week
The tblProductList contain a list of products that should be in every open
store
The qryReportDates is a list of distinct dates in the
tblWeeklyInventoryCounts table
The tblweeklyInventoryCounts is a compilation of weekly data that is
imported in from excel every week. It contains the inventory level for each
product and for every store at the close of the week. The key fields being:
ReportDate, StoreNumber, ProductID, OnHandQty

My goal was to create a table of items that were sold at each store for each
week by subtracting last weeks (this weeks inventory level) - (last weeks
inventory level). I could not figure out how to do this in a query so I
decided to write the VBA code. Is there a way to perform this in a query
environment.

I was successful in creating the code and creating the table. The code also
affords me the ability to check whether this weeks inventory is greater then
last week, showing that the store must have received some product. Instead
of sales reflecting a negative amount, I can assign 0 sales.

The one problem is that the code take approx 30-40 minutes to run on 800,000
records. while it is running, I can not do anything else on my computer.
It seems to have full control. If I do try to do something else, the code
seems to freeze up or the cursor in the code window turn to an hourglass. I
have to ctrl+break / debug / and F5 to start it again. This happen if the
laptop goes into sleep mode also.

Is there a way to run this procedure in it own thread? Or to allow it to run
in the background? Or to stop the computer from entering stand-by?

Without knowing what your code is doing I might suggest that some
judiciously placed "DoEvents" statements might alleviate some of the pain.

You might also try a third party app such as the excellent "Process
Explorer" (formerly published by Sysinternals, recently acquired by
Microsoft) which allows you to tune processor priority settings (much
better than Task Manager!) as well as processor affinity if you have a
multiprocessor system.

OTOH if your code has SQL calls that take forever, you may be SOL.