crosstab query question.

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

pat67

I am trying to do a crosstab for weekly results from my pool league. I
can do wins and losses separately but not together. Any ideas? I am
using a query that shows weekly reuslts so it would be like this


GameDate Name Won Lost
9-13 Bob 3 1
9-20 Bob 2 2
9-27 Bob 3 1

What I can do is this for wins

9-13 9-20 9-27
Bob 3 2 3

I know you can't have two values in a crosstab so what I am looking
for is this

9-13 9-20 9-27
Bob 3-1 2-2 3-1
 
Hi,
try this:

TRANSFORM First([Won] & "-" & [lost]) AS result
SELECT Table1.Name
FROM Table1
GROUP BY Table1.Name
PIVOT Table1.GameDate;

Then you get as result:

Name 9-13 9-20 9-27
Bob 3-1 2-2 3-1
 
Hi,
try this:

TRANSFORM First([Won] & "-" & [lost]) AS result
SELECT Table1.Name
FROM Table1
GROUP BY Table1.Name
PIVOT Table1.GameDate;

Then you get as result:

Name    9-13    9-20    9-27
Bob     3-1     2-2     3-1
Click to expand...

That works. But can i incorporate the Schedule table so any weeks not
yet played will show as blan. Reason is I want to create a report and
each date is a field. I don't want to have to add a field every week.
 
Hi,
try this:
Click to expand...
TRANSFORM First([Won] & "-" & [lost]) AS result
SELECT Table1.Name
FROM Table1
GROUP BY Table1.Name
PIVOT Table1.GameDate;
Click to expand...
Then you get as result:
Click to expand...
Name    9-13    9-20    9-27
Bob     3-1     2-2     3-1
Click to expand...

That works. But can i incorporate the Schedule table so any weeks not
yet played will show as blan. Reason is I want to create a report and
each date is a field. I don't want to have to add a field every week.
Click to expand...

Never mind. i got it. thanks
 
Back
Top