VERITICAL & HORIZONTAL REPORT

[zyus]

My sample data is as follows

Name Approvedate Defaultdate
Mr Y 01-01-2007 30-04-2008
Mr X 01-01-2008 31-05-2009

In my report i want to group by approvedate (rows) and group by defaultdate
as column as per below (without having to hardcoded defaultdt column in
textbox)

Apr date\Defaultdt
2008 2009
2007 1
2008 1

Hope my explaination suffice

 

[Tom van Stiphout]

On Thu, 25 Jun 2009 02:12:02 -0700, zyus

That's what's called a crosstab query, and there is a wizard for it
under Create Query.

In your output sample you have years rather than dates. If that's what
you want: Before you run it, create another query, base it on your
sampledata query, and enter the expression:
Year(Approvedate)

-Tom.
Microsoft Access MVP

 

[KARL DEWEY]

Substitute your table name and try this --
TRANSFORM Count([Change Requests].[Defaultdate]) AS [CountOfDate close]
SELECT Format([Approvedate],"yyyy") AS [Apr date]
FROM [Change Requests]
GROUP BY Format([Approvedate],"yyyy")
PIVOT Format([Defaultdate],"yyyy");