crosstab query question.

[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

 

[Farid]

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

 

[pat67]

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

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.

 

[pat67]

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

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.

Never mind. i got it. thanks