Group by date

J

Jens Hofmeier

Hello,

i have got the following table:

event appointment1 appointment2
event1 01.01.2005 12.01.2005
event2 02.02.2005
....

I want to create a report that shows the number of appointments per Month:
event1 event2 ....
Jan 05 2 0
Feb 05 0 1

The problem is, that i am unable to create a query that delivers the
required result. Can someone help?

thanks in advance,

Jens
 
G

Graham Mandeno

Hi Jens

You can convert the date/time of the appointment to the first of the month
like this:

ApptMonth: DateSerial( Year(ApptTime), Month(ApptTime), 1 )

Then you can sort and group on that field, and use Format to display the
month however you wish.
 
K

KARL DEWEY

You should change your table structure to look like --
Appointments
Event - Number - long interger
Appoint - DateTime
X - Number - Default 1

Use this crosstab query --
TRANSFORM Count(EventSched.X) AS CountOfX
SELECT Format(DatePart("m",[Appointment]),"mmm") & " " &
Format(DatePart("y",[Appointment]),"yy") AS Schedule
FROM EventSched
GROUP BY Format(DatePart("m",[Appointment]),"mmm") & " " &
Format(DatePart("y",[Appointment]),"yy")
PIVOT EventSched.Event;


Graham Mandeno said:
Hi Jens

You can convert the date/time of the appointment to the first of the month
like this:

ApptMonth: DateSerial( Year(ApptTime), Month(ApptTime), 1 )

Then you can sort and group on that field, and use Format to display the
month however you wish.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jens Hofmeier said:
Hello,

i have got the following table:

event appointment1 appointment2
event1 01.01.2005 12.01.2005
event2 02.02.2005
...

I want to create a report that shows the number of appointments per Month:
event1 event2 ....
Jan 05 2 0
Feb 05 0 1

The problem is, that i am unable to create a query that delivers the
required result. Can someone help?

thanks in advance,

Jens
Click to expand...
Click to expand...
 
Top