production records

[adriana]

Hi, I'm glad to find you



I created a db in access 2010 where I insert the daily production(knitwear), so I have the fallowing tables: [Articles] with the fields Article (primary key) and Ncycle(this is the number of cycle to be done to a certain article); [Orders] with the fields : IDOrder (primary key), Date, Article, Quantity, hours to do
[daily production] with the fields: Date, Article, Cycle, Quantity, IDOrder. The relation sheep [Orders].[IdOrder] one to many with [daily production].[IDOrder]; [Articles].[Article] one to many with [orders].[Article].

I created a query of calculation where I drew the tables: Articles, Orders, daily production, with the fields [Date] and [Article] from table [daily production], and I made the following formula: production made:[daily production].[Quantity]/[Article].[Ncycle]; production still to be done:[Orders].[Quantity]-[production made]

done hours: ([production made]*[orders].[hours to do])/[Orders].[Quantity]; hours still to be done:[Orders].[hours to do]-[done hours].

and the query does all calculations, BUT if I have 400 clothes to do,every day the query have to subtract the quantity already made (ex: 10.05.13 made quantity 50, remaining : 350 (400-50), 11.05.13 made quantity 60, remaining: 290 (350 -60)), but the query does not doing this calculation, every day starts do substract to begin: 11.05.13, remaining 340 (400-60), the same thing with the hours.

There is a VBA code do do this?

Please help me

thank you

[June7]

Start with a review of http://allenbrowne.com/AppInventory.html

If you want to account for daily production then need a table to store the daily data.

[adriana]

Start with a review of http://allenbrowne.com/AppInventory.html

If you want to account for daily production then need a table to store the daily data.

Hi June, and thank you for your readiness, I have a table to store the daily data, which is [daily production], but now I need a query to calculate the remaining of clothes do do. If I have to do 1000 clothes, the first day I made 100, the remainder is 900, the next day I made 80, the remainder is 820, and so on, but the query every day starts to calculate "remainig of production" from zero, first day remaining is 900, the next day 920. That's why I think that there is a VBA code, because I make all test in query, but there is nothing to do. Can you help me, or maybe you now a link where there are VBA codes that I need ?

Thank you again

have a nice day

[June7]

You also need a table that has transactions for the production commitment. Then calculate difference between sum of commitment and sum of daily production, grouping by customer or order or whatever. Use aggregate query to calculate the sums. Then join the queries. Without knowing your db better, can't be more specific.