Sorting for a report

  • Thread starter Thread starter Charger430
  • Start date Start date
C

Charger430

Is it possible to create a report that has four columns such as two grade
levels and by gender of students pulling from First Name, Last Name, Gender,
and Grade as fields in the table. Thanks for any help
 
How would you get 'two grade levels'? Post example of output and sample
data going in.
 
I would like to look some thing like this:

Seventh Boys Eighth Boys Seventh Girls Eighth Girls
name name name
name
name name name
name

etc on down the top line would be the titles and the names would be from
the input information. I have four fields: First Name Last Name Grade
Gender.
 
Try these two queries --
SELECT [Q].[Gender] & [Q].[Grade] AS GenderGrade, Q.[First Name] & " " &
Q.[Last Name] AS Full_Name, (SELECT COUNT(*) FROM [Charger430] Q1 WHERE
Q1.[Gender] = Q.[Gender] AND Q1.[Grade] = Q.[Grade] AND
Q1.[First Name] &Q1.[Last Name] < Q.[First Name]&Q.[Last Name] )+1 AS Rank
FROM Charger430 AS Q
ORDER BY [Q].[Gender] & [Q].[Grade], Q.[First Name] & Q.[Last Name];

SELECT
Max(IIf([Charger430_List].[GenderGrade]="BoysSeventh",[Charger430_List].[Full_Name],Null))
AS [Seventh Boys],
Max(IIf([Charger430_List_1].[GenderGrade]="GirlsSeventh",[Charger430_List_1].[Full_Name],Null))
AS [Seventh Girls],
Max(IIf([Charger430_List_2].[GenderGrade]="BoysEight",[Charger430_List_2].[Full_Name],Null))
AS [Eight Boys],
Max(IIf([Charger430_List_3].[GenderGrade]="GirlsEight",[Charger430_List_3].[Full_Name],Null)) AS [Eight Girls]
FROM ((Charger430_List LEFT JOIN Charger430_List AS Charger430_List_1 ON
Charger430_List.Rank = Charger430_List_1.Rank) LEFT JOIN Charger430_List AS
Charger430_List_2 ON Charger430_List.Rank = Charger430_List_2.Rank) LEFT JOIN
Charger430_List AS Charger430_List_3 ON Charger430_List.Rank =
Charger430_List_3.Rank
GROUP BY Charger430_List.Rank
ORDER BY Charger430_List.Rank;
 
Back
Top