Average Amount

  • Thread starter Thread starter Stefan
  • Start date Start date
S

Stefan

I have a report that tracks stock trades. I am trying to be able to figure
out the most money that is invested at any one time during the month.

Data looks like this

Purchase Date Sale Date Purchase Price Stock

2/1/09 2/10/09 $3000 ABC
2/11/09 2/20/09 $5000 XYZ
2/8/09 2/15/09 $10,000 LMN

I would like to calculate during this time period what was the most money I
had in the market at any one time for the month of february


Thanks in advance for any help
 
To get a record for every date, you will need a table of dates.

1. Create a table with one field name (say) TheDate, of type Date/Time. Mark
it as primary key. Save it with a name such as tblDate. Enter records for
the date range you need, or use the code below to create the entries for
you.

2. Create a query using tblDate and your original table. There must be no
line joining the two tables in the upper pane of query design. Drag TheDate
from tblDate into the grid. In the Criteria under this field, enter:
Between [Purchase Date] And [Sale Date]
Also drag [Purchase Price] into the grid.

3. Depress the Total button on the toolbar.
Access adds a Total row to the grid.
In the Total row under TheDate, accept GroupBy.
Choose Sum under [Purchase Price].

The query shows a record for each date, with the sum of price at that date.

Here's the code to populate the table of dates programmatically if you wish:

Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
 
Back
Top