Kumba1
Technical User
- Aug 29, 2002
- 94
I have a total of 10 assembly-steps for a product... I keep track of when the product moves from one assembly-step to the next, by recording the date of the last fully-completed-assembly... what I need to do is create a function that I can call to calculate the MTDTotal and MTD Average, with a regular date (IE mm/dd/yyyy) supplying the month to Total and Average, as well as a second input that defines the assembly area to Total and Average... the use would be something similar to MTDTotal(#10/09/2002#,[Step1])... Also need to accomplish the same thing, but over the year... here's how the tables are laid out...
Index (pk)
ProductID
step1date
step2date
step3date
step4date
step5date
step6date
step7date
step8date
step9date
step10date
So basically, if I would use MTDTotal(#10/09/2002#,[step1date]) it would count how many productID's (running sum basically) where in that month...
Now if I use MTDAverage(#10/09/2002#,[step1date]), it would count how many products where completed in that field, then divide the number by the number of months there are so far in the year (IE, if it's the month of Febuary, and i've done 50 engiens, 50/2=25 engines a month, but it it's october, and i've done 500, 500/10=50 engines a month)...
Basically, the YTD Functions would do the same thing, with YTDTotal counting the products completed in that area for the year, and YTDAverage function counting all the products it has, and dividing that number by the year's counted...
The reason i'm trying to create modular functions like this is because it eliminates excessive queries... IE, to calculate the YTD and MTD totals/averages, it would take 4 queries, now with 10-fields, that's 40-queries... and i'm not sure a query can do the type of averaging i'm trying to do... If anyone can help, it would be greatly appreciated... Hell, i'd even put the functions up for grabs on my webserver for future uses ]
Index (pk)
ProductID
step1date
step2date
step3date
step4date
step5date
step6date
step7date
step8date
step9date
step10date
So basically, if I would use MTDTotal(#10/09/2002#,[step1date]) it would count how many productID's (running sum basically) where in that month...
Now if I use MTDAverage(#10/09/2002#,[step1date]), it would count how many products where completed in that field, then divide the number by the number of months there are so far in the year (IE, if it's the month of Febuary, and i've done 50 engiens, 50/2=25 engines a month, but it it's october, and i've done 500, 500/10=50 engines a month)...
Basically, the YTD Functions would do the same thing, with YTDTotal counting the products completed in that area for the year, and YTDAverage function counting all the products it has, and dividing that number by the year's counted...
The reason i'm trying to create modular functions like this is because it eliminates excessive queries... IE, to calculate the YTD and MTD totals/averages, it would take 4 queries, now with 10-fields, that's 40-queries... and i'm not sure a query can do the type of averaging i'm trying to do... If anyone can help, it would be greatly appreciated... Hell, i'd even put the functions up for grabs on my webserver for future uses ]
