should I use a crosstab, union, or other

[Guest]

What I'm trying to do is create a report that has the Month in the column,
plus if an amount was "won" or "lost" so it would look like something like
this:

Number Aug' 05 Won Aug' 05 Lost
123 500.00 1000.00
456 50.00 10.00
789 800.00 500.00

I got it to work this way but I have to re-type the order I want it in, in
the crosstab query, which I don't want to do every month.

Could you please advise. Is this the best way to do this?

I combined the month and the won or lost when I append and make my table like:
number Amount won_lost Date pk
123 500.00 won 08/10/2005 08/10/2005 Won
123 1000.00 lost 08/18/2005 08/10/2005 Lost

Any help is much appreciated!

 

[Duane Hookom]

Rather than using a month, consider the Month difference. Your column
headings would be created by the expression:

[Won_Lost] & " - " & DateDiff("m",[Date], Date())

This will create Column Headings like: "Won - 0" for the current month,
"Won - 1" for last month.