Hi Stephen,
It seems it is complex query; from your sample, there is logic to sum the
number of shares.
1. Get a row from table DividendTbl;
2. Find the rows in table TransActTbl with the same ticker, and the row's
TransactDate (in table TransActTbl) is smaller than the row's DivDate (in
the table DividendTbl).
3. Add the number of shares of the matched rows.
So for record/row :
2 5/10/2000 SO 0.5
its DivDate is 5/10/2000, compare the same ticker in table TransActTbl:
2 1/1/1999 SO 710
3 1/12/2000 SO 800
9 5/10/2003 SO 1000
there are two items meet the conditions: date is smaller:
2 1/1/1999 SO 710
3 1/12/2000 SO 800
so the Total is 1510.
I think we can get the final goal by VBA code, loop though the TransActTbl
and sum the shares.
Sincerely,
Alick Ye, MCSD
Microsoft Online Partner Support
Get Secure! -
www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------
| Content-Class: urn:content-classes:message
| From: "Stephen" <
[email protected]>
| Sender: "Stephen" <
[email protected]>
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:167282
| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| X-Tomcat-NG: microsoft.public.access.queries
|
|
| You are correct. The result is very similar with the
| DividendTbl.
|
| There is more than one approach, but one logic for
| deriving the Total shares for a given record in the
| result is:
| 1) Get the running total of shares for each ticker as of
| each transaction date for that ticker.
| 2) Identify the transaction date for that ticker that is
| closest to but not past a dividend date having the same
| ticker.
| 3) Use that running total as the Total shares for a given
| dividend date.
|
| So for SO and PCG: it is because the DivDates for PCG all
| fall after the transactionDates while for SO the
| transactionDates are after the divdate.
|
| The context of the query is that I want to calculate how
| much of a dividend a company will pay me on a given
| dividend date. This is calculated by multiplying the
| total number of shares I own on that divdate by the
| DivAmt. Before I do a calc field though I first must
| determine how many shares I own on that dividend date.
|
|
| Thanks for being willing to work with me on this.
|
| I really appreciated it.
|
| Stephen
|
| >-----Original Message-----
| >Hi Stephen,
| >
| >It seems query result is very similar with the table
| DividendTbl, except
| >the DivID/ Total column; the Total column's value should
| comes from
| >NoSharesBought in table TransActTbl; however, I don't
| find any
| >logic/relationship we can use to get the value. For
| example, ticker PCG:
| >
| >Table TransActTbl:
| >
| >1 1/1/1999 PCG 300
| >5 7/1/2001 PCG 200
| >6 4/8/2002 PCG 150
| >8 1/1/2003 PCG 400
| >
| >Table DividendTbl
| >
| >8 6/11/1999 PCG 0.3
| >9 11/11/2001 PCG 0.25
| >10 11/15/2002 PCG 0.7
| >
| >
| >Result:
| >
| >300 6/11/1999 PCG 0.3
| >500 11/11/2001 PCG 0.25
| >650 11/15/2002 PCG 0.7
| >
| >The Total value seems calculated by:
| >
| >300 = 300; DivID (1)
| >500 = 300 + 200; DivID(1) + DivID (5)
| >650 = 300 + 200 + 150; DivID(1) + DivID (5) + DivID (6)
| >
| >if we say there is a calculation logic in ticker PCG;
| the logic explicitly
| >does not apply to others, (explicitly ticker SO).
| >
| >How do you get the value? Any logic/relationship?
| >
| >
| >Sincerely,
| >
| >Alick Ye, MCSD
| >Microsoft Online Partner Support
| >
| >Get Secure! -
www.microsoft.com/security
| >This posting is provided "as is" with no warranties and
| confers no rights.
| >
| >--------------------
| >| Content-Class: urn:content-classes:message
| >| From: "Stephen" <
[email protected]>
| >| X-Tomcat-NG: microsoft.public.access.queries
| >|
| >| Thanks so much for your consideration
| >|
| >| Here is the TransActTbl
| >|
| >| TransactID TransactDate Ticker NoSharesBought
| >| 1 1/1/1999 PCG 300
| >| 2 1/1/1999 SO 710
| >| 3 1/12/2000 SO 800
| >| 4 7/1/2001 DKE 200
| >| 5 7/1/2001 PCG 200
| >| 6 4/8/2002 PCG 150
| >| 7 5/10/2002 DKE 400
| >| 8 1/1/2003 PCG 400
| >| 9 5/10/2003 SO 1000
| >| 10 5/5/2004 DKE 700
| >|
| >| Here is the DividendTbl
| >|
| >|
| >| DivID DivDate Ticker DivAmt
| >| 1 5/5/1999 SO 1.1
| >| 2 5/10/2000 SO 0.5
| >| 3 5/15/2002 SO 2
| >| 4 1/3/2003 SO 0.9
| >| 5 10/10/2000 DKE 1.5
| >| 6 12/10/2001 DKE 3
| >| 7 6/10/2002 DKE 0.75
| >| 8 6/11/1999 PCG 0.3
| >| 9 11/11/2001 PCG 0.25
| >| 10 11/15/2002 PCG 0.7
| >|
| >|
| >| Here is what I would like as a result:
| >|
| >| Total Shares DivDate Ticker DivAmt
| >| 710 5/5/1999 SO 1.1
| >| 1510 5/10/2000 SO 0.5
| >| 1510 5/15/2002 SO 2
| >| 1510 1/3/2003 SO 0.9
| >| 0 10/10/2000 DKE 1.5
| >| 200 12/10/2001 DKE 3
| >| 600 6/10/2002 DKE 0.75
| >| 300 6/11/1999 PCG 0.3
| >| 500 11/11/2001 PCG 0.25
| >| 650 11/15/2002 PCG 0.7
| >|
| >|
| >| I am trying to build some tools for a start-up
| investment
| >| management company and I see this as a key tool. I
| tried
| >| a subquery to give me a running total of the number of
| >| shares owned at each transaction date, but then I run
| into
| >| a problem with trying to get the right number of
| shares
| >| mapped to the right ticker, dividend date, and div
| amount.
| >|
| >| I'm really hoping this can be solved without having to
| >| resort to VBA code, but after the number of hours I've
| >| spent I'm anxious for any answer.
| >|
| >| Thanks so much for any help you could provide.
| >|
| >| Sincerely,
| >|
| >| Stephen
| >|
| >|
| >|
| >| >-----Original Message-----
| >| >Hi Stephen,
| >| >
| >| >Would you post sample table content and the result
| you
| >| want to get? For
| >| >example, TransActTbl with 10 records, DividendsTbl
| with
| >| 10 records, and
| >| >after run the query, the result records.
| >| >
| >| >
| >| >Sincerely,
| >| >
| >| >Alick Ye, MCSD
| >| >Microsoft Online Partner Support
| >| >
| >| >Get Secure! -
www.microsoft.com/security
| >| >This posting is provided "as is" with no warranties
| and
| >| confers no rights.
| >| >
| >| >
| >| >
| >| >
| >| >--------------------
| >| >| Content-Class: urn:content-classes:message
| >| >| From: "Stephen" <
[email protected]>
| >| >| Sender: "Stephen" <
[email protected]>
| >| >|
| >| >| Terri,
| >| >|
| >| >| Thanks for your help, however, this does not seem
| to
| >| >| work. One challenge to this querry is getting the
| sum
| >| of
| >| >| the shares bought only up to the dividend pay
| date. So
| >| >| for example, if I buy 100 shares of IBM in May and
| >| another
| >| >| 200 shares in Jul and then have a dividend pay date
| in
| >| Jun
| >| >| and again in Aug for IBM; I should get
| >| >|
| >| >| Jun IBM 100
| >| >| Aug IBM 300
| >| >|
| >| >| Instead your query gives me:
| >| >|
| >| >| Jun IBM 300
| >| >| Aug IBM 300
| >| >|
| >| >|
| >| >| >-----Original Message-----
| >| >| >Sounds like your looking for something like this:
| >| >| >
| >| >| >SELECT DividendsTbl.DivPayDate,
| DividendsTbl.Ticker,
| >| Sum
| >| >| >(TransActTbl.NoSharesBought) AS SumOfNoSharesBought
| >| >| >FROM DividendsTbl INNER JOIN TransActTbl ON
| >| >| >DividendsTbl.Ticker = TransActTbl.Ticker
| >| >| >GROUP BY DividendsTbl.DivPayDate,
| DividendsTbl.Ticker;
| >| >| >
| >| >| >Thanks
| >| >| >Terri
| >| >| >
| >| >| >>-----Original Message-----
| >| >| >>I have two tables:
| >| >| >>TransActTbl with fields [TransActID], [Ticker],
| >| >| >>[NoSharesBought],and [TransactDate]
| >| >| >>
| >| >| >>DividendsTbl with fields [Ticker],[DivPerShare],
| >| >| >>[DivPayDate]
| >| >| >>
| >| >| >>I'm trying to create a query that will sum the
| number
| >| of
| >| >| >>shares I own of a given company(ticker) for each
| >| >| PayDate.
| >| >| >>so that I end up with the following output:
| >| >| >>
| >| >| >>[PayDate], [Ticker], [TotalSharesOwned]
| >| >| >>
| >| >| >>Thanks
| >| >| >>Stephen
| >| >| >>.
| >| >| >>
| >| >| >.
| >| >| >
| >| >|
| >| >
| >| >.
| >| >
| >|
| >
| >.
| >
|
