Feb 25 2023 11:05 PM
Feb 25 2023 11:05 PM
I am struggling to create a stock balances file where I can easily know the closing balances PER DAY, and what I need to borrow from the Main store as explained in the file.
I have attached the file and would appreciate to be assisted.
I am using office 2019.
Feb 26 2023 06:16 PM
Feb 26 2023 06:47 PM
Feb 26 2023 07:06 PM
Feb 26 2023 07:17 PM
I have edited the file again and I think if I could have the closing balances of the previous day become the new Opening balances of a new day, then I can manage to move on from there. Please see attached updated file. The file can be changed completely and not a must to appear the way it is. Important for me it to use the closing balances of the previous day as the new balances of the new day.
Feb 26 2023 08:07 PM
Feb 27 2023 08:26 AM
Feb 27 2023 11:05 AM
I have tried to reformat the table though some areas are still not clear. Please have a look.
Let me explain the process.
1. The end users will request for items to be used the next day (upfront) from the Issuing store.
2. The issuing store will prepare a summary of all these requested items which will be entered into a spreadsheet.
3. Upon entering this data, it shall be known if the issuing store has enough stock or will require top-up from Main store. If the latter applies, then the item will be issued to them from the Main Store as explained earlier. Stock levels for both Stores will be affected. For depleted items, they end users will choose an Alternative item till it is replenished.
4. Every Month, they make a list of items that need to be procured. Of course.. assisted by the program in use.
What is not clear to me is the used and add tx type and where the data comes from. The program user is supposed to feed the data from end users and the program to do the calculations. They should be concerned with what the end users want and the program to calculate what is available from issuing store and if there is anything needed from Main store on a daily basis. May be I did not understand clearly your point.
Feb 27 2023 02:34 PM - edited Feb 27 2023 02:36 PM
running total？ or summary by now？
each items store Date Store Type Item Code Transaction Type Amount balance 2023/2/21 Issuing Store CHEM1 used 400 -400 2023/2/21 Issuing Store CHEM1 Added 5000 4600 2023/2/21 Issuing Store CHEM2 Added 5000 5000 2023/2/21 Main Store CHEM1 Added 50000 50000 2023/2/21 Main Store CHEM2 used 50 -50 each items Date Store Type Item Code Transaction Type Amount balance 2023/2/21 Issuing Store CHEM1 used 400 -400 2023/2/21 Issuing Store CHEM1 Added 5000 4600 2023/2/21 Main Store CHEM1 Added 50000 54600 2023/2/21 Issuing Store CHEM2 Added 5000 5000 2023/2/21 Main Store CHEM2 used 50 4950
Mar 01 2023 02:29 PM - edited Mar 01 2023 02:30 PM
cli_add_html~balance by now;
select *,sum(iif(`Transaction Type` like 'used',-Amount,Amount)) balance from running_total_of_every_data_each_item group by `Store Type`,`Item Code`;