Valentino-
What is a .mcc file? No clue how to look at it.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Jun 6, 2014, at 6:53 AM, valentino.avvisati@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John-
Thank you.
In meantime I discovered that the issue is not the 64 bit operative system but the access version.
The database is done in access 2007, If I run it with access 2010 - 64 bit machine it is ok and it take one minute (10 seconds more), but with access 2013 not: take more than 5 minute to make the same elaboration.
I uploaded the file called dispogaro01tes.mcc
I'm really not an expert of Access and to be honest, I fear a little bit send my "code" to this forum full of very Expert person, but I like Access very much it and at the end, is producing the result I need.
In the file that I uploaded in folder "AssistanceNeeded " called "dispogaro01test" there is one module with 4 Function in one module:
Frigo1
Frigo2
Frigo3
Calc
I delete all the unnecessary data keeping just 2 varieties to make a real test.
(in reality the varieties are more than 100 ).
background:
I work for an young plants company that is producing cuttings (Poinsettia, Pelargonium, Carnation, other ) from mother plants.
Some of these young plants (Pot carnation or carnation ), can be stocked for a certain period in the cool store (4 weeks).
Every week cuttings are collected from the greenhouses in plastic bags and moved inside the cool store to be stocked.
The goal of this tool it is calculating the residual availability to be shown to sales force after the collection of some orders.
Unfortunately the fact that can be stocked in cool store make the calculation of weekly availability extremely complicated.
(for no cool store product is very easy jut Availability – Orders in each week).
Data is available as in table frigo1:
| WWFRIGO01 | |||||||||||||
| SMDS03PIN | TBTP01PIN | TBTP01cod | DBMA01PIN | DBMA01cod | DBMA01des | Gr | SubGr | SMDS03IAN | SMDS03ISE | SMDS03FAN | SMDS03FSE | SommaDiSMDS03QDS | SommaDiSMDS03QUS |
| 10418 | 1 | 000 | 2035 | 02013000 | Diogenes, URC | 02 | - - | 2014 | 25 | 2014 | 29 | 9349 | 4517 |
| 20687 | 1 | 000 | 2035 | 02013000 | Diogenes, URC | 02 | - - | 2014 | 26 | 2014 | 30 | 9349 | 2350 |
| 3071 | 1 | 000 | 2035 | 02013000 | Diogenes, URC | 02 | - - | 2014 | 27 | 2014 | 31 | 9349 | 9349 |
Where the variety 2035 has 3 records
1. Quantity of 9349 entered in week 25-2014 that is good up to week 29 -2014
2. Quantity of 9349 Enter in week 26-2014 good up to 30 -2014
3. Quantity of 9349 Entered in week 27 good up to 31-2014
we need to know the residual availability after that some order are collected
Each time that we have one order for one delivery week, we need to engage the bags starting from the oldest one (but still valid) .
This mean that if we have one order of 9349 delivery week 30, we should start to engage the one numbered as 20687 (second record in the example) because oldest respect the one numbered 3071 that also we could take for this week.
(the first record is excluded because too old in week 30 when we need).
To calculate the residual availability I exploded the table wwwfrigo1 in table wwfrigo02 (function frigo1)
Where I have one record for each week (in wwwfrigo1 I have one record that can be used in a range of week)
| WWFRIGO02 | ||||||||||||||||
| SMDS03PIN | TBTP01PIN | TBTP01cod | DBMA01PIN | DBMA01cod | DBMA01des | Gr | SubGr | ANNOiniz | settimanainiz | ANNO | settimana | Q | Qinit | Qres | flag | processati |
| 20687 | 1 | 000 | 2035 | 02013000 | Diogenes, URC | 02 | - - | 2014 | 26 | 2014 | 30 | 9349 | 6999 | ok | ||
| 20687 | 1 | 000 | 2035 | 02013000 | Diogenes, URC | 02 | - - | 2014 | 26 | 2014 | 26 | 9349 | 6999 | ok | ||
| 20687 | 1 | 000 | 2035 | 02013000 | Diogenes, URC | 02 | - - | 2014 | 26 | 2014 | 27 | 9349 | 6999 | ok | ||
| 20687 | 1 | 000 | 2035 | 02013000 | Diogenes, URC | 02 | - - | 2014 | 26 | 2014 | 28 | 9349 | 6999 | ok | ||
| 20687 | 1 | 000 | 2035 | 02013000 | Diogenes, URC | 02 | - - | 2014 | 26 | 2014 | 29 | 9349 | 6999 | ok | ||
In this way it is easier for me query and engage the bags against the order starting from the older one.
(unfortunately I did not find the way to query directly the table wwwfrigo01 to take the oldest bag for one delivery week)
This operation is performed using the function frigo2 and at the end I produce a table wwwfrgo05 with two column: potential availability and residual availability after that some order engaged starting from oldest bags.
The problem now is that the residual availability it is not really telling me how much order I can enter in one week without going in overbooking.
The quantity I see as residual availability underestimate the real possibility to collect orders because the engagement of bags can deeply change depending on order that we insert.
In one week I can see 0 because all the bags of this week are engaged following the criteria to take the oldest bags but in reality I can still place order without creating overbooking due to the rearrangement of use of the bags after that I introduced my additional order.
This is the reason why there is function frigo3:
This function try to insert in each week (Keeping all existing order) a quantity and stop when overbooking is created
This is repeated for each week and the result is table wwwfrigo10 , Where in column dispo2 I have the maximum quantity is possible insert without increase overbooking.
It look like this function is very slow in Access 2013 (64 bit) but acceptable in Access 2007 (32 bit)
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (9) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar