I think perhaps a little bit of background is appropriate.
The original program was an Excel Spreadsheet containing the roster. This has, say, twenty-four tabs with twenty-four weeks worth of rosters in. I have (I think) attached a guideline layout).
This has to then be converted into another spreadsheet, in a slightly different format, to be submitted to our Paybills department as a Time Sheet.
As part of our rostering guidelines, we have to make sure we don't fall foul of certain criteria - minimum rest, maximum shift length, maximum hours per week, without authorisation.
I have written a VBA module which, for each member of staff, finds their pay number in the roster, then copies their shifts across to the time sheet for submission to paybills.
It then struck me that it would be much better if rather than using a list of pay numbers in Excel, I used a table in Access, which I could also use as the centre of a staff database.
I then started working on the current aspect, which is to pull out certain information (when the aforementioned criteria are breached) and write them to an Access table, so that the management could run reports and find out information about the number of breaches per month etc.
So, in a nutshell the information is all coming from Excel, but being written to Access.
There are good reasons for keeping the roster in Excel (makes it easier to navigate) but using VBA in Excel to manipulate Access does cause some headaches in itself.
Hope this makes sense.
Chris