calendar

[zuglchaim]

i have an "event" table.
in this table there 2 fields
field "date"- calendar for year 2003
field "event"- different events which will take plase on this date

i whould like to make in report a regulore calendar with weekdays "sunday", "monday" ,its
and in the "date" box there will be showen an "event" just like we do with a pen on the calendar)
don't forget that sometimes month starets in the middles of the week so a page should display first part of the week with empty boxes
i thought that creating a crosstab query, where colum header will be weekday([date])=vbsunday, its will help, but relised it does not display actual date but rathere counting dates
please help, is there posebility do do sothing lke this without writing 200 line of code?

 

[allanon]

Well. This one was not easy. I did this with a couple of tables and some queries.

Here is what I did.

1) create a test message table. I created exactly 2 months of data for testing.

Key TheDate Message
1 01/01/2003 Message 1
2 02/01/2003 Message 2
3 03/01/2003 Message 3
4 04/01/2003 Message 4
5 05/01/2003 Message 5
6 06/01/2003 Message 6
7 07/01/2003 Message 7
8 08/01/2003 Message 8
9 09/01/2003 Message 9
10 10/01/2003 Message 10
11 11/01/2003 Message 11
12 12/01/2003 Message 12
13 13/01/2003 Message 13
14 14/01/2003 Message 14
15 15/01/2003 Message 15
16 16/01/2003 Message 16
17 17/01/2003 Message 17
18 18/01/2003 Message 18
19 19/01/2003 Message 19
20 20/01/2003 Message 20
21 21/01/2003 Message 21
22 22/01/2003 Message 22
23 23/01/2003 Message 23
24 24/01/2003 Message 24
25 25/01/2003 Message 25
26 26/01/2003 Message 26
27 27/01/2003 Message 27
28 28/01/2003 Message 28
29 29/01/2003 Message 29
30 30/01/2003 Message 30
31 31/01/2003 Message 31
34 01/02/2003 Message 1
35 02/02/2003 Message 2
36 03/02/2003 Message 3
37 04/02/2003 Message 4
38 05/02/2003 Message 5
39 06/02/2003 Message 6
40 07/02/2003 Message 7
41 08/02/2003 Message 8
42 09/02/2003 Message 9
43 10/02/2003 Message 10
44 11/02/2003 Message 11
45 12/02/2003 Message 12
46 13/02/2003 Message 13
47 14/02/2003 Message 14
48 15/02/2003 Message 15
49 16/02/2003 Message 16
50 17/02/2003 Message 17
51 18/02/2003 Message 18
52 19/02/2003 Message 19
53 20/02/2003 Message 20
54 21/02/2003 Message 21
55 22/02/2003 Message 22
56 23/02/2003 Message 23
57 24/02/2003 Message 24
58 25/02/2003 Message 25
59 26/02/2003 Message 26
60 27/02/2003 Message 27
61 28/02/2003 Message 28

2) create a query to divide the data into month and week of month.

The query:

SELECT Table3.TheDate, Table3.Message, DatePart("w",Table3.TheDate) AS DayOfWeek, DatePart("ww",Table3.TheDate) - datepart("ww", CDate("01/" & DatePart("m",Table3.TheDate) & "/" & DatePart("yyyy",Table3.TheDate))) + 1 AS WeekOfMonth
FROM Table3;

the results:

TheDate Message DayOfWeek WeekOfMonth
01/01/2003 Message 1 4 1
02/01/2003 Message 2 5 1
03/01/2003 Message 3 6 1
04/01/2003 Message 4 7 1
05/01/2003 Message 5 1 2
06/01/2003 Message 6 2 2
07/01/2003 Message 7 3 2
08/01/2003 Message 8 4 2
09/01/2003 Message 9 5 2
10/01/2003 Message 10 6 2
11/01/2003 Message 11 7 2
12/01/2003 Message 12 1 3
13/01/2003 Message 13 2 3
14/01/2003 Message 14 3 3
15/01/2003 Message 15 4 3
16/01/2003 Message 16 5 3
17/01/2003 Message 17 6 3
18/01/2003 Message 18 7 3
19/01/2003 Message 19 1 4
20/01/2003 Message 20 2 4
21/01/2003 Message 21 3 4
22/01/2003 Message 22 4 4
23/01/2003 Message 23 5 4
24/01/2003 Message 24 6 4
25/01/2003 Message 25 7 4
26/01/2003 Message 26 1 5
27/01/2003 Message 27 2 5
28/01/2003 Message 28 3 5
29/01/2003 Message 29 4 5
30/01/2003 Message 30 5 5
31/01/2003 Message 31 6 5
01/02/2003 Message 1 7 1
02/02/2003 Message 2 1 2
03/02/2003 Message 3 2 2
04/02/2003 Message 4 3 2
05/02/2003 Message 5 4 2
06/02/2003 Message 6 5 2
07/02/2003 Message 7 6 2
08/02/2003 Message 8 7 2
09/02/2003 Message 9 1 3
10/02/2003 Message 10 2 3
11/02/2003 Message 11 3 3
12/02/2003 Message 12 4 3
13/02/2003 Message 13 5 3
14/02/2003 Message 14 6 3
15/02/2003 Message 15 7 3
16/02/2003 Message 16 1 4
17/02/2003 Message 17 2 4
18/02/2003 Message 18 3 4
19/02/2003 Message 19 4 4
20/02/2003 Message 20 5 4
21/02/2003 Message 21 6 4
22/02/2003 Message 22 7 4
23/02/2003 Message 23 1 5
24/02/2003 Message 24 2 5
25/02/2003 Message 25 3 5
26/02/2003 Message 26 4 5
27/02/2003 Message 27 5 5
28/02/2003 Message 28 6 5

3) create a result set table with two months of space. one record for each week in a month at 5 records per month.

4) created 7 update queries that can be run in a macro.

these are the queries:

UPDATE Query6 INNER JOIN Table4 ON (datepart("m",query6.TheDate) = Table4.month) AND (Query6.WeekOfMonth = Table4.week) SET table4.message1 = Query6.Message
WHERE query6.dayofweek = 1;

UPDATE Query6 INNER JOIN Table4 ON (Query6.WeekOfMonth = Table4.week) AND (datepart("m",query6.TheDate) = Table4.month) SET table4.message2 = Query6.Message
WHERE query6.dayofweek = 2;

UPDATE Query6 INNER JOIN Table4 ON (Query6.WeekOfMonth = Table4.week) AND (datepart("m",query6.TheDate) = Table4.month) SET table4.message3 = Query6.Message
WHERE query6.dayofweek = 3;

UPDATE Query6 INNER JOIN Table4 ON (Query6.WeekOfMonth = Table4.week) AND (datepart("m",query6.TheDate) = Table4.month) SET table4.message4 = Query6.Message
WHERE query6.dayofweek = 4;

UPDATE Query6 INNER JOIN Table4 ON (Query6.WeekOfMonth = Table4.week) AND (datepart("m",query6.TheDate) = Table4.month) SET table4.message5 = Query6.Message
WHERE query6.dayofweek = 5;

UPDATE Query6 INNER JOIN Table4 ON (Query6.WeekOfMonth = Table4.week) AND (datepart("m",query6.TheDate) = Table4.month) SET table4.message6 = Query6.Message
WHERE query6.dayofweek = 6;

UPDATE Query6 INNER JOIN Table4 ON (Query6.WeekOfMonth = Table4.week) AND (datepart("m",query6.TheDate) = Table4.month) SET table4.message7 = Query6.Message
WHERE query6.dayofweek = 7;

Running these queries gives you this result:

key month week message1 message2 message3 message4 message5 message6 message7
47 1 1 Message 1 Message 2 Message 3 Message 4
48 1 2 Message 5 Message 6 Message 7 Message 8 Message 9 Message 10 Message 11
49 1 3 Message 12 Message 13 Message 14 Message 15 Message 16 Message 17 Message 18
50 1 4 Message 19 Message 20 Message 21 Message 22 Message 23 Message 24 Message 25
51 1 5 Message 26 Message 27 Message 28 Message 29 Message 30 Message 31
52 2 1 Message 1
53 2 2 Message 2 Message 3 Message 4 Message 5 Message 6 Message 7 Message 8
54 2 3 Message 9 Message 10 Message 11 Message 12 Message 13 Message 14 Message 15
55 2 4 Message 16 Message 17 Message 18 Message 19 Message 20 Message 21 Message 22
56 2 5 Message 23 Message 24 Message 25 Message 26 Message 27 Message 28

This then is a table that is formatted to allow you to print it out in a calander form.

Have fun.

 

[PWise]

The way I read this the first day of the day of the month will always be in the first column.
See my sample code in thread701-438803 I wrote from memory it might need some debugging and error checking

 

[zuglchaim]

i think in stad of message1 it has to writen sunday and so on
but what i can't understend with allanon answer is that the first query gives result in "weekofmonth" column from 1 to 9


DatePart("ww",Table3.TheDate) - datepart("ww", CDate("01/" & DatePart("m",Table3.TheDate) & "/" & DatePart("yyyy",Table3.TheDate))) + 1 AS WeekOfMonth

i gess there is something missing or i missunderstud something, but everything else works

 

[zuglchaim]

never mind
i am using USA date format: month/date/year
i fixed it in the table but forgot to fix it in that query