By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,404 Members | 1,873 Online
Bytes IT Community
+ Ask a Question
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
  1. DateAdd("s", -1, DateAdd("q", DateDiff("q", "1/1/1900", Date), "1/1/1900"))
The query would return:
Expand|Select|Wrap|Line Numbers
  1. Forfeiture Date:  1/20/2013
  2. Forfeited Units: 5
  3. NetAssetValue: $1,000
  4. NAV_Date: 12/31/2013
Jan 22 '14 #1
Share this Question
Share on Google+
4 Replies


Rabbit
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
  1. SELECT AssociateTbl.AstFirstName, AssociateTbl.AstLastName, PlanTbl.PlanDesc, AwardTbl.ForfeitDate, AwardTbl.ForfeitedUnits
  2. FROM (AwardTbl INNER JOIN AssociateTbl ON AwardTbl.EmployeeID = AssociateTbl.EmployeeID) 
  3. INNER JOIN PlanTbl ON AwardTbl.PlanID = PlanTbl.PlanID
  4. WHERE (((AwardTbl.ForfeitDate) Is Not Null) 
  5. AND ((AwardTbl.ForfeitedUnits)>0));
  6.  
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
  1. 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

Rabbit
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

Post your reply

Sign in to post your reply or Sign up for a free account.