Schoolrosters, PivotTables or Access database? | AccountingWEB

Schoolrosters, PivotTables or Access database?

Schoolrosters, PivotTables or Access database?
How do I extract TEACHERS' WORKLOADS OVERVIEWS from their school-roster? Please see a screenshot here, you can get the actual sheet there as well.

Basically, my question is:
How can I produce the OUTPUT based on the INPUT?

So the process is like this:
1. I get a roster in table-form. In the "row-area" are the entities Subjects and Hours, in the "column-area" is the entity Classes, teachers' names get filled out into the "data-area". This school-roster looks like the table under INput.

2. I fill the roster with the names of the teachers (in red) that are going to teach certain classes. It's a "everybody teaches every subject" type of school. So, every teacher can teach multiple subjects to multiple classes

3. Then, I have to determine per teacher, what subjects they are gonna teach to which classes. This will result in an overview per teacher that looks like the stuff under OUTput. In short, their workload. Like the one under OUTput for teacher "Johnson".

I would like that it works like this: that as soon as I type a name into the roster (INput), that this will show up in their personal overviews (OUTput). So, when I fill out the INput-sheet with teachers names, I would like that each OUTput-sheet would then fill itself with the appropriate information, per teacher.

I imagine this to happen like this: every month, when the roster changes, I would copy paste the INput sheet into the workbook and then every and any teacher-worksheet would produce this scheme as indicated under output.

My solutions: I have thought about massive nested IF's but that would be limited, heavy and nested IF's seem to be constrained to a ceiling-number. So I abandoned that one.
I could perhaps use either an Access database (my version is 2003) or work with Pivot-Tables on spreadsheets, again Excel 2003. Should I do it with formulas, functions, macros, or .... ????

- Each teacher has his own sheet. The INput is on a separate sheet too.
- Individual students are of no importance at this point.
- I use Excel 2003 under WinXP.

PS: I asked this on other forums but no luck there.

ShirleyM's picture


ShirleyM | | Permalink

I think Access would be ideal for the results you need.

I am self-taught and am not an expert so please do your own research before committing yourself.

Once you have created a table to hold the data, a simple query will give you the results equivalent to a pivot-table in Excel, but is much easier to create and a very simple matter to add parameters for your teachers to enter the criteria needed.

If you research further you can create a form for the entry of the parameters, and then a report to format the end results.


zploek's picture


zploek | | Permalink

Hi ya,

thanks for replying, it's not that busy a site, it seems :)

I will try to follow your suggestions, if i manage I will post a database here, would you be willing to have a look at it, then? If i get stuck?



ShirleyM's picture

Have a play with Access

ShirleyM | | Permalink

Access is a wonderful piece of software, and like most things, there is usually more than one way of achieving the desired result.

The best way to learn is to just play around with it. There are many tutorials and access templates on the MS website, or get a book (or two or three) and teach yourself, as I did. There are also many Access forums on the internet where you can get tips and help from people with far more experience than myself.

I wouldn't really want to commit myself to anything definite. I know from experience that once you realise what Access is capable of, your interest in it can grow exponentially, and a simple project can turn into something much bigger.

However, I would be happy to help with any simple questions posted on AWeb (if I can!).

John Stokdyk's picture

Try Any Answers next time

John Stokdyk | | Permalink

Hi Z,

I'm glad Shirley has come to your aid. AccountingWEB actually is quite a busy site, with more than 1m page impressions a month from 225k accountant users.

However, you've posted this article in a relatively quiet discussion group devoted to the workings of the site itself and for members to put forward complaints or functional suggestions. One of the issues with the site is that it's so big and has so many different areas that it can be difficult for new members to find their way around. We're aware of this and are working on a simplified design that we hope will be up and running before too long.

If you visit the Any Answers page and repost your question (just copy and paste from the copy above before clicking this link and then paste it into the "Body" box), I'm sure you'll get a lot more suggestions, particularly if you select the "Excel" topic from the menu on the data-entry page.

Also have a look at some of our tutorial articles on Microsoft Access.

Richard Willis's picture

I realise this is a bit old, just found it!

Richard Willis | | Permalink

When I was teaching Access I always used a school or college roster as a simple model as it contains many of the basic elements required.  For instance it is easy to check what the effects of an absent teacher will be, i.e. which classes they teach, or which teachers to contact if a pupil is absent.

How did you get on?

Add comment
Log in or register to post comments
Group: AccountingWEB feedback group
AccountingWEB feedback