435,404 Members | 1,873 Online
Need help? Post your question and get tips & solutions from a community of 435,404 IT Pros & Developers. It's quick & easy.

# Retrieve value from table with last day of each quarter as key

 100+ P: 294 For the life of me I cannot figure out how to do this for some reason. I have two tables: (1)AwardTbl and (2)NAV_Tbl. I am trying to retrieve the following information: (1)Forfeited Units, (1)ForfeitureDate, (2)NAV_Date, (2)NetAssetValue in a single query. I am trying to use the ForfeitureDate to get the NetAssetValue for the NAV_Date which the ForfeitureDate would use in it's calculation at the time of forfeiture. Let me provide an example of a successful run: User wants to find the forfeited units in the year 2013. All forfeitedunits with a date between 1/1/2013 and 12/31/2013 would be included, per employee. The ForfeitureDate for Jane Smith's awards is 1/20/2013, for example, and she forfeited 5 units. The NAV_Date used for that particular forfeiture date would be 12/31/2012 because we use the prior quarter's last day for the NAV_Date field, and let's give it a value of \$1,000. This is used, sometimes. I tried it using the forfeiture date instead of Date() and it didn't work. Expand|Select|Wrap|Line Numbers DateAdd("s", -1, DateAdd("q", DateDiff("q", "1/1/1900", Date), "1/1/1900")) The query would return: Expand|Select|Wrap|Line Numbers Forfeiture Date:  1/20/2013 Forfeited Units: 5 NetAssetValue: \$1,000 NAV_Date: 12/31/2013 Jan 22 '14 #1
4 Replies

 Expert Mod 10K+ P: 12,366 Please post the query code along with table definitions. Jan 22 '14 #2

 100+ P: 294 Expand|Select|Wrap|Line Numbers SELECT AssociateTbl.AstFirstName, AssociateTbl.AstLastName, PlanTbl.PlanDesc, AwardTbl.ForfeitDate, AwardTbl.ForfeitedUnits FROM (AwardTbl INNER JOIN AssociateTbl ON AwardTbl.EmployeeID = AssociateTbl.EmployeeID)  INNER JOIN PlanTbl ON AwardTbl.PlanID = PlanTbl.PlanID WHERE (((AwardTbl.ForfeitDate) Is Not Null)  AND ((AwardTbl.ForfeitedUnits)>0));   That's all I have so far for the code. It returns everything except for the NetAssetValue. Table Definitions AwardTbl ForfeitedUnits - Number [Single] ForfeitDate - Date/Time [Short Date] : Date units were forfeited NAV_Tbl (PK)NAV_Date: Date/Time [Short Date] : Ending date of valuation period - which is a quarterly basis NetAssetValue: [Currency] : Value of each unit Jan 22 '14 #3

 100+ P: 294 I got it working. I had to use a work-around which is what I always try to avoid, however I could not come to an alternate solution. I created a MakeTable Query called ForfeitTbl from all of the fields I required in the query, aside from the NAV_Tbl values Added a new date field to the ForfeitTbl which I used to store the ending NAV_Date's, and called it NAV_Dt (FK that I created, basically) Once the table was created, I have an update query that solely updates the NAV_Dt field in the ForfeitTbl to: Expand|Select|Wrap|Line Numbers DateAdd("s",-1,DateAdd("q",DateDiff("q","1/1/1900",[ForfeitTbl]![ForfeitDate]),"1/1/1900")) I then use this SELECT statement to query the records for all employees who have forfeited units and what the NetAssetValue of 1 awarded unit is per record. Please forgive my terrible explanation. I will re-configure my methods of performing this, and merely add a ForfeitNAV field to the AwardTbl, so it excuses the need to create a new table, and have an update query which updates the ForfeitNAV field when there are units to be forfeited. Jan 22 '14 #4

 Expert Mod 10K+ P: 12,366 I see no reason why you can't do it all in one query without creating tables or storing calculated data. Please show the code where you attempted to do everything in one query. Jan 23 '14 #5