Running total + date calculation

[YST]

Hey all,

Thanks to the great advice of this board, I am moving slowly but surely towards getting my database up and running.

To refresh, it is to monitor stock, bond, options trades, and summarise accounts, stock holdings, etc.

In Canada, the law on tax loss selling is this:

1) You sell your entire stake in a particular stock, so total shares in one account = 0. Note that shares can still be held in other accounts.

2) You MUST wait 30 days before buying the stock back to get the new price as your cost basis.

3) If you do not wait 30 days, you cost basis is the price of the shares you owned previously weighted averaged with the price of the shares you just bought.

So, in order to incorporate this information, I first need to walk through each security, grouped by account, and find out when the shares go to 0. I then have to check whether or not the next transaction (which can only be a BUY) occurs 30 days later.

This information should not be stored in the database itself, BUT it needs to be available in a form and a report.

The reason I believe it needs to be available in a form is that it is information that can be acted on, so it should be apparent to the user when they are entering trades into the system. I want the user interface to be as simple as possible.

Can I even do this with a query or do I need to add some VB code to my form?

 

[dcx693]

You won't need any complicated formulas for this. A simple query should do it. You just need to carry out some actions on your form should the total shares in one account=0. You can do this using the form's On Current event. If the number of shares might change whlie the form is open, you'll need some other event triggers also. You can do that calculation, as well as the one to check for whether or not 30 days have elapsed, in the form's underlying query.

If you're in a multi-user situation, you should refresh the form view at regular intervals.

 

[YST]

Thank you for the reply.

I am not worried about multi-user situations at this point in time. Perhaps in the future.

Can you please elaborate a little more on the queries?

I have one table for ALL transactions for all accounts.

Obviously, I know how to sort these by account and then by stock symbol.

The question is, how do I walk through these sub groups and sum the shares?

And then when I find a sum that equals 0, how do I exactly calculate the date?

This system will be used for dates going back 3-4 years as well, if that matters.