wintermute
New Member
Dears,
problem which bothers me for a long time.
I have an access database with raw data for purchasing.
I'd like to simply calculate weekly balance for buys ("Previous week stock/balance" plus "Incoming" minus "Demand") weekly, considering stock from previous week. I think it cannot be calculated in Access, as Access cannot reference to value in previous record... I don't want to calculate it standard way with functions on a sheet, I want to keep all data in access and pull them into excel's pivot table (as items count from 100 one week to 3500 next week because of long running items and short, one_time_buy items) therefore I don't need to maintain rows with dead, obsolete functions or, on the other hand, copy functions to new rows, if new items arise. I tried to solve it with "Running total in" in pivot, but it's not what I need. I can see only one option now: pull in data from Access to Excel in pivot table, change pivot table to regular table, filter subtotals and put functions manualy into the cells, but you know, it's not elegant and proper way. Is is possible to reference in pivot calculated field value from previous week? Or is it possible to calculate it somehow in Access?
I hope you understand my english and thank you in advance. Sample file attached.
problem which bothers me for a long time.
I have an access database with raw data for purchasing.
I'd like to simply calculate weekly balance for buys ("Previous week stock/balance" plus "Incoming" minus "Demand") weekly, considering stock from previous week. I think it cannot be calculated in Access, as Access cannot reference to value in previous record... I don't want to calculate it standard way with functions on a sheet, I want to keep all data in access and pull them into excel's pivot table (as items count from 100 one week to 3500 next week because of long running items and short, one_time_buy items) therefore I don't need to maintain rows with dead, obsolete functions or, on the other hand, copy functions to new rows, if new items arise. I tried to solve it with "Running total in" in pivot, but it's not what I need. I can see only one option now: pull in data from Access to Excel in pivot table, change pivot table to regular table, filter subtotals and put functions manualy into the cells, but you know, it's not elegant and proper way. Is is possible to reference in pivot calculated field value from previous week? Or is it possible to calculate it somehow in Access?
I hope you understand my english and thank you in advance. Sample file attached.