Using Dcount between two dates 1

[thembela]

I got a query that gives me the following results:

Month 2003 2004 2005
Aug 03 11,000
Aug 04 3,200
Aug 05 700

What formula can I use to change the query into 1 line of August so I can have all the values for 2003, 2004 and 2005 into one line: This is what I want to achieve:

Month 2003 2004 2005
Aug 11,000 3,200 700

 

[stephenk1973]

Left Trim the month field in the query.

select right(month,3),....

If this doesn't work post your sql.

All the best

Stephen

 

[thembela]

Nope it doesn't work.Instead I get the ff:

Month 2003 2004 2005
Aug 11,000
Aug 3,200
Aug 700

What I want is this:

Month 2003 2004 2005
Aug 11,000 3,200 700

Here's my SQL:

TRANSFORM Sum([Broker Information].API) AS SumOfAPI
SELECT (left(Format([EffMonth],"mmm"" '""yy"),3)) AS Expr1
FROM [Broker Information]
GROUP BY (Year([EffMonth])*12+Month([EffMonth])-1), (Format([EffMonth],"mmm"" '""yy"))
PIVOT Format([EffMonth],"yyyy") ;

 

[stephenk1973]

I think....


TRANSFORM Sum([Broker Information].API) AS SumOfAPI
SELECT Format([EffMonth],"mmm"") AS Expr1
FROM [Broker Information]
GROUP BY Format([EffMonth],"mmm"")
PIVOT Format([EffMonth],"yyyy") ;