423,309 Members | 2,163 Online
Need help? Post your question and get tips & solutions from a community of 423,309 IT Pros & Developers. It's quick & easy.

# Struggling with a query

 Expert 100+ P: 1,290 To cut things to the bone I have 3 tables. TblBoughtSold which lists the date that stock was purchased or sold and, culpa mia, the balance. Expand|Select|Wrap|Line Numbers BoughtSold     BoughtSoldID    Primary Key Autonumber     InvestmentID     BoughtSoldDate     Balance   This lists dividends received Expand|Select|Wrap|Line Numbers Dividends     DividendID    'Primary Key Autonumber     InvestmentID     DivdDate     UKInterest   This is the stock name Expand|Select|Wrap|Line Numbers Investments     InvestmentID   'Primary Key Autonumber     SecurityName   'Name of Investment   All three tables have the relationships enforced between the InvestmentIDs As I said it this is the simplified version. I am trying to establish the Balance of each item of stock on the date the dividend was paid. That is problem no 1. The second problem is that stock may have been sold and, due to a delay in the dividend payment, it appears that there was no stock at that Dividend Date. Given the correct balance I should be able to calculate the dividend rate. This is as close as I have got, but I should be getting 1 line for every dividend received, and I am only getting about 90% of the lines. Expand|Select|Wrap|Line Numbers SELECT Dividends.DividendID, Investments.InvestmentID,  Investments.SecurityName, Dividends.DivDate, Dividends.UKInterest,  BoughtSold.DateBoughtSold, BoughtSold.Balance,  IIf([Balance]>0,([UKInterest])/[Balance]) AS DivRate FROM (Investments INNER JOIN Dividends  ON Investments.InvestmentID = Dividends.InvestmentID) INNER JOIN BoughtSold  ON Investments.InvestmentID = BoughtSold.InvestmentID WHERE (((BoughtSold.DateBoughtSold)=DMax("DateBoughtSold","BoughtSold","InvestmentID = "  & [Investments]![InvestmentID] & " AND DateBoughtSold <= #" & [DivDate] & "#"))) ORDER BY Investments.InvestmentID, Dividends.DivDate;   Any help would be appreciated Phil Jul 13 '18 #1
16 Replies

 Expert Mod 5K+ P: 5,279 just on a lark, would it be advisable to add a field to the [BoughtSold] that would link the record to the dividend paid for that inventory? Expand|Select|Wrap|Line Numbers  BoughtSold      BoughtSoldID    Primary Key Autonumber      InvestmentID    Foreign Key Investments      fk_Dividends    Foreign Key Dividends      BoughtSoldDate      Balance would this allow you to at least solve your second problem? Jul 14 '18 #2

 Expert 100+ P: 1,290 Thanks zmbd. I think you are on the right track, but it needs to be the other way round. The way you have suggested would create a BoughtSold record for each Dividend. My suggestion is: Expand|Select|Wrap|Line Numbers Dividends     DividendID    'Primary Key Autonumber     InvestmentID     DivdDate     UKInterest     LastBoughtSoldID        That way the number of records in both tables remains the same, and, if the Balance is zero when the dividend is paid, it should be quite simple to find the previous BoughtSoldID for that Investment. I will wait to hear whether you agree with my reasoning, before making quite a big change to the Db. Again, thanks, Phil Jul 14 '18 #3

 Expert 100+ P: 1,290 Thanks again, zmbd, but I don' think your second post is that relevant. I obviously didn't make mkyself as clear as I should have done. When I mentioned stock, I really meant stocks & shares (Investments). So when a dividend is paid for a particular share, I am trying out how many shares my client owned on that payment date. Hopefully if he buys more shares, and assuming the dividend rate is constant, he would expect to get a proportionally bigger dividend. We need to work backwards. We know the dividend at a specific date, and when we solve the problem, we know how how many shares he owned, so we can calculate the rate. Don't forget that he buys and sells shares when he thinks he can make a profit, so there is no pattern in the Balance of shared owned. Dividends are paid on a more or less regular basis, but I emphasise, more or less. Hope this clarifies the question Phil Jul 14 '18 #5

Expert 100+
P: 1,290
Ah, the Rum-Cola and wine has made you quite relaxed. Excellent.

You obviously know a lot more about the market than I do, but we are talking English law, not American Law.

OK, the database has entries that go back over 20 years, so it is well established, and works well. So although I have simplified the question, the table names & field names are long established.

The nature if the stocks varies, but that is not relevant. Your second paragraph sums up the situation perfectly, the difference being that on 1st February we received \$370, and looking back, we found that we had 1000 shares on that date so we can calculate the rate at 3.7%.
On 4th June we received \$2,400 and found that we had held 6000 shares from May 1st, so the rate would be 4%.

I regret holding the Balance of shares (Bought - Sold) in the table. I am always preaching against holding calculated values in a table, but 24 years ago, when this Db was born, I knew even less about Access than I do now. Yes I know that must be hard to believe...

Attached is an Excel example from the database. The black figures are straight from the Db, and the red figures have been put in manually by looking at the balance of shares held prior to the dividend being paid.

We need to find those red values.

Thanks again,

Phil
Attached Files
 Sample.xlsx (11.7 KB, 4 views)
Jul 15 '18 #7

 Expert 100+ P: 1,290 Ah,in the end I gave up trying to use a query and had to resort to VBA. Fortunately it runs pretty quickly. Using this, we do not even need to change the table designs. I suspect my SQL is even worse than my VBA Here's the code if anyone is interested:- Expand|Select|Wrap|Line Numbers Function BalAtDate(InvestmentID As Long, DivDate As Date) As Currency '? BalAtDate(172, #11/04/06#)     Dim MyDb As Database     Dim RstBoughtSold As Recordset     Dim StrSQL As String     Dim LastDate As Date     Dim BalanceAtDate As Currency     Dim PreviousBalanceAtDate As Currency     Dim StrDivDate As String       StrSQL = "SELECT BoughtSold.* FROM BoughtSold "     StrSQL = StrSQL & "WHERE InvestmentID = " & InvestmentID     StrSQL = StrSQL & " Order By DateBoughtSold;"       Set MyDb = CurrentDb     Set RstBoughtSold = MyDb.OpenRecordset(StrSQL)       StrDivDate = CStr(DivDate)     With RstBoughtSold         Do Until .EOF             If DDiff("d", CStr(!DateBoughtSold), StrDivDate) >= 0 Then  ' DateDiff only works for US Dates                 Exit Do             End If             LastDate = !DateBoughtSold             BalanceAtDate = !Balance             If Nz(!Balance) <> 0 Then                 BalanceAtDate = !Balance                 PreviousBalanceAtDate = !Balance             Else                 BalanceAtDate = PreviousBalanceAtDate             End If             .MoveNext         Loop         .Close         Set RstBoughtSold = Nothing     End With       BalAtDate = BalanceAtDate   End Function   I might add that I have to use a UDF DDiff() as DateDiff() works with US dates, but not English dates DD/MM/YYYY. The DDiff function uses information from the windows profile to get information on the local date format, and hopefully is independent so "knows" how to interpret the date that you use. Thanks for your input Kind Regards Phil Jul 15 '18 #8

 Expert Mod 15k+ P: 30,996 PhilOfWalton: I might add that I have to use a UDF DDiff() as DateDiff() works with US dates, but not English dates DD/MM/YYYY. DateDiff() works with dates. There are no US or UK dates. There are only US and UK formatted date strings. If you're using date literals in SQL then you should always use a SQL standard date format for them. US dates formats happen to be supported whereas UK date formats certainly are not. Please see Literal DateTimes and Their Delimiters (#). This is not an area where experienced developers from anywhere in Europe should be struggling. Certainly not on Bytes.com where I repeat this point almost weekly. Moving on. I thought you had a query which produced for you the total stocks/shares held, at a particular date, as well as the dividends paid for those stocks/shares. From there, why is it not simply a case of dividing one by the other for the rate you're after. Jul 15 '18 #9

 Expert 100+ P: 1,290 I have often run into problems with trying to use DateDiff an UK dates as stored in a table (dd/mm/yyyy) Certainly using the data in the Excel file and Expand|Select|Wrap|Line Numbers If DateDiff("d", !DateBoughtSold, DivDate >= 0 Then ...   interprets the dates as if the were in US format mm/dd/yyyy, and gives the wrong result. So my UDF DDiff seems to give the correct results. Yes, a simple division is correctly giving me the dividend rate per share. Bit more fun now as I have a table of share prices recorded at different dates. so my next job is to work out the value of the shares held from the price immediately prior to the dividend date, and see what percentage he is getting. But that's tomorrow's entertainment Phil Jul 15 '18 #10

 Expert Mod 15k+ P: 30,996 Again, that's simply a misinterpretation of what's going on. Excel can store dates, and it can also store strings that look like dates. Take any date value in a cell and format it differently - it keeps the same date but shows a different string. The underlying content hasn't changed. Tables don't store dates as UK dates, or as US dates. They store dates or strings. They may have a format property such that when the value's viewed it appears as a string, but that is not the value itself. If your data is actually a string then you may see such issues. If it isn't then you won't. I say again, dates are stored as dates. They are never stored as the formatted value except when stored as strings, in which case they aren't really dates. If you think you have an exception then feel free to call me up to discuss it. I don't want you thinking it's so simply because I say it is. I'm confident it is of course. I just want you to understand the issue fully so you know why it is so. Jul 15 '18 #11

 Expert 100+ P: 1,290 I am aware that dates are stored as a floating point decimal number (Double) with the integer part the number of days from 30 Dec 1899 and the decimal part as the fraction of a day (hours, minutes & seconds), so the display of a date in a table depends purely on the region & the format. However, if I want the days difference between January 7th and January 13th, I type in the mug's way in the immediate window Expand|Select|Wrap|Line Numbers ? datediff("d", #07/01/18#, #13/01/18#)   and get -1990 I have to type in Expand|Select|Wrap|Line Numbers ? datediff("d", #01/07/18#, #01/13/18#)   to get the correct result of 6. so given those two dates - January 7th and January 13th, in the form of 07/01/18 and 13/01/18 (apparently) in my table, what is your method of getting DateDiff to work? Phil Jul 15 '18 #12

 Expert Mod 15k+ P: 30,996 That isn't what those two dates represent. I must admit that I wasn't aware that dates in VBA are region independent. I knew that in SQL they are, but look at the same literal date string in the QBE that you enter in SQL, and you'll see it's been regionalised. The two dates you represent as #07/01/18# and #13/01/18# are actually 1st July and 13th January. If you want them to be recognised correctly you have to provide a region independent string that is unambiguous. #7 Jan 2018# will work, as will #1/7/2018#, or even #2018\-1\-7#. Although I guess we none of us use actual date literals in VBA very much, I'm very surprised that is also region independent. I didn't know that before today. Jul 16 '18 #13

Expert 100+
P: 1,290
Actually looking again, it is weirder than I thought.
In the immediate window, the code
Expand|Select|Wrap|Line Numbers
1. ? Datediff("d", #07/01/18#, #13/01/18#)
2.
giving -1990 (about 5 years difference) implies it is interpreting it as
Expand|Select|Wrap|Line Numbers
1. ?Datediff("d",#01 Jul 2018#, #18 Jan 2013#)
2.
However
Expand|Select|Wrap|Line Numbers
1. ?Datediff("d", #07/01/18#, #11/01/18#)
2.
gives 123 which is the same as
Expand|Select|Wrap|Line Numbers
1. ?Datediff("d",#01 Jul 2018#, #01 Nov 2018#)
2.
ie here it is interpreting it as standard US Dates.

The following SQL gives the correct results:-

Expand|Select|Wrap|Line Numbers
1. SELECT TblDatesTest.*,
2. [Date2]-[Date1] AS Subtract,
3. DateDiff("d",[Date2],[Date1]) AS StdDateDiff,
4. DDiff("d",[Date2],[Date1]) AS UDFDDiff,
5. #1/7/2018#-#1/13/2018# AS FixedSubtract,
6. DateDiff("d",#1/7/2018#,#1/13/2018#) AS FixedStdDateDiff,
7. DDiff("d",#1/7/2018#,#1/13/2018#) AS FixedUDFDDiff
8. FROM TblDatesTest;
9.

All highly confusing, especially when you think you have entered the same dates in the immediate window as in your table, and get totally unpredictable results.

Phil
Attached Images
 DateQuery.jpg (31.7 KB, 169 views)
Jul 16 '18 #14

 Expert Mod 10K+ P: 12,247 Back to the query, the only reasons I can think of for the missing records are either there isn't at least one record in all three tables for each investment ID and/or some of the dividends have no earlier bought sold record. I would do a distinct count on the dividend id without any joins and then add in a table and filter at a time until I found which caused the records to disappear. Jul 16 '18 #15

 Expert Mod 15k+ P: 30,996 PhilOfWalton: Actually looking again, it is weirder than I thought. Yeah. Me too. I never even entered the dates with two-digit years. I've avoided that for a lot longer than two decades wherever possible. It just doesn't make sense to me to offer myself up to unnecessary problems. So, it does tell us that date literals in the Immediate Pane are treated unexpectedly. IE. VBA date literals are similar in that respect to those used in SQL. The obvious answer is to determine exactly how they're interpreted. That, or use a format that you know is always unambiguous. Dates are tricky in that if a date can be interpreted in UK format as well as US format then it will choose the latter. However, if either is impossible (EG. US #13/1/2018#.) then it will be treated by using the local format. This is great - except it is singularly responsible for most of the date problems people fall over, because it's so hard to identify. NB. Please don't overlook Rabbit's post on the main issue that came in before mine.. Jul 16 '18 #16

 Expert 100+ P: 1,290 @ Rabbit Thanks for suggestion, but in the end, as the query had to be updateable, I had to resort to the VBA solution. It's a bit slow, but, by limiting the records to a limited date range, just about acceptable. Phil Jul 17 '18 #17