crosstab query?

  • Thread starter Thread starter pat67
  • Start date Start date
P

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 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?
Click to expand...


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.
 
Post the SQL of your present crosstab query. Post what the output looks like
now and how you want it to look.
 
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 -
Click to expand...

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