Search results

Sure, Doc_Man, no problem, and thank you once again for the detailed breakdown. Would have been going around in circles much longer if not fo that. Sanjay

One bit of advice I received was to reduce the "Else's" in my code, and use "ElseIf" instead.

The_Doc_Man, Thank you for a very insightful, detailed breakdown of the approach to solving my problem. I read up a bit on using recordsets, and managed to write a VBA module that actually did the job. I am attaching the accdb file here for your perusal. I have put the procedure into a macro...

I just read through your method again, after doing some reading on recordsets. Thank you very much for this. I think I should be able to progress on this, and will keep you updated on the progress.

My apologies to all for the unintended breach. Thank you for the detailed break-down. I will start work on this and I hope I can come back with questions if I hit barriers. Sanjay

This is correct, and it is also correct that in some cases we need to split the purchase transactions, and in other cases the redemption transactions. I can, of course do this manually in excel, but it takes forever. I was hoping that I could get some pointers on implementing this in Access...

I have a series of transactions across various customer folios, in different mutual fund products, which are characterized by the fields [Folio_No] and [Prod_Code]. In the appended files, I have restricted the data to one Folio_No and Prod_Code, for simplicity. Transactions can either be buys...

The database is attached. The query is NAV_Top_1 Sanjay

In the attached ACCESS Db, I have a price table (NAV) with fields [Product Code], that indicates the product, [NAV Date] that indicates the price date, and [NAV] that indicates the price per unit for that product for that day. This table has a few gaps, largely because in many products (I have...

Thank you everyone for the inputs. Pat, the autonumber didn't work for me- it did the job in providing an identifier, but for whatever reason, when I fed this query into another append query, I got an "Overflow" message. The earlier workaround I had of creating a different date counter, taking...

Thanks, Pat. The date is not unique in that you can have two transactions (a purchase and a switch) on the same date, but the combination of [Ttype] and [TDate] is unique. The problem of course is in setting the criteria for the running total, as this is not a number or a date. One experiment...

In Excel, the formulae are different, and as I had said in my first post, it is easy to achieve the desired outcome there. I have found a work-around for MS Access, but it is a two step process. I first need to introduce an autonumber field (I've called this SCount) in the FiFO_Live_Temp table...

Dear Arnelgp, It is working now, but the results are exactly the same as the Dsum. The running total is the same for the first two rows- 6283.337, whereas it should be 4732.608 for the first row and 6283.337 for the second. Sanjay

Dear arnelgp, With this the overall query should be SELECT FIFO_Live_Temp.[Folio No], FIFO_Live_Temp.[Product Code], FIFO_Live_Temp.Ttype, FIFO_Live_Temp.TDate, Avg(FIFO_Live_Temp.NAV) AS NAV, Sum(FIFO_Live_Temp.[Live Units]) AS Units, Sum(FIFO_Live_Temp.[Live Amount]) AS Amount...

In the attached Access Database ("Cumulative Illustration"), I have a table FiFO_Temp_Live, which contains purchase transactions by folio (Field [Folio No]), product purchased (Field [Product Code]), Investor (Field [Mod PAN]) date (Field [TDate]), Purchase Amount (Field [Amount]), Price of...

Thank you, Colin. This seems to work. I will run it on the full table, and then start groping forward to the 'end result'! Sanjay

I have two tables, PASTMONTH, which has all dates of the last month (in this case, July 2017) and ALLFUNDS_NAV, which has the net asset values (NAV) of two funds, since their inception. I have attached a database (Access 2013) with these two tables, and the query described below, for reference...

Ok, I've figured out the SelectSql query part, to get it to filter the recordset, referencing the two distinct values in the [Match Code] field of Table XIRR_Array. I did this by modifying the function arguments to take another field called "MatchCode_Value", and by setting...

There is no form here; the database comprises a table XIRR_Array with three fields: [Match Code], [CFlow] and [TDate]. We need to get an XIRR function that can, for each [Match Code], give us an XIRR, based on the [CFlow] and [TDate] values associated with that [Match Code]. In this table...

Jan, thanks for the observation. When I do SelectSql = "SELECT CFlow, TDate FROM XIRR_Array WHERE [Match Code]= '" & MatchCode & "' ORDER BY TDate" and I also do a MsgBox(SelectSql) I get the following as my SelectSql: SELECT CFlow, TDate FROM XIRR_Array WHERE [Match Code]='[Match Code]' ORDER...