I need help creating a cross tab query that will calculate the value of a person who is hired and terminated between 2 dates.
What I currently track in a table is the date someone is hired, and the date someone is terminated. We call these people HQP - highly qualified people. Every quarter between those dates, is assigned a value of $25,000, so if someone was employed for 4 quarters, their total value is $100k.
I have a crosstab query that has a row for each HQP and a column for each quarter. My calculated value should be 25k for each quarter that a person is employed, but is currently only showing 25k for the 1st quarter they are employed. I have attached a picture to show this query result.
Here is the SQL of the crosstab:
Here is the SQL of the query that feeds the crosstab:
What I currently track in a table is the date someone is hired, and the date someone is terminated. We call these people HQP - highly qualified people. Every quarter between those dates, is assigned a value of $25,000, so if someone was employed for 4 quarters, their total value is $100k.
I have a crosstab query that has a row for each HQP and a column for each quarter. My calculated value should be 25k for each quarter that a person is employed, but is currently only showing 25k for the 1st quarter they are employed. I have attached a picture to show this query result.
Here is the SQL of the crosstab:
Code:
TRANSFORM Sum(qryHQPSplitIntoQuarters.HQPValue) AS SumOfHQPValue
SELECT qryHQPSplitIntoQuarters.ID AS HQP
FROM qryHQPSplitIntoQuarters
GROUP BY qryHQPSplitIntoQuarters.ID
PIVOT qryHQPSplitIntoQuarters.QuarterHired;
Here is the SQL of the query that feeds the crosstab:
Code:
SELECT tblJoin_HQP_Project.ID, tblJoin_HQP_Project.DateHired, tblJoin_HQP_Project.DateTerminated, IIf(IsNull(DateDiff("d",[datehired],[dateTerminated])),(DateDiff("d",[datehired],Date())),(DateDiff("d",[datehired],[dateTerminated])))
AS TotalDays, Format([datehired],"yyyy") & "-" & "Qtr " & Format([datehired],"q")
AS QuarterHired, 25000
AS HQPValue, (IIf(IsNull(DateDiff("d",[datehired],[dateTerminated])),(DateDiff("d",[datehired],Date())),(DateDiff("d",[datehired],[dateTerminated]))))/91
AS NumberOfQtrs
FROM tblJoin_HQP_Project
GROUP BY tblJoin_HQP_Project.ID, tblJoin_HQP_Project.DateHired, tblJoin_HQP_Project.DateTerminated, IIf(IsNull(DateDiff("d",[datehired],[dateTerminated])),(DateDiff("d",[datehired],Date())),(DateDiff("d",[datehired],[dateTerminated]))), Format([datehired],"yyyy") & "-" & "Qtr " & Format([datehired],"q"), 25000, (IIf(IsNull(DateDiff("d",[datehired],[dateTerminated])),(DateDiff("d",[datehired],Date())),(DateDiff("d",[datehired],[dateTerminated]))))/91
HAVING (((tblJoin_HQP_Project.ID)=1));