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

Show more relevant field

P: n/a
Ray
I need to create a report showing the candidate, class, score of judge 1, 2,
3, 4. Currently, I manage to obtain the result of candidate and score of
judge 1, 2, 3 and 4 but not the class. I would appreciate any suggestions to
accomplish this job.

I have a table containing the details of candidate, class, score of judge 1,
2, 3 and 4. Each record represents the score of each judge for each
candidate for a particular date. Each judge may have more than one score for
each candidate in different dates and need to show the latest one only.
Below are nested queries I currently use.

--------------------------------------------
First query to find out the latest scores of each judges for each candidate
from the table, tblScore.

SELECT tblScore.ClassID, tblScore.CandidateID, tblScore.JudgeID,
Max(tblScore.Updated) AS MaxOfUpdated
FROM qryJudge INNER JOIN (qryCandidate INNER JOIN tblScore ON
qryCandidate.CandidateID = tblScore.CandidateID) ON qryJudge.JudgeID =
tblScore.JudgeID
GROUP BY tblScore.ClassID, tblScore.CandidateID, tblScore.JudgeID;

----------------------------------------
Second query selects the relevant record if the candidate is active.
SELECT TqryScore0.CandidateID, TqryScore0.JudgeID, tblScore.Score,
TqryScore0.MaxOfUpdated, tblScore.Active
FROM TqryScore0 INNER JOIN tblScore ON (TqryScore0.ClassID =
tblScore.ClassID) AND (TqryScore0.CandidateID = tblScore.CandidateID) AND
(TqryScore0.MaxOfUpdated = tblScore.Updated) AND (TqryScore0.JudgeID =
tblScore.JudgeID)
WHERE (((tblScore.Active)=-1));

----------------------------------------------------------
Third query twists the result to the required format.

TRANSFORM First(TqryScore1.Score) AS [The Value]
SELECT TqryScore1.CandidateID
FROM TqryScore1
GROUP BY TqryScore1.CandidateID
PIVOT TqryScore1.JudgeID In ("1","2","3","4");

------------------------------------------------------

Thanks,

Ray
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
In query 2, why don't you include the ClassID in the SELECT clause,
since it is in the 1st query? Then include it in the crosstab query
(3rd query).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Ray wrote:
I need to create a report showing the candidate, class, score of judge 1, 2,
3, 4. Currently, I manage to obtain the result of candidate and score of
judge 1, 2, 3 and 4 but not the class. I would appreciate any suggestions to
accomplish this job.

I have a table containing the details of candidate, class, score of judge 1,
2, 3 and 4. Each record represents the score of each judge for each
candidate for a particular date. Each judge may have more than one score for
each candidate in different dates and need to show the latest one only.
Below are nested queries I currently use.

--------------------------------------------
First query to find out the latest scores of each judges for each candidate
from the table, tblScore.

SELECT tblScore.ClassID, tblScore.CandidateID, tblScore.JudgeID,
Max(tblScore.Updated) AS MaxOfUpdated
FROM qryJudge INNER JOIN (qryCandidate INNER JOIN tblScore ON
qryCandidate.CandidateID = tblScore.CandidateID) ON qryJudge.JudgeID =
tblScore.JudgeID
GROUP BY tblScore.ClassID, tblScore.CandidateID, tblScore.JudgeID;

----------------------------------------
Second query selects the relevant record if the candidate is active.
SELECT TqryScore0.CandidateID, TqryScore0.JudgeID, tblScore.Score,
TqryScore0.MaxOfUpdated, tblScore.Active
FROM TqryScore0 INNER JOIN tblScore ON (TqryScore0.ClassID =
tblScore.ClassID) AND (TqryScore0.CandidateID = tblScore.CandidateID) AND
(TqryScore0.MaxOfUpdated = tblScore.Updated) AND (TqryScore0.JudgeID =
tblScore.JudgeID)
WHERE (((tblScore.Active)=-1));

----------------------------------------------------------
Third query twists the result to the required format.

TRANSFORM First(TqryScore1.Score) AS [The Value]
SELECT TqryScore1.CandidateID
FROM TqryScore1
GROUP BY TqryScore1.CandidateID
PIVOT TqryScore1.JudgeID In ("1","2","3","4");

Nov 13 '05 #2

P: n/a
Ray
MG,

Thanks for your suggestion. I can include ClassID in query 2 but it cannot
display in the crosstab query 3. Is there any way to include/display it in
query 3 once ClassID is in query 2?

Thanks,

Ray

"MGFoster" <me@privacy.com> wrote in message
news:OC*****************@newsread1.news.pas.earthl ink.net...
In query 2, why don't you include the ClassID in the SELECT clause, since
it is in the 1st query? Then include it in the crosstab query (3rd
query).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Ray wrote:
I need to create a report showing the candidate, class, score of judge 1,
2, 3, 4. Currently, I manage to obtain the result of candidate and score
of judge 1, 2, 3 and 4 but not the class. I would appreciate any
suggestions to accomplish this job.

I have a table containing the details of candidate, class, score of judge
1, 2, 3 and 4. Each record represents the score of each judge for each
candidate for a particular date. Each judge may have more than one score
for each candidate in different dates and need to show the latest one
only. Below are nested queries I currently use.

--------------------------------------------
First query to find out the latest scores of each judges for each
candidate from the table, tblScore.

SELECT tblScore.ClassID, tblScore.CandidateID, tblScore.JudgeID,
Max(tblScore.Updated) AS MaxOfUpdated
FROM qryJudge INNER JOIN (qryCandidate INNER JOIN tblScore ON
qryCandidate.CandidateID = tblScore.CandidateID) ON qryJudge.JudgeID =
tblScore.JudgeID
GROUP BY tblScore.ClassID, tblScore.CandidateID, tblScore.JudgeID;

----------------------------------------
Second query selects the relevant record if the candidate is active.
SELECT TqryScore0.CandidateID, TqryScore0.JudgeID, tblScore.Score,
TqryScore0.MaxOfUpdated, tblScore.Active
FROM TqryScore0 INNER JOIN tblScore ON (TqryScore0.ClassID =
tblScore.ClassID) AND (TqryScore0.CandidateID = tblScore.CandidateID) AND
(TqryScore0.MaxOfUpdated = tblScore.Updated) AND (TqryScore0.JudgeID =
tblScore.JudgeID)
WHERE (((tblScore.Active)=-1));

----------------------------------------------------------
Third query twists the result to the required format.

TRANSFORM First(TqryScore1.Score) AS [The Value]
SELECT TqryScore1.CandidateID
FROM TqryScore1
GROUP BY TqryScore1.CandidateID
PIVOT TqryScore1.JudgeID In ("1","2","3","4");

Nov 13 '05 #3

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sure just put it in the SELECT & GROUP BY clauses. I'm assuming the 2nd
query is named "TqryScore1."

TRANSFORM First(TqryScore1.Score) AS [The Value]
SELECT TqryScore1.ClassID, TqryScore1.CandidateID
FROM TqryScore1
GROUP BY TqryScore1.ClassID, TqryScore1.CandidateID
PIVOT TqryScore1.JudgeID In ("1","2","3","4");

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQls3T4echKqOuFEgEQL+6wCgk65Lp4vbm48D8NgXEDyoeX MNsAUAoPrA
Q1pGKSai6yP6G26zech+GxOF
=Wg/I
-----END PGP SIGNATURE-----

Ray wrote:
MG,

Thanks for your suggestion. I can include ClassID in query 2 but it cannot
display in the crosstab query 3. Is there any way to include/display it in
query 3 once ClassID is in query 2?

Thanks,

Ray

"MGFoster" <me@privacy.com> wrote in message
news:OC*****************@newsread1.news.pas.earthl ink.net...
In query 2, why don't you include the ClassID in the SELECT clause, since
it is in the 1st query? Then include it in the crosstab query (3rd
query).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Ray wrote:
I need to create a report showing the candidate, class, score of judge 1,
2, 3, 4. Currently, I manage to obtain the result of candidate and score
of judge 1, 2, 3 and 4 but not the class. I would appreciate any
suggestions to accomplish this job.

I have a table containing the details of candidate, class, score of judge
1, 2, 3 and 4. Each record represents the score of each judge for each
candidate for a particular date. Each judge may have more than one score
for each candidate in different dates and need to show the latest one
only. Below are nested queries I currently use.

--------------------------------------------
First query to find out the latest scores of each judges for each
candidate from the table, tblScore.

SELECT tblScore.ClassID, tblScore.CandidateID, tblScore.JudgeID,
Max(tblScore.Updated) AS MaxOfUpdated
FROM qryJudge INNER JOIN (qryCandidate INNER JOIN tblScore ON
qryCandidate.CandidateID = tblScore.CandidateID) ON qryJudge.JudgeID =
tblScore.JudgeID
GROUP BY tblScore.ClassID, tblScore.CandidateID, tblScore.JudgeID;

----------------------------------------
Second query selects the relevant record if the candidate is active.
SELECT TqryScore0.CandidateID, TqryScore0.JudgeID, tblScore.Score,
TqryScore0.MaxOfUpdated, tblScore.Active
FROM TqryScore0 INNER JOIN tblScore ON (TqryScore0.ClassID =
tblScore.ClassID) AND (TqryScore0.CandidateID = tblScore.CandidateID) AND
(TqryScore0.MaxOfUpdated = tblScore.Updated) AND (TqryScore0.JudgeID =
tblScore.JudgeID)
WHERE (((tblScore.Active)=-1));

----------------------------------------------------------
Third query twists the result to the required format.

TRANSFORM First(TqryScore1.Score) AS [The Value]
SELECT TqryScore1.CandidateID
FROM TqryScore1
GROUP BY TqryScore1.CandidateID
PIVOT TqryScore1.JudgeID In ("1","2","3","4");


Nov 13 '05 #4

P: n/a
Ray
MG,

Many thanks for your useful advice.

Ray

"MGFoster" <me@privacy.com> wrote in message
news:4H*****************@newsread2.news.pas.earthl ink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sure just put it in the SELECT & GROUP BY clauses. I'm assuming the 2nd
query is named "TqryScore1."

TRANSFORM First(TqryScore1.Score) AS [The Value]
SELECT TqryScore1.ClassID, TqryScore1.CandidateID
FROM TqryScore1
GROUP BY TqryScore1.ClassID, TqryScore1.CandidateID
PIVOT TqryScore1.JudgeID In ("1","2","3","4");

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQls3T4echKqOuFEgEQL+6wCgk65Lp4vbm48D8NgXEDyoeX MNsAUAoPrA
Q1pGKSai6yP6G26zech+GxOF
=Wg/I
-----END PGP SIGNATURE-----

Ray wrote:
MG,

Thanks for your suggestion. I can include ClassID in query 2 but it
cannot display in the crosstab query 3. Is there any way to
include/display it in query 3 once ClassID is in query 2?

Thanks,

Ray

"MGFoster" <me@privacy.com> wrote in message
news:OC*****************@newsread1.news.pas.earthl ink.net...
In query 2, why don't you include the ClassID in the SELECT clause, since
it is in the 1st query? Then include it in the crosstab query (3rd
query).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Ray wrote:

I need to create a report showing the candidate, class, score of judge
1, 2, 3, 4. Currently, I manage to obtain the result of candidate and
score of judge 1, 2, 3 and 4 but not the class. I would appreciate any
suggestions to accomplish this job.

I have a table containing the details of candidate, class, score of
judge 1, 2, 3 and 4. Each record represents the score of each judge for
each candidate for a particular date. Each judge may have more than one
score for each candidate in different dates and need to show the latest
one only. Below are nested queries I currently use.

--------------------------------------------
First query to find out the latest scores of each judges for each
candidate from the table, tblScore.

SELECT tblScore.ClassID, tblScore.CandidateID, tblScore.JudgeID,
Max(tblScore.Updated) AS MaxOfUpdated
FROM qryJudge INNER JOIN (qryCandidate INNER JOIN tblScore ON
qryCandidate.CandidateID = tblScore.CandidateID) ON qryJudge.JudgeID =
tblScore.JudgeID
GROUP BY tblScore.ClassID, tblScore.CandidateID, tblScore.JudgeID;

----------------------------------------
Second query selects the relevant record if the candidate is active.
SELECT TqryScore0.CandidateID, TqryScore0.JudgeID, tblScore.Score,
TqryScore0.MaxOfUpdated, tblScore.Active
FROM TqryScore0 INNER JOIN tblScore ON (TqryScore0.ClassID =
tblScore.ClassID) AND (TqryScore0.CandidateID = tblScore.CandidateID)
AND (TqryScore0.MaxOfUpdated = tblScore.Updated) AND (TqryScore0.JudgeID
= tblScore.JudgeID)
WHERE (((tblScore.Active)=-1));

----------------------------------------------------------
Third query twists the result to the required format.

TRANSFORM First(TqryScore1.Score) AS [The Value]
SELECT TqryScore1.CandidateID
FROM TqryScore1
GROUP BY TqryScore1.CandidateID
PIVOT TqryScore1.JudgeID In ("1","2","3","4");



Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.