To build a cross tab query with details-CodePudding

I need to build a crosstab query in ms access but instead of the summarized info, I need to show the details.

I have a table like:

Date Teamname Teammemebername

The cross tab should have:

Date as the row heading
The team name as the column heading
And team member names in the summarize section

How can this be done?

ID  ScheduleDate    TeamCode    TeamMemberCode
5585    3/4/2022    NT      NW
5586    3/4/2022    USHRL   RN
5587    3/4/2022    USHRT   KN
5588    3/4/2022    USHRT   KI
5589    3/4/2022    USHRT   RF
5590    3/11/2022   NT      MF
5591    3/11/2022   USHRL   QD
5592    3/11/2022   USHRT   NW
5593    3/11/2022   USHRT   KN
5594    3/11/2022   USHRT   KI

CodePudding user response：

Incorrect first answer: TRANSFORM First(tblTeamdata.Teammemebername) AS FirstMembername SELECT tblTeamdata.DAT FROM tblTeamdata GROUP BY tblTeamdata.DAT PIVOT tblTeamdata.Teamname;

Since each cell in the cross tab can have multiple MemberCodes you will have to use a function to return a list of those names. The function shall have date and teamcode as parameters.

Function names(dat As Variant, team As Variant)
    Dim res$, sql$
    Dim rs As DAO.Recordset
    If IsNull(dat) Or IsNull(team) Then
        names = Null
    Else
        sql = "SELECT * FROM Teamdata"
        sql = sql & " Where ScheduleDate =#" & dat & "#"
        sql = sql & " AND TeamCode=""" & team & """"
        sql = sql & " Order by TeamMemberCode;"
        Set rs = CurrentDb.OpenRecordset(sql)
        Do Until rs.EOF
            If res <> "" Then res = res & ","
            res = res & rs!TeamMemberCode
            rs.MoveNext
        Loop
        rs.Close
        names = res
    End If
End Function

TRANSFORM names([ScheduleDate],[Teamcode]) AS Result SELECT TeamData.ScheduleDate FROM TeamData GROUP BY TeamData.ScheduleDate PIVOT TeamData.TeamCode;

The Result of the query with the above dataset will be:

ScheduleDate  NT    USHRL   USHRT
2022-03-04    NW    RN      KI,KN,RF
2022-03-11    MF    QD      KI,KN,NW