Crosstab query help needed (1 Viewer)

[Sketchin]

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:

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:

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));

 

[plog]

A query can't create records for which there are none in the underlying datasource. You have 1 record in tblJoin_HQP_Project for each employee--that's as many as the query is going to display if you use just it the query's datasource.

Working backwards--you want 1 record for every employee/quarter. If John was employeed for 3 quarters, he should have 3 records, if Sally was there for a year, she should have 4 records, etc. The way you accomplish this is by creating a datasource that has all those permutations.

You have a datasource with all the employees you want to report on (tbl_JoinHQP_Projects).
Do you have a table with datasource with all the quarters you want to report on?

P.S. If you think your corss-tab runs slow now, the solution I am working towards is going to make it even slower

 

[Sketchin]

Working backwards--you want 1 record for every employee/quarter. If John was employeed for 3 quarters, he should have 3 records, if Sally was there for a year, she should have 4 records, etc. The way you accomplish this is by creating a datasource that has all those permutations.

I totally see what you are saying and have no idea how I would implement it. It also seems to go against proper database design as I would be storing calculated values, no?

 

[plog]

Not necessarily. You might be able to create a query from tbl_JoinHQP_Projects that shows the unique quarters.

Can you post a database with that table and some data?

 

[Sketchin]

Hi PLOG, I really appreciate your help with this!

Please see the attached database.

 

[plog]

This isn't the whole thing, but hopefully it will get you where you need to go. Like I said, you can't create rows in a query for which there aren't any in the table. Since you want to report on every Employee/Quarter permutation and you don't have a list of every Quarter, you need to make one. This SQL will do that:

SELECT Year([DateHired]) & "-Qtr " & DatePart("q",[DateHired]) AS TimeFrameName, CDate((DatePart("q",[DateHired])-1)*3+1 & "/1/" & Year([DateHired])) AS TimeFrameBegin, DateAdd("d",-1,DateAdd("m",1,DatePart("q",[DateHired])*3 & "/1/" & Year([DateHired]))) AS TimeFrameEnd
FROM tblJoin_HQP_Project
GROUP BY Year([DateHired]) & "-Qtr " & DatePart("q",[DateHired]), CDate((DatePart("q",[DateHired])-1)*3+1 & "/1/" & Year([DateHired])), DateAdd("d",-1,DateAdd("m",1,DatePart("q",[DateHired])*3 & "/1/" & Year([DateHired])));

Paste it into a query, and save it naming it 'sub_QuarterlyValue'. It will give you all the quarter names you had in your intial post and it gives the starting and ending dates of each quarter which will be used in determine if an employee was employed during that quarter.

Since that calculation is kind of complex, I created a function and put the logic in there. Paste the below code into a module and save it:

Public Function Is_EmployedInTimeFrame(in_Hired, in_Terminated, in_Date1, in_Date2)
  ' determines if employee was employeed between 2 dates (in_Date1 & in_Date2
  ' does this by breaking problem into 3 cases:
  ' 1-Hired during timeframe, 2-Terminated during timeframe, 3-hired before timeframe and terminated after timeframe
   
    ret = False                 ' default value--assumes not employed
    TerminatedAfter = False     ' determines if terminated after in_Date2, or if not terminated at all
    
    If IsNull(in_Terminated) Then TerminatedAfter = True Else If in_Terminated > in_Date2 Then TerminatedAfter = True
    ' Used in Case 3: sees if termination date is outside timeframe
    
    If ((in_Hired >= in_Date1) And (in_Hired <= in_Date2)) Then ret = True
    ' Case 1: if was hired in timeframe, returns true
    
    If IsNull(in_Terminated) = False Then If ((in_Terminated >= in_Date1) And (in_Terminated <= in_Date2)) Then ret = True
    ' Case 2: if has termination date, sees if was terminated in timeframe and if so returns true
    
    If ((in_Hired < in_Date1) And (TerminatedAfter)) Then ret = True
    ' Case 3: if hired before timeframe and not terminated or terminated after timeframe, returns true

    Is_EmployedInTimeFrame = ret
  

End Function

You pass it the hire and termination date of an employee and also the start and end dates of the time frame you want to look at and it returns True or False to say if they were employed during the timeframe.

Lastly, the below code will be the query you use in creating your crosstab:

SELECT tblJoin_HQP_Project.ID, sub_QuarterlyValue.TimeFrameName, -25000*Is_EmployedInTimeFrame([DateHired],[DateTerminated],[TimeFrameBegin],[TimeFrameEnd]) AS HQP_Value
FROM tblJoin_HQP_Project, sub_QuarterlyValue;

Paste that into a query and run it. You can then turn it into a crosstab to replace the one you initially posted.

 

[Sketchin]

Thank you again for this, it worked like a charm!

My next question is how to sum each column so that I end up with just 1 row and a summary of values in each column?

 

[plog]

You would use the last SQL I gave you and sum in that query. If its one row, why do you need it horizontally instead of vertically? I fear you are using cross-tabs as a crutch to get data into a format you are used to (Excel).

 

[Sketchin]

That's a good question....I guess I really just need each quarter as a row, with the sum of values in each row.

I have a new PITA problem with linking this query to excel. Apparantly I can't do this because the query is based on a function! :banghead: