Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date payment falls into (2000)

    Edited by HansV to display data in tables and to remove superfluous line ends.

    ok i have tbl_units with the fields unit_date,member no, FTmex, cumulative_holding with data like the below

    CODE
    <table border=1><td>05-Mar-04</td><td align=right>76772</td><td>BGAMAA</td><td align=right>8045.870885</td><td align=right>10-Jan-04</td><td align=right>76772</td><td>BGAMAA</td><td align=right>8039.519885</td><td align=right>12-Feb-04</td><td align=right>76668</td><td>RCEGT</td><td align=right>965.366242</td><td>24-Mar-04</td><td align=right>77827</td><td>SIEMA</td><td align=right>676.520998</td><td align=right>12-Feb-04</td><td align=right>76668</td><td>SZUKMA</td><td align=right>1300.268097</td><td align=right>12-Feb-04</td><td align=right>76662</td><td>SZUKMA</td><td align=right>1300.268097</td><td>24-Mar-04</td><td align=right>77827</td><td>GMTBUA</td><td align=right>138.47</td><td align=right>18-Feb-04</td><td align=right>77191</td><td>SPNE</td><td align=right>1493.875112</td><td align=right>12-Feb-04</td><td align=right>76662</td><td>4SSCBR</td><td align=right>1924.603</td><td align=right>12-Feb-04</td><td align=right>76662</td><td>STPIA</td><td align=right>2198.798595</td><td>24-Mar-04</td><td align=right>77827</td><td>FISS</td><td align=right>97.98</td><td>17-Oct-03</td><td align=right>76772</td><td>BSRECA</td><td align=right>1164.94</td><td align=right>10-Feb-04</td><td align=right>76692</td><td>NUIITT</td><td align=right>6047.16791</td><td>11-Mar-04</td><td align=right>77296</td><td>Q5TOH</td><td align=right>2320.574163</td><td>01-Mar-04</td><td align=right>77262</td><td>SPEU</td><td align=right>951.887952</td><td align=right>12-Feb-04</td><td align=right>76662</td><td>NUPRO</td><td align=right>3089.417947</td><td>01-Mar-04</td><td align=right>77269</td><td>SPEU</td><td align=right>1189.85994</td><td>18-Mar-04</td><td align=right>77649</td><td>NTFEEE</td><td align=right>3406.690741</td><td>18-Mar-04</td><td align=right>77649</td><td>SZTA</td><td align=right>3623.188406</td></table>
    I then have a query called qry_feed_from_sept which is accesing another table which returns results like this which looks like this

    CODE
    <table border=1><td>feed_FTMEX</td><td>feed_xd_date</td><td>feed_payment</td><td>BSRECA</td><td align=right>05-Nov-03</td><td align=right>0.41</td><td>CFILBA</td><td align=right>03-Nov-03</td><td align=right>0.31</td><td>FIMYI</td><td align=right>01-Dec-03</td><td align=right>0.11</td><td>FIMYI</td><td align=right>02-Jan-04</td><td align=right>0.11</td><td>FIMYI</td><td align=right>02-Feb-04</td><td align=right>0.1</td><td>FIMYI</td><td align=right>01-Mar-04</td><td align=right>0.1</td><td>PCCBA</td><td align=right>02-Feb-04</td><td align=right>1.21</td><td>PCUKMC</td><td align=right>01-Dec-03</td><td align=right>0.72</td><td>PLAO</td><td align=right>04-Feb-04</td><td align=right>0</td><td>PLPFIA</td><td align=right>16-Oct-03</td><td align=right>1.39</td><td>RSHYB</td><td align=right>03-Nov-03</td><td align=right>0.39</td><td>RSHYB</td><td align=right>01-Dec-03</td><td align=right>0.38</td><td>RSHYB</td><td align=right>02-Jan-04</td><td align=right>0.42</td><td>STPIA</td><td align=right>01-Dec-03</td><td align=right>0.91</td><td>STPIA</td><td align=right>01-Mar-04</td><td align=right>0.89</td><td>SZINA</td><td align=right>02-Feb-04</td><td align=right>40.82</td><td>YFAA</td><td align=right>31-Dec-03</td><td align=right>0</td><td>YFECA</td><td align=right>30-Sep-03</td><td align=right>1.46</td><td>YFECA</td><td align=right>02-Jan-04</td><td align=right>0.98</td></table>
    Now what the above is dividend payments , tbl_units is what the member has purchased and the date he or she has purchased. Now what i need to do is find out if they have had any dividend payments from their purchases so this is what Im doing

    CODE
    SELECT tbl_units.[MEMBER NO], tbl_units.MEMBER, tbl_units.FTmex, CDate(DMin("[unit_date]","tbl_units","[FTmex] = '" & [FTmex] & "' AND [MEMBER NO] =" & [MEMBER NO])) AS Start_lookup_from, Date() AS End_look_up
    FROM tbl_units
    GROUP BY tbl_units.[MEMBER NO], tbl_units.MEMBER, tbl_units.FTmex, CDate(DMin("[unit_date]","tbl_units","[FTmex] = '" & [FTmex] & "' AND [MEMBER NO] =" & [MEMBER NO])), Date()
    HAVING (((tbl_units.FTmex) Is Not Null));

    the above is just to find out what holdings the member has and when he first bought that holding and the current date at the moment. my next query based on the results on the first

    CODE
    SELECT qry_member.[MEMBER NO], qry_member.MEMBER, qry_member.FTmex, qry_member.Start_lookup_from, qry_member.End_look_up, qry_feed_from_sept.feed_xd_date, qry_feed_from_sept.feed_payment
    FROM qry_member INNER JOIN qry_feed_from_sept ON qry_member.FTmex = qry_feed_from_sept.feed_FTMEX
    WHERE (((qry_feed_from_sept.feed_xd_date)>[Start_lookup_from] And (qry_feed_from_sept.feed_xd_date)<[End_Look_up]));

    Now using the date range from the first query i see if there has been any dividend payments and if so i return the feed_xd_date. all ok so far, my next problem and which is where im stuck is i need to find out how many holdings the person had at the time that dividend was paid (cumulative holdings) and this is where im stuck :-(

  2. #2
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date payment falls into (2000)

    Ive changed tactics slightly with the following query

    SELECT tbl_units.[MEMBER NO], tbl_units.FTmex, IIf(IsNull(DMax("[unit_date]","[tbl_units]","[FTmex] = '" & [Ftmex] & "' AND [MEMBER NO] = " & [MEMBER NO] & " AND [unit_date] < #" & Format([unit_date],'mm/dd/yyyy') & "#")),DMin("[unit_date]","tbl_units","[FTmex] = '" & [FTmex] & "' AND [MEMBER NO] =" & [MEMBER NO]),DMax("[unit_date]","[tbl_units]","[FTmex] = '" & [Ftmex] & "' AND [MEMBER NO] = " & [MEMBER NO] & " AND [unit_date] < #" & Format([unit_date],'mm/dd/yyyy') & "#")) AS Start_lookup_from, CDate(IIf([start_lookup_from]=[unit_date],Date(),[unit_date])) AS End_look_up, tbl_units.[CUMULATIVE UNITS], tbl_units.unit_DATE
    FROM tbl_units
    WHERE (((tbl_units.FTmex) Is Not Null));

    but not getting correct start and end time dates :-(

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date payment falls into (2000)

    What is CUMULATIVE UNITS? There is a cumulative_holding field in tbl_units, but no CUMULATIVE UNITS.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Date payment falls into (2000)

    I pretty sure I don't understand all you are trying to accomplish, but on the surface it appears to me you don't have sufficient information to perform the task at hand. I presume that the value in tbl_units is a cumulative value at the date shown, or in other words it is a calculated value that is stored. One of the basic rules in database design is not to store calculated values, but to store the detail information from which the result can be derived. In this case I would expect to see a transaction table that would show an initial "contribution" and then subsequent dividends (and perhaps withdrawls). In such a design, it is not uncommon to show the prior balance and the resulting balance so that an audit can be performed if necessary. It also lets you establish what balance was reported on a given day in case questions arise. Is it possible that such data is available to you?
    Wendell

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date payment falls into (2000)

    unfortuantly Im dealing with financial feeds and some of the feeds are pre-formatted which is making my day .. well long.

    Im just reconciling data at the moment but you make some good points there wendell.

    In the end this is what i settled for,

    SELECT tbl_units.[MEMBER NO], tbl_units.FTmex, IIf(DCount("[ID]","[tbl_units]","[MEMBER NO]=" & [MEMBER NO] & " AND [FTmex] = '" & [FTmex] & "'")=1,[unit_date],IIf([unit_date]=DMin("[unit_date]","[tbl_units]","[FTmex] = '" & [Ftmex] & "' AND [MEMBER NO] = " & [MEMBER NO]),[unit_date],[unit_date])) AS Start_lookup_from, (IIf(IsNull(DMax("[unit_date]","[tbl_units]","[FTmex] = '" & [Ftmex] & "' AND [MEMBER NO] = " & [MEMBER NO] & " AND [unit_date] < #" & Format([unit_date],'mm/dd/yyyy') & "#")),IIf(DCount("[ID]","[tbl_units]","[MEMBER NO]=" & [MEMBER NO] & " AND [FTmex] = '" & [FTmex] & "'")=1,Date(),DMin("[unit_date]","[tbl_units]","[FTmex] = '" & [Ftmex] & "' AND [MEMBER NO] = " & [MEMBER NO] & " AND [unit_date] > #" & Format([unit_date],'mm/dd/yyyy') & "#")),IIf([unit_date]=DMax("[unit_date]","[tbl_units]","[FTmex] = '" & [Ftmex] & "' AND [MEMBER NO] = " & [MEMBER NO]),Date(),DMin("[unit_date]","[tbl_units]","[FTmex] = '" & [Ftmex] & "' AND [MEMBER NO] = " & [MEMBER NO] & " AND [unit_date] > #" & Format([unit_date],'mm/dd/yyyy') & "#"))))-1 AS End_look_up, DMax("[unit_date]","[tbl_units]","[FTmex] = '" & [Ftmex] & "' AND [MEMBER NO] = " & [MEMBER NO] & " AND [unit_date] < #" & Format([unit_date],'mm/dd/yyyy') & "#") AS old_date, tbl_units.[CUMULATIVE UNITS], tbl_units.unit_DATE
    FROM tbl_units
    WHERE (((tbl_units.FTmex) Is Not Null));

    then my second query

    SELECT DISTINCT qry_member.[MEMBER NO], qry_member.FTmex, qry_feed_from_sept.feed_xd_date, qry_feed_from_sept.feed_payment, qry_member.[CUMULATIVE UNITS], [feed_payment]*[CUMULATIVE UNITS] AS dividend_income, qry_feed_from_sept.feed_default_tax, qry_feed_from_sept.feed_currency, qry_feed_from_sept.feed_special_payment, qry_feed_from_sept.feed_isinterest, qry_feed_from_sept.feed_dividends_per_year, qry_feed_from_sept.feed_dividends_months, qry_feed_from_sept.feed_distribution, qry_feed_from_sept.feed_legal_structure, qry_feed_from_sept.feed_dividend_date, qry_feed_from_sept.feed_Payment_date, qry_feed_from_sept.feed_tax_code
    FROM qry_member INNER JOIN qry_feed_from_sept ON qry_member.FTmex = qry_feed_from_sept.feed_FTMEX
    WHERE (((qry_feed_from_sept.feed_xd_date)>=[Start_lookup_from] And (qry_feed_from_sept.feed_xd_date)<=[End_Look_up]));

    it seems to be working ok

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date payment falls into (2000)

    I could be wrong, but - given "dividends" and that the OP is from the UK - we may be dealing with Unit Trusts. This could mean that we are dealing with Units arising from reinvestment or bonuses - as well as those purchased. Hence CUMULATIVE UNITS. It's a pretty arcane subject, but HTH.
    Gre

  7. #7
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date payment falls into (2000)

    Elementary my dear watson that is spot on what im working on,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •