By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,759 Members | 1,712 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,759 IT Pros & Developers. It's quick & easy.

Make query2 contain same rows as query1?

P: n/a
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 : )

May 19 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

May 19 '06 #2

P: n/a
Hi James,

Sorry it took a while to reply. Thanks for the help! Was a very
detailed response and I appreciate it. I have had a look at what you've
suggested, got the query up and running in the actual DB, however
unless im mistaken I will be running into almost the same problem I was
before with the crosstab queries, specifically that instead of them
generating a column with all the skills reviewed for that employee it
spreads them out, 1 skill review per column.

I think it may be best if i follow your advice at the end of your
response and create a query that will generate a temporary table with
the information and use that as the data source for my report.

Thanks for the help, it's helped put me on the right course,

Andrew

May 23 '06 #3

P: n/a
Andy wrote:
Hi James,

Sorry it took a while to reply. Thanks for the help! Was a very
detailed response and I appreciate it. I have had a look at what you've
suggested, got the query up and running in the actual DB, however
unless im mistaken I will be running into almost the same problem I was
before with the crosstab queries, specifically that instead of them
generating a column with all the skills reviewed for that employee it
spreads them out, 1 skill review per column.
Give an example of what you're trying to do. You may not have to give
up on crosstabs yet.

I think it may be best if i follow your advice at the end of your
response and create a query that will generate a temporary table with
the information and use that as the data source for my report.
My advice about the queries was not to create a temporary table. It
was to move your table data into the new structure. Don't resort to a
temporary table unless your other alternatives are worse.

Thanks for the help, it's helped put me on the right course,

Andrew


I'm happy to help,

James A. Fortune
CD********@FortuneJames.com

May 24 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.