Crosstab Query Help

[dmeiser]

I have 3 tables containing the following information:

1) Job Categories & Descriptions
2) Pay rates for the job categories at specified time intervals - e.g.
- 6 mo, 1 yr, 2 yr - with a date field that marks when that pay rate
became effective
3) Employee Job Category, Job Cat start and end date, & date of hire.

What I need is to create a query that shows the job area, emp name, and
then employee pay for each month between two dates that are passed in
via parameters.

However, here's the wrinkle:

1) Because pay changes at scheduled intervals, I need to find the Date
Difference (datediff(whatever)) between the date of hire and the column
header.
2) Not every month is represented in any of the databases but pay
probably changed for at least one person, and, regardless, I need a sum
for each and every month.

Here's what I have so far:

TRANSFORM Max(IIf(DateDiff("y",[DOH],[Start Date])>2,[Two
Year],IIf(DateDiff("y",[DOH],[Start Date])>=1,[One
Year],IIf(DateDiff("m",[DOH],[Start Date])>=6,[Six
Month],[Start])))+[Differential]) AS Expr2
SELECT tblRates.[Job Area], tblTM.[Last Name], tblTM.[First Name]
FROM (tblJob INNER JOIN tblRates ON tblJob.[Job Area] = tblRates.[Job
Area]) INNER JOIN tblTM ON tblJob.[Job Area] = tblTM.[Job Area]
GROUP BY tblRates.[Job Area], tblTM.[Last Name], tblTM.[First Name]
PIVOT Format([Start Date],"mmm yyyy");

The [Start Date] Parameter is really just a placeholder until I can
figure out how to compare it to the column header. Another possibility
is just to use set dates (and therefore column headers) and change them
each budget year.

But I think the biggest problem is comparing the date of hire with the
column header.

Has anybody else encountered this problem?

How did one go about fixing it?

Any other thoughts?

 

[Duane Hookom]

Apparently you have used time intervals as field names. If I am correct,
consider changing this to a more normalized table structure. If you can't
change the table structure, then use a union query to create one record per
time interval, job category, and effective date.