calendar month report

[aar241]

Hi,

I have a database of investment transactions. Each transaction shows a
date that the investment was bought and the date that the investment
was sold. Now I want to create a "report" (it doesn't actually have to
be an Access report - it could be a query if that would work better) in
which for a given month (entered by the user), I'd get something like
this:

1st column: the day of the given month (all dates in the month should
be shown, even if nothing happened that day)
2nd column: the sum of all investments bought on that day
3rd column: the sum of all investments sold on that day

Is this possible?

Thanks!

 

[Allen Browne]

Okay, the dates have to come from somewhere, so you need a table of dates to
supply them.

1. Create a table with just one field named TheDate.
Mark the field as primary key.
Save the table with the name tblDate.

2. Enter a record for each date you need to cover.
You can use the function below to enter all dates between 2000 and 2020
with:
? MakeDates(#1/1/2000#, #1/1/2020#)

3. Create a query based on tblDate and your Transaction table.
Join the two tables on the Date field.
Double-click the line joining the 2 tables.
Access gives a dialog with 3 options.
Choose the one that says:
All records from tblDate, and any matches from Transaction.
The query will not output all dates, whether there is a transaction or not.

4. Depress the Total button on the toolbar (upper sigma icon.
Access adds a Total row to the query design grid.

5. Drag tblDate.TheDate into the output grid.
In the Total row under this field, accept Group By.

6. Drag Transaction.Buy into the output grid.
Choose Sum in the Total row.

7. Drag Transaction.Sell into the output grid.
Choose Sum in the Total row.

The query now shows all dates, and the total Buy and Sell values (if any.)

If your table actually has Buy and Sell in one column, with another field
indicating the TransactionType (say 1 for Buy, and -1 for Sell), you can
type an expression into a fresh column of the Field row instead of the Buy
and Sell fields. The two calculated fields would be:
IIf([TranactionType] = 1, [Amount], 0)
and
IIf([TranactionType] = -1, [Amount], 0)

Here's the function to programmatically insert all those dates:

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