Andy wrote:
Hi,
I've been using this newsgroup heaps recently but after vigorous
searching I can't find quite what I'm looking for:
I have a DB where there are employees who have their skills reviewed.
ie. Employee 1
Review 1:
Skill1=2
Skill2=2
Skill4=1
Review 2:
Skill1=2
Skill2=3
Skill3=2
Skill4=2
In the report I want to have a matrix come up with the latest 5 reviews
and show what the employee was ranked for each skill each time he was
reviewed
ie.
Skill Rev2 Rev1
1 2 2
2 3 2
3 2 0
4 2 1
I looked at crosstab queries but ran into the problem that the field
titles were always changing so I couldnt make my report that way. What
I have at the moment is 5 queries, the first one gets the skill ratings
from the latest review, the next one the skill ratings from the next
latest review etc. I use 5 subreports and put them along side each
other and this makes my report. The latest report is used to display
the Skill Column as well as the Rev2 column (in the example above).
My problem comes in the case where the number of skills has changed.
(ie. in the example there is Skill3 in the latest review but not in the
older one). This means that the older review ratings aren't necessarily
'lining up' to skill column as it comes out like this:
Skill Rev2 Rev1
1 2 2
2 3 2
3 2 1
4 2
Essentially I need a way of getting my older review queries to look at
the latest one, if the Skill column is not the same it needs to add it
in and make the rating 0.
Any suggestions how to do that? (or solve it some other way :Þ )
I would put all the reviews in one table after normalizing as follows:
tblEmployees
EID Autonumber (PK)
EFirstName
ELastName
tblSkills
SID Autonumber (PK)
SkillName
tblEmployeeSkills
ESID Autonumber (PK)
EID Long (FK)
SID Long (FK)
ReviewNumber Long
ReviewDate Date/Time
ReviewRating Long
tblEmployees
1 Joe Young
2 King Kong
3 The Hulk
tblSkills
1 Climb Buildings
2 Destroy Buildings
3 Strength
tblEmployeeSkills
1 1 1 1 1/1/06 7
2 1 2 1 1/1/06 1
3 1 3 1 1/1/06 3
4 2 1 1 1/2/06 10
5 2 2 1 1/2/06 3
6 2 3 1 1/2/06 8
7 3 1 2 3/1/06 10
8 3 2 2 3/1/06 9
9 3 3 2 3/1/06 10
10 3 1 1 1/1/06 9
Review results are added to tblEmployeeSkills. Don't reuse skill ID's.
qrySkillsCrosstab:
TRANSFORM Avg(ReviewRating) AS [The Value] SELECT
tblEmployees.EFirstName, tblEmployees.ELastName, tblSkills.SkillName
FROM (tblEmployeeSkills INNER JOIN tblSkills ON tblEmployeeSkills.SID =
tblSkills.SID) INNER JOIN tblEmployees ON tblEmployeeSkills.EID =
tblEmployees.EID GROUP BY tblEmployeeSkills.EID,
tblEmployees.EFirstName, tblEmployees.ELastName, tblSkills.SkillName
PIVOT tblEmployeeSkills.ReviewNumber;
!qrySkillsCrosstab:
EFirstName ELastName SkillName 1 2
Joe Young Climb Buildings 7 Null
Joe Young Destroy Buildings 1 Null
Joe Young Strength 3 Null
King Kong Climb Buildings 10 Null
King Kong Destroy Buildings 3 Null
King Kong Strength 8 Null
The Hulk Climb Buildings 9 10
The Hulk Destroy Buildings Null 9
The Hulk Strength Null 10
Then replace Avg(ReviewRating) with Nz(Avg(ReviewRating), 0) if you
want to get Nulls to show up as 0's. Immediately before the "GROUP BY"
-- as a warmup -- put "WHERE ReviewNumber Between 1 And 5 " to limit
the ReviewNumber range. Then try:
WHERE ReviewNumber Between (SELECT Max(ReviewNumber) FROM
tblEmployeeSkills) - 4 And (SELECT Max(ReviewNumber) FROM
tblEmployeeSkills)
That should show results for only the last five ReviewNumber's.
Finally, I changed the final part to:
PIVOT 'Rev' & tblEmployeeSkills.ReviewNumber;
so that 1 2 became Rev1 Rev2. Here's a function I use in some reports
to get arbitrary values from tables not in the Record Source into
control sources using SQL:
Public Function ReturnSQLResult(strSQL As String) As Variant
Dim MyDB As Database
Dim MyRS As Recordset
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If MyRS.RecordCount <> 0 Then
MyRS.MoveFirst
ReturnSQLResult = MyRS(0)
Else
ReturnSQLResult = Null
End If
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
End Function
It used the Fields collection to grab the first field asked for in the
SQL string. Maybe you can reference your five fields directly in a
second query that uses fixed aliases. I don't remember trying this
before.
Perhaps you can write some queries that will put your review tables
into a normalized table structure. One sneaky part was using
Avg(ReviewRating) when it is known that there is only one value for the
grouping as a pseudo-aggregrate. I hope this gives you a starting
point.
James A. Fortune
CD********@FortuneJames.com 
