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

[mcupito]

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)ForfeitureDa te, (2)NAV_Date, (2)NetAssetValu e 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.

DateAdd("s", -1, DateAdd("q", DateDiff("q", "1/1/1900", Date), "1/1/1900"))

The query would return:

Forfeiture Date:  1/20/2013
Forfeited Units: 5
NetAssetValue: $1,000
NAV_Date: 12/31/2013

[Rabbit]

Please post the query code along with table definitions.

[mcupito]

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

[mcupito]

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:

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.

[Rabbit]

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.