Class Days Per Month

[smithyonline]

Hello thanks for reading

bit of a novice so hoping you can help

i trying to build a query to return how many time a class will run each month so i can then invoice accordingly

so if class 1 runs on Mondays and class 2 on Tuesdays can i get access to return the number of each per month?

 

[Gasman]

It would depend on how your data is structured, but I would have thought you would have at least

ClassName (or ClassID as a foreign key?) and date of class.
If so, then you can group on Year, Month from the date and ClassName or ClassID ?

 

[plog]

Gasman's method is correct, but I bet his assumption of your data structure is incorrect. I bet you don't have the specific date each class takes place, but instead simply have a record that says Class1 is on Mondays. Getting from there to where you want to be is going to be difficult.

Can you post some sample data? Provide 2 sets:

A. Starting sample data from your table(s), include table and field names and enough sample data to cover all cases.

B. Expected results based on A. Show what data you expect your query to return when you feed it the data in A.

 

[smithyonline]

yes i have ClassID, ClassName, ClassDay, ClassPrice

so trying to build a query to use ClassDay and a date (or a date range) to return number of times a class happens per month

 

[smithyonline]

the school had a excel spreadsheet that was very glitchy in that they had:

=SUMPRODUCT(--(ISNUMBER(MATCH(TEXT(ROW(INDIRECT(Schedule!$B$1&":"&Schedule!$C$1)),"dddd"),B7,0))))
with schedule related to a date range and B7 was the day of class but not sure how to do this in access

 

[Gasman]

You do not say where you are located, but this month we in the UK will have had two bank holidays this month, each on a monday, so they would have to be excluded?

yes i have ClassID, ClassName, ClassDay, ClassPrice

so trying to build a query to use ClassDay and a date (or a date range) to return number of times a class happens per month

 

[smithyonline]

You do not say where you are located, but this month we in the UK will have had two bank holidays this month, each on a monday, so they would have to be excluded?

sorry ye I'm From the UK currently the spreadsheet that is used has and adjustment cell to allow a -2 to exclude bank holidays so was intending to do the same one i can return the number of Mondays

 

[isladogs]

Also what about holidays: Easter, Xmas etc etc?

And just to further complicate things:
1. many schools have 2 week timetables so alternate week's lessons are different
2. some classes may have 2 lessons (or a double period) for same class on same day
3. non-timetable days / INSET days?
4. early closure on last day of term before Xmas / summer holiday

I'm not trying to be awkward but merely point out other things you MAY have to factor in ...

In a similar situation I have a school calendar table, where term & holiday dates are identified using boolean ValidAM/ValidPM fields: