Rosters in Access

[stevekos07]

Hello all. I have been asked to develop a couple of rosters to replace ones we have been using in Excel. I am having trouble with this. I thought I could simply develop a query that brings a table with e.g. volunteers and another with a date list and generate a monthly list of dates per volunteer that I could use as a roster.

This doesn't seem to be as easy as I thought.

Can anyone suggest a fairly straight forward approach to this? I know that there are some very sophisticated ways of doing rosters but I am looking for the simplest approach I can find without extensive coding etc.

My roster simply needs to feature a list of people, a list of dates and a way of selecting or deselecting the dates for each person, along with a notes field for each selection. I can visualise it easily in a spreadsheet but in access it is a bit more of a challenge.

I was thinking of a cross join query but the resulting datasheet doesn't allow editing of the fields.

 

[stevekos07]

Ok here's what I'm thinking at present. I could have a form with a combobox which selects locations and a list of dates and other relevant details for each location, which are saved as separate subforms. There are about 25 locations.

I could also set up comboboxes against each date which select a volunteer for each location for each day.

The subform which loads is based on the combobox selection. The subform could filter the dates based on a query criteria or expression.

This seems to be a workable rostering solution. Any other thoughts?

 

[arnelgp]

here is a rough sample.

 

[stevekos07]

Can't seem to open it here at work arnelgp. Security! I'll have a look at it at home.

 

[stevekos07]

here is a rough sample.

Thanks once again arnelgp! That has everything I need. I admit I don't understand all of the code and that makes me nervous. If you have time would you mind sending a PM with an explanation of what each line of the VBA does?

If not that's ok, I will study it out. I just don't like copying code blindly without understanding what it does in case something goes wrong.

Cheers,

Steve.

 

[stevekos07]

Actually arnelgp the db does need something else.

What I need to be able to do is to show the roster dates for a full month per location. The main form should have two comboboxes. The first, a combobox to select the location, and another to select the month, (or the other way around), then the subform needs to show the dates for the month selected so that the roster can be completed for the full month for that location.

This is a bit more complicated I know. I hope I am making sense.

 

[stevekos07]

Actually there is more to your little db than meets the eye! I looked in the relationships pane and the tables you have there do not show in the object navigation pane. This might need a bit of explanation. I am a relatively low level developer and this is new to me.

 

[stevekos07]

Sorry for the multiple posts. I have worked out the system file thing. It was that I had hidden files checked on in the navigation pane options. Ok, so now it's just the other things.