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

On Dec 18 2011, 5:23*pm, Farid <s.s...@freenet.de> wrote:
> 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.

On Jan 4, 4:28*pm, pat67 <pbus...@comcast.net> wrote:
> On Dec 18 2011, 5:23*pm, Farid <s.s...@freenet.de> wrote:
>
> > 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