Using calculation results on recordset to populate form objects (1 Viewer)

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

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?

Click to expand...

First of all, welcome to AWF

I suggest creating a procedure which performs each calculation in turn using the SQL equivalent of update queries.
This is likely to be a lot faster than looping through a recordset - especially if you have a lot of records

Then create a query showing the fields you require - suggest adding an ID field as well.
Use the query as the record source for your form

You should also consider how yiu store your data both on buying and selling.
You bought item 1 yesterday @ $1 x qty. The same item you bought the ff day @ 1.25 x qty.
When yiu sell them which method will you use, fifo, lifo or avg price? You shoul also monitor the remain qty of each item per price.