Hi
I have been working with Access for a while but I only use it for my own personal projects so I'm very much an amateur.
I use an access database to keep track of purchases and sales of items. The nature of the business is such that I only buy one item at a time. By that I mean that while I may buy 20 widgets in one purchase, I don't buy anything else. So, the core table in the database is tblTransactions which records all the essential info (#, price, date, etc) for each transaction.
I need to calculate the Adjusted Cost Base per Unit, of all the items I have bought and sold. The method of calculating Adjusted Cost Base, over which I have no control, is as follows.
-The Adjusted Cost Base per Unit is re-calculated each time an item is
bought or sold.
-If a number of units are bought in a transaction, the number of units bought are added to the previous total of units bought, and the total cost of the number bought is added to the previous total cost. The new Total Cost is then divided by the new Total Bought to get the Adjusted Cost Base per Unit.
-If a number of units are sold, the number sold is subtracted from the previous total of units bought, and an amount equal to - # of units sold * adjusted cost base per unit - is subtracted from the Total Cost. The new Adjusted Cost Base per unit is then calculated by dividing the new Total Cost by the new Total Bought.
So my idea is to create a recordset, grouped on name and sorted by date. I can then step through the recordset and do the calculations necessary to end up with two variables for each item. One for name and one for Adjusted Cost Base per Unit.
My question is: How do I get that information to display on a Continuous Form so I can see a list of items and their Adjusted Cost Base?
Thanks a lot for any help you can provide
I have been working with Access for a while but I only use it for my own personal projects so I'm very much an amateur.
I use an access database to keep track of purchases and sales of items. The nature of the business is such that I only buy one item at a time. By that I mean that while I may buy 20 widgets in one purchase, I don't buy anything else. So, the core table in the database is tblTransactions which records all the essential info (#, price, date, etc) for each transaction.
I need to calculate the Adjusted Cost Base per Unit, of all the items I have bought and sold. The method of calculating Adjusted Cost Base, over which I have no control, is as follows.
-The Adjusted Cost Base per Unit is re-calculated each time an item is
bought or sold.
-If a number of units are bought in a transaction, the number of units bought are added to the previous total of units bought, and the total cost of the number bought is added to the previous total cost. The new Total Cost is then divided by the new Total Bought to get the Adjusted Cost Base per Unit.
-If a number of units are sold, the number sold is subtracted from the previous total of units bought, and an amount equal to - # of units sold * adjusted cost base per unit - is subtracted from the Total Cost. The new Adjusted Cost Base per unit is then calculated by dividing the new Total Cost by the new Total Bought.
So my idea is to create a recordset, grouped on name and sorted by date. I can then step through the recordset and do the calculations necessary to end up with two variables for each item. One for name and one for Adjusted Cost Base per Unit.
My question is: How do I get that information to display on a Continuous Form so I can see a list of items and their Adjusted Cost Base?
Thanks a lot for any help you can provide