Count of scheduled events (need help!!) 1

[ad2]

Hi, thanks up front!

I have to create a report out of a db I didn't design. It is an event scheduling db. Events can happen once or be reoccurring. Here are the relevant fields in the table:

Occures – weekly
StartDate – 8/24/06
EndDate – 12/28/06
OccuresMonday – 1
OccuresTuesday – 0
OccuresWednesday – 1
(fields for each day of the week and are checkboxes)

So I need to count how may weeks there are between the start and end date and how many time each week the event occurs to get a total of how many times the event was scheduled. How would I write a query to do this?

 

[lespaul]

this may return the number of weeks between the start and end date:
DateDiff("w", StartDate, EndDate)

To get the number of times the event is scheduled, I would first normalize (see Fundamentals document link below for more on normalization) this table design with a union query:

SELECT Occures, StartDate, EndDate, "Mondays" As OccursWhen From TableName WHERE OccuresMonday
UNION
SELECT Occures, StartDate, EndDate, "Tuesdays" As OccursWhen From TableName WHERE OccuresTuesday


Once you have that then you can easily count the OccursWhen:

Select A.StartDate, A.EndDate, A.OccursWhen, COunt(A.*) FROM
(SELECT Occures, StartDate, EndDate, "Mondays" As OccursWhen From TableName WHERE OccuresMonday
UNION
SELECT Occures, StartDate, EndDate, "Tuesdays" As OccursWhen From TableName WHERE OccuresTuesday) As A
Group By A.StartDate, A.EndDate, A.OccursWhen




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[ad2]

Gosh, thanks!!!!