Append Query Extremely Slow

[Cullihall]

Hey guys.

If someone can find the time would they please have a look at the database I have attached here?

What it does is calculate average cases shipped for each day of the week from 4 tables for each day which contain an average or 15,000 records each. There are queries that also has to calculate what we call master cases for each day because some things are shipped as a master case or the master case is opened and individual units are shipped (This is identified as TRUE in the 'Break' field of the transactions tables).

In a nut shell I want to calculate the averages for each day for all products in the t_ItemSearch table (always has about 35,000 records) and put it into the t_CombinedVelocityTotals table for use in another database.

Everything seems to work properly but when I run the q_AllItemsWeeklyAverage append query, it takes forever to add the records to the table. By the way, I delete all records in the t_CombinedVelocityTotals table each time before I run the query.

I would really appreciate someone's help on this. Thanks.

 

[Galaxiom]

Usually a good idea to post the sql of the query. Ths often is enough to show the problem and saves downloading the database.

 

[Poppa Smurf]

In a nut shell I want to calculate the averages for each day for all products in the t_ItemSearch table (always has about 35,000 records) and put it into the t_CombinedVelocityTotals table for use in another database.

I am lost. You want to add records to t_CombinedVelocityTotals using t_ItemSearch. Is t_ItemSearch populated by another query etc or is it the transaction table? Now in t_ItemSearch you have a field Status Date as text field this should be a date/time field.

Now using Monday as an example how are the tables t_TransactionsMon1 to Mon4 created? Each table Mon1 to Mon4 has 55 records a total of 220 records BUT each query q_Mon1Totals to q_Mon4Totals produce 15575 records a total of 62,300 records IMPOSSIBLE.

Using t_ItemSearch table to produce t_CombinedVelocityTotals cannot be done as the Status Date is a text field and should be a date/time field.

 

[Cullihall]

Thank for your replies guys.

Sorry for not posting SQL of the query. I didn't because I am not sure if the issue is in other queries.

I have 4 transactions tables (t_TransactionsMon1, t_TransactionsMon2, ...3, 4) for each day so I can keep a 4 week rolling transaction history. If today is Monday then I replace the data in t_TransactionsMon# table that has the oldest date.

The t_TransactionsMon1 tables are shipping data for one day that I paste in from another source. The t_ItemSearch table is also data pasted from another source and contains all my items.

I want to use all the items in the t_ItemSearch because if an item did not ship in this period, then it would not be in the transactions tables so by using all items in the t_ItemSearch table, I can put zeros in if it did not ship.

So I am using all item data from the t_ItemSearch table and the shipping information comes from the transactions tables.

All the data in the tables is pasted in from another source with the exception of t_CombinedVelocityTotals, this is the table that I want to put all of my items into along with their shipping averages from the transactions. When finished, this table should have every single item from the t_ItemSearch table and the calculated averages for every single item (zero if no transactions in the transactions tables) for Monday, for Tuesday, up to Saturday. i.e. Item 123456 ships an average of 23 cases on Monday, 34 cases on Tuesday, 3 cases on Wednesday, etc.

The field Status Date is text because again, I copy the data from another source and I cannot paste into the access table without re-formatting the date if the field is set as a date.

 

[Poppa Smurf]

Please find a version that may provide a solution to your problem.

In the attached I have added a new table tbl_temp_monthly_data, this table will hold the monthly data. The field Item Code in the table t_CombinedVelocityTotals was set as the Primary Key.

Run the module mod_run_monthly_data. This module does the following process:-
1. Delete the table tbl_temp_monthly_data
2. Append the transactions tables t_TransactionsMon1 to t_TransactionsSun4 to the table tbl_temp_monthly_data
3. Calculate and update the fields master_pack and eaches_pack
4. Delete the table t_CombinedVelocityTotals
5. Use code to create a cross tab query, then use the result of the cross tab query to append the totals to the table t_CombinedVelocityTotals
6. Append the field item code from table t_ItemSearch to t_CombinedVelocityTotals. Only item codes not already in t_CombinedVelocityTotals are added.

I do not have the time to check my results against your version.

 

[Cullihall]

Please find a version that may provide a solution to your problem.

In the attached I have added a new table tbl_temp_monthly_data, this table will hold the monthly data. The field Item Code in the table t_CombinedVelocityTotals was set as the Primary Key.

Run the module mod_run_monthly_data. This module does the following process:-
1. Delete the table tbl_temp_monthly_data
2. Append the transactions tables t_TransactionsMon1 to t_TransactionsSun4 to the table tbl_temp_monthly_data
3. Calculate and update the fields master_pack and eaches_pack
4. Delete the table t_CombinedVelocityTotals
5. Use code to create a cross tab query, then use the result of the cross tab query to append the totals to the table t_CombinedVelocityTotals
6. Append the field item code from table t_ItemSearch to t_CombinedVelocityTotals. Only item codes not already in t_CombinedVelocityTotals are added.

I do not have the time to check my results against your version.

Awesome Poppa Smurf!

I really appreciate the fact that you put the time and effort into this for me. Your code accomplishes in seconds what was taking me about 5 minutes.

Thank you very much. This site is the best!!!

 

[Poppa Smurf]

Your welcome, Yes, it was a challenge. I forgot to mention that when I was consolidating the data trasnactions table I also had field in the append code to flag the day of the week e.g. mon, tue etc. This flag was used to produce totals for the crosstab for each day of the week.

In your version item code 9756216 does not exist in your item search table.