Join query

[chun via AccessMonster.com]

Hello

I am having a problem here with the query.
This is my table with following fields

Fields - GroupNo,Grader,Points

GroupNo-Grader-Points
204-Jack- 20
204-Ryan-40
204-Tita-30

202-Jack-35
202-Ryan-24

205-Jack-56
205-Ryan-73
205-Tita-45

And I am trying to get this output
204-Jack-20-Ryan-40-Tita-30
202-Jack-35-Ryan-24
205-Jack-56-Ryan-73-Tita-45

This is the query I use:
SELECT tblGrade.GroupNo, tblGrade.Grader, tblGrade.Points, tblGrade_1.Grader,
tblGrade_1.Points, tblGrade_2.Grader, tblGrade_2.Points
FROM (tblGrade INNER JOIN tblGrade AS tblGrade_1 ON tblGrade.GroupNo =
tblGrade_1.GroupNo) INNER JOIN tblGrade AS tblGrade_2 ON tblGrade_1.GroupNo =
tblGrade_2.GroupNo

For some reason, it does not work and I don't understand why.
Can somebody please help me out. I would really appreciate your input.
Thanks

 

[Jamie Collins]

This is my table with following fields

Fields - GroupNo,Grader,Points

GroupNo-Grader-Points
204-Jack- 20
204-Ryan-40
204-Tita-30

202-Jack-35
202-Ryan-24

205-Jack-56
205-Ryan-73
205-Tita-45

And I am trying to get this output
204-Jack-20-Ryan-40-Tita-30
202-Jack-35-Ryan-24
205-Jack-56-Ryan-73-Tita-45

This is the query I use:
SELECT tblGrade.GroupNo, tblGrade.Grader, tblGrade.Points, tblGrade_1.Grader,
tblGrade_1.Points, tblGrade_2.Grader, tblGrade_2.Points
FROM (tblGrade INNER JOIN tblGrade AS tblGrade_1 ON tblGrade.GroupNo =
tblGrade_1.GroupNo) INNER JOIN tblGrade AS tblGrade_2 ON tblGrade_1.GroupNo =
tblGrade_2.GroupNo

For some reason, it does not work and I don't understand why.

There is a good reason why SQL (even the Access/Jet flavour <g>) does
not have a concatenation function. Try a google search using the words

sql concatenate 1NF violation

Jamie.

--

 

[chun via AccessMonster.com]

Thank you very much Jamie for your reply.

Actually I am sorry that I did not express my problem clearly here, I do not
have to concatenate them into one string...rather I have to combine the
records based on GroupNo, but in 7 different fields like this

GroupNo---Name1---Points1---Name2---Points2---Name3---Points3
204----------Jack-------20----------Ryan------40-----------Tita---------30
202----------Jack-------35----------Ryan------24
205----------Jack-------56----------Ryan------73-----------Tita---------45

Can somebody please help me with this.
THanks

This is my table with following fields

[quoted text clipped - 25 lines]

For some reason, it does not work and I don't understand why.

There is a good reason why SQL (even the Access/Jet flavour <g>) does
not have a concatenation function. Try a google search using the words

sql concatenate 1NF violation

Jamie.

--

 

[Gary Walter]

I don't see any easy *all-cases* way except to use a
ranking query (where each distinct name is
given a distinct "rank") then use the xtab method
that Duane has referenced earlier

http://www.tek-tips.com/faqs.cfm?fid=4524.

1) Create a table "tblXtabColumns" with one
text field "FldName"

Enter 2 records for "FldName"

Grader
Points

You wanted "Name1, Name2, ..."
but here we will get "Grader1, Grader2,..."
It makes no difference in meaning, plus
we are escaping any consequences from
reserved word "Name," even though I
doubt that might be the case here....



2) qrytblGradeWithRank

I'm going to make an assumption here
(I know that is bad) that you want all
the Grader names to align up in their
same column.

I've also assumed (there's that bad
thought process again) that "Rank"
will suffice if applied in reference to
the alpha sort of the names of the graders.

Just to test the above, I added the following
records to your example data

206 Tita 50
207 Abby 15
207 Ryan 35

SELECT
t1.GroupNo,
t1.Grader,
t1.Points,
(SELECT
Count(*) +1
FROM
[SELECT
DISTINCT
t.Grader
FROM
tblGrade As t]. Q
WHERE
Q.Grader<t1.Grader) AS Rank
FROM tblGrade As t1;


result from all example data:

GroupNo Grader Points Rank
204 Jack 20 2
204 Ryan 40 3
204 Tita 30 4
202 Jack 35 2
202 Ryan 24 3
205 Jack 56 2
205 Ryan 73 3
205 Tita 45 4
206 Tita 50 4
207 Abby 15 1
207 Ryan 35 3

the Rank will allow us to do our magic
in the crosstab....the bad news is that
crosstabs are really picky on what you
feed them, and typically with a query as
complicated as above, it usually makes
sense to save the results in a table, then
feed the table to the query. So....

3) qrymaktblGradeWithRank


SELECT
t1.GroupNo,
t1.Grader,
t1.Points,
(SELECT
Count(*) +1
FROM
[SELECT
DISTINCT
t.Grader
FROM
tblGrade As t]. Q
WHERE
Q.Grader<t1.Grader) AS Rank
INTO tblGradeWithRank
FROM tblGrade As t1;

This is all specious driven by your example
data. In real world there could be more than
one "Jack" and I would assume you would
have a table "tblGraders" where "Grader"
above would be a foreign key into the
primary key of "tblGraders" (whatever..)

And if one wanted to run this multiple times,
one would change above to an append query
(after running at least once so table exists), then
in code, one would empty the table, then run
the append query...

4) the magic crosstab

TRANSFORM
First(IIf([FldName]="Grader",q.[Grader],q.[Points]))
SELECT q.GroupNo
FROM tblXtabColumns, tblGradeWithRank AS q
GROUP BY q.GroupNo
PIVOT [FldName] & q.Rank
IN
("Grader1",
"Points1",
"Grader2",
"Points2",
"Grader3",
"Points3",
"Grader4",
"Points4");

(sorry, the query results will probably word-wrap badly)

GroupNo Grader1 Points1 Grader2 Points2 Grader3 Points3 Grader4 Points4
202 Jack 35 Ryan 24
204 Jack 20 Ryan 40
Tita 30
205 Jack 56 Ryan 73
Tita 45
206
Tita 50
207 Abby 15 Ryan 35

to be more "general," one should probably leave
out the IN clause, but "PointsN" will not show up
next to "GraderN"

If this was for a report, that would not be a problem...

If you were going to export to Excel, I suppose
you would want to add additional "Graderx/Pointsx"
to the IN clause to accomodate all possible combinations.

"chun wrote:>