Can anyone take a look at this dbs

[djclntn]

I come here with a question – one that I may have posed once before. I have a Playoffs Table in my NHL (Hockey) with just the Teams playing against one another ending Series Results. A little Hockey 101: there are 3-rounds (including the Stanley Cup) in the NHL playoffs, but we only need to focus on the first round. Unlike in the Regular Season when Teams played one another one team is always on the Road & the opposing Team was at Home. So in my Reg Season Table results I have a ‘RoadHome field. In the Playoffs Teams play each other & it’s the best out of 7-games between 2-teams & the winner Teams advances to Round-2, etc. Right now instead of having a “RoadHome’ field I instead have a ‘AOrB” fld because in the playoffs 2-Team play 7-games & goes back & forth between the Team’s venues. Here’s how my Playoff Table looks:

• Round
• AOrB
• Rank
• Conf
• Div
• Team
• SeriesResults

What I would like to have & know how to create is a query which shows the 2-Teams playing against one another (A & B) in one Row/Record. I don’t believe I’m explaining myself clearly enough here, I want to see it almost like in a CrossTab Query where the 2-teams playing are in one row/Record. I know what I want, but I’m not able to express what it is I’m trying to achieve. Below I provide the dbs; might someone take a look & ask me to father explain my mission?

Thanks

[June7]

Not clear to me which team plays against which team.

A field for game number would make this much simpler.

[djclntn]

June,
I updated the db by adding a 'GmNmbr' fld. If you will, take a look & can you advise me on what do I do next, please.

Thanks

[June7]

Now see if crosstab gets you want you want:
TRANSFORM First([Playoffs Query].[ATeam]) AS FirstOfATeam
SELECT [Playoffs Query].[Round], [Playoffs Query].[GmNmbr]
FROM [Playoffs Query]
GROUP BY [Playoffs Query].[Round], [Playoffs Query].[GmNmbr]
PIVOT [Playoffs Query].[AOrB];

[djclntn]

Ok June, it worked somwhat. However, I also want to see the 'SeriesResults for Team A & for Team B.

I tried addind 'SeriesReslts as such:
TRANSFORM First([Playoffs Query].[ATeam]) AS FirstOfATeam
SELECT [Playoffs Query].[Round], [Playoffs Query].[GmNmbr], [Playoffs Query].[SeriesResults]
FROM [Playoffs Query]
GROUP BY [Playoffs Query].[Round], [Playoffs Query].[GmNmbr], [Playoffs Query].[SeriesResults]
PIVOT [Playoffs Query].[AOrB];
This didn't work

[June7]

Do another crosstab query:

TRANSFORM First([Playoffs Query].[SeriesResults]) AS FirstOfSeriesResults
SELECT [Playoffs Query].Round, [Playoffs Query].[GmNmbr]
FROM [Playoffs Query]
GROUP BY [Playoffs Query].Round, [Playoffs Query].[GmNmbr]
PIVOT [Playoffs Query].[AOrB];

Now join my first crosstab with the second crosstab:

SELECT PlayoffsResults_Crosstab.Round, PlayoffsResults_Crosstab.GmNmbr, PlayoffsResults_Crosstab.A, PlayoffsResults_Crosstab.B, PlayoffsTeams_Crosstab.A, PlayoffsTeams_Crosstab.B
FROM PlayoffsTeams_Crosstab INNER JOIN PlayoffsResults_Crosstab ON (PlayoffsTeams_Crosstab.GmNmbr = PlayoffsResults_Crosstab.GmNmbr) AND (PlayoffsTeams_Crosstab.Round = PlayoffsResults_Crosstab.Round);