Multiple Records 1 Field

[Ripper]

I need to create a report where I consolidate multiple records from 1 table
and join them to 1 record in another. The problem is, I need them to all be
joined into 1 record.

tblStudents (Name, LocID, Grade) - Single record per student
tblMods-Math (ModID, LocID, Mod) - Multiple mods for 1 kiddos is normal

Finished product needs to be

SomeQuery (Name, LocID, Grade, Mod1, Mod2, Mod3, Mod4, etc...)

How do I do that? I tried creating a crosstab query, but that didn't work out.

 

[Michel Walsh]

Rank the Mod values, per LocID. One possible solution is:

SELECT a.locID, a.mod, COUNT(*) as rank
FROM mods AS a INNER JOIN mods AS b
ON a.locID=b.locID AND a.mod >= b.mod
GROUP BY a.locID, a.mod



then, use the crosstab:

TRANSFORM LAST(mod)
SELECT name, students.locID, LAST(grade)
FROM students INNER JOIN savedQuery
ON students.locID = savedQuery.locID
GROUP BY name, students.locID
PIVOT rank


Note: you may have to use, instead:


TRANSFORM LAST(mod)
SELECT name, students.locID, grade
FROM students INNER JOIN savedQuery
ON students.locID = savedQuery.locID
GROUP BY name, students.locID, grade
PIVOT rank


since 'grade' seems a little bit suspicious to me about what it really does
in the whole picture.



Hoping it may help,
Vanderghast, Access MVP