Class Days Per Month (1 Viewer)

smithyonline · May 21, 2017

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 · May 21, 2017

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 · May 21, 2017

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 · May 21, 2017

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 · May 21, 2017

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 · May 21, 2017

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?

smithyonline said:
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 · May 21, 2017

Gasman said:
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 · May 21, 2017

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:

Class Days Per Month (1 Viewer)

smithyonline

New member

Gasman

Enthusiastic Amateur

plog

Banishment Pending

smithyonline

New member

smithyonline

New member

Gasman

Enthusiastic Amateur

smithyonline

New member

isladogs

MVP / VIP

Attachments

Similar threads

Users who are viewing this thread