Create a calendar of course meeting weekly and biweekly

[Awat]

I have a course meeting table that shows only the starting and ending dates of the classes and meeting days of week (meet once, weekly or biweekly on M W T...)

The days of week data is quite tricky. The days are determined by the position of the meeting code. Below is how the data look like.

W = Weekly
B = Biweekly
O = Once
Position 1 = Monday, Position 2 = Tuesday, and so on.

Course# StartDate EndDate DaysofWeek
50050 03/01/2010 05/25/2010 WW <- Mon, Tu, weekly
60100 03/03/2010 05/26/2010 B <- Wed, biweekly
41100 03/02/2010 05/27/2010 W W <- Tu, Th, weekly
30900 03/06/2010 03/06/2010 O <- Sat, Once

How can I get all dates of each course from the starting to ending dates? I have to create a calendar-like report that will list every course in each date for the whole semester. Please advise.
Thanks!
Asuth

 

[lbass]

When you reference the position, do you mean the codes are in separate columns, e.g., Mon, Tue, Wed, as separate columns? Or is this really one field that is a constant length with spaces for missing characters? How many courses might there be? What is the final output supposed to look like?

-LB

 

[Awat]

Hi Ibass,

The codes are in one field that is a constant length (=7) with spaces for missing characters. The position of the codes indicates the day of the week.

Course[tab][tab]DayOfWeek[tab][tab]Room

40030[tab]

http://WWWWWW

[tab][tab]A1001[tab][tab][tab](weekly, Mon-Fri)
60070[tab] W W W[tab][tab][tab][tab]A2002[tab][tab][tab](weekly, Mon, Wed, Fri)
30090[tab] B B [tab][tab][tab][tab][tab]A3003[tab][tab][tab](bi-weekly, Tu, Thur)
80050[tab]O[tab][tab][tab][tab]O[tab][tab]B4004[tab][tab][tab](Once, Sat, Sun)



Here is how the report should look like.

[tab][tab][tab][tab][tab][tab][tab][tab]Building A[tab][tab][tab][tab][tab][tab][tab]Building B ..
[tab][tab][tab][tab][tab][tab][tab]Time[tab]Room[tab]Course[tab]Time[tab] Room[tab]Course

Monday March 1 |5pm-8pm A1001 40030|
[tab][tab][tab][tab][tab][tab][tab]|5pm-7pm A2002 60070|

Tuesday March 2 |5pm-8pm A1001 40030|
[tab][tab][tab][tab][tab][tab][tab]|4pm-8pm A3003 30090|
.
.

Sat March 6[tab][tab][tab][tab][tab][tab][tab][tab][tab][tab][tab][tab]|8am-9am B4004 80050|

I am trying to make the courses repeat weekly and bi-weekly on the report. Since I only have the starting and ending dates of each course, not every occurrence dates, I cannot figure out how to get all actual dates that the courses take place.

Actually, it doesn't matter how the report will look like.

I tried to put it in a calendar using the starting dates (following your instruction on ml-consult website) and group the courses by building within each day.

It works fine (doesn't look good because there could be 10-100 courses each day) except that the courses will not be repeated weekly and bi-weekly on the calendar. Please advise.

Thank you!
Asuth

 

[lbass]

This looks like an interesting challenge, but unfortunately I don't have time to work on it. I think you would be best off if you could create a table of dates using the code and start and end dates, and only then tackle getting it into a calendar format. You would probably need to use arrays to create the list of dates, and that isn't one of my strengths really.

-LB

 

[Awat]

lbass,
Thank you for the idea. If an expert like you are saying that this is a challenge, maybe it's not a good idea to do this calendar in Crystal.

I will try the method you suggested above and see how it goes (if I can ever make it happen).

Thanks!
Asuth

 

[Awat]

I just realized that we have every occurrence date stored in a table. *_* So, the question now is how can I put the data in a nice calendar style with the dates in the left (like the sample above.)
Thank you.
Asuth

 

[lbass]

How many buildings are there?

-LB

 

[Awat]

Ibass,
Thanks for your help. I already finished the report using your idea of creating a date table in excel. I then link the calendar date to the meeting date in each building sub-report and order the building sub-reports horizontally.

 

[lbass]

Glad you got it working.

-LB