crosstab query?

[pat67]

I use Access to run my pool league. I am trying to get if not a
crosstab query, some query whic will give me one team's wins vs.
another team. Right now I enter results and use a union query to show
them. Here is the format of the union query:

GameDate GameID HomeTeam Player Team Result
12/1 1 AA Joe BB Win
12/1 2 AA Bob BB Loss

I then ran a crosstab query to show wins per team vs. the home team.
Team is the row, wins the value and home team the column.

What I am looking for is a way to show that Team A had say 10 wins vs.
Team B and Team B had 12 wins vs. Team.

Is there a way to combine crosstab queries? If not any advice?

 

[pat67]

I use Access to run my pool league. I am trying to get if not a
crosstab query, some query whic will give me one team's wins vs.
another team. Right now I enter results and use a union query to show
them. Here is the format of the union query:

GameDate GameID HomeTeam Player Team Result
   12/1           1           AA           Joe      BB    Win
   12/1           2           AA           Bob     BB    Loss

I then ran a crosstab query to show wins per team vs. the home team.
Team is the row, wins the value and home team the column.

What I am looking for is a way to show that Team A had say 10 wins vs.
Team B and Team B had 12 wins vs. Team.

Is there a way to combine crosstab queries? If not any advice?

I actually have been able to do this with 2 union queries and a 3
select queries. I would like to have less steps if possible. Here are
my queries

union1

SELECT [Home] as Team, [HWins] as Won, [Away] as Opponent
FROM [qryHome_Wins_By_Week_3]
UNION ALL SELECT [Away] as Team, [AWins] as Won, [Home] as Opponent
FROM [qryAway_Wins_By_Week_3];

union2

SELECT [Away] as Team, [HWins] as Lost, [Home] as Opponent
FROM [qryHome_wins_By_Week_3]
UNION ALL SELECT [Home] as Team, [AWins] as Lost, [Away] as Opponent
FROM [qryAway_Wins_By_Week_3];

then I have a select query for each union query as follows

qry1

SELECT qryUnion_2.Team, qryUnion_2.Opponent, Sum(qryUnion_2.Won) AS
Wins
FROM qryUnion_2
GROUP BY qryUnion_2.Team, qryUnion_2.Opponent;

qry2

SELECT qryUnion_3.Team, qryUnion_3.Opponent, Sum(qryUnion_3.Lost) AS
Losses
FROM qryUnion_3
GROUP BY qryUnion_3.Team, qryUnion_3.Opponent;

I then do another select query to combine wins and losses queries.

 

[KARL DEWEY]

Post the SQL of your present crosstab query. Post what the output looks like
now and how you want it to look.

 

[pat67]

Post the SQL of your present crosstab query.  Post what the output looks like
now and how you want it to look.
--
Build a little, test a little.









- Show quoted text -

If you see my second post, I have done it without the crosstab and it
works fine. Just wondering if there are less steps.