Employee Schedule

[tstutting]

[IMG]Hello, This is my first post and I'd really appreciate any help I can get. Sorry this is so long but it's complicated. I'm a USAF Officer and I'm attempting to make a shared MS Access DB that contains Training, Certification and Scheduling of Crew Members. I've got most of the back-end done (Tables with Relationships) and I'm happy with the Training and Certification sections. I'm having trouble with the Scheduling Part.

The Table is simple enough and I'd be done if I wanted to schedule Crews one day at a time, but I want to schedule a whole month from one Data Entry Form for one crew; the scheduling part will have to be abandoned otherwise.

I expect to need to use VBA and probably SQL to accomplish this task. I have a form now that looks and acts the way I need it to but with one Major Glitch... I can't enter or update the information because I'm using OUTER JOINS to query the Schedule Table and return the results in the way I want. I've uploaded screen shots of the Form and Table but I don't understand how to insert thier links.

[IMG]schedule_form.j pg[/IMG]
[IMG]Schedule_Table. JPG[/IMG]

Basicly the form is an unbound Outer Form with Crew (Alapha, Bravo...) and Date (January 06, Febuary 06...) information and Bound SubForms where thier Recordsource will be set using VBA code. Imagin a spreadsheet with employee names in Column A and Columns B-B+30 are the days of the month. Column A is a Subform showing all employees for the month working in Crew X (no duplicates) and Subforms B-XX are the Days of the month showing a date and Checkbox indicating the member in SF A is scheduled to work.

So now that you have the set-up, here's how I want to use it. I select the Crew and Month to schedule, I then choose a Crew Member from a combo box, and then start checking off what days that month he's going to work. Every day that I check ends up making an entry in the Schedule table with the members id, Date, Crew, and Worked...etc fields. Logiclly it sounds simple, in practice I am stumped.

Again, That was a really long post, I'm sorry and am prepared to accept the hate and angry comments if I've broken any rules of engagement.

Thanks
-Trevor

[MMcCarthy]

The only thing I can suggest is that you keep this form as is but create another form to add new scheduling events without the outer joins in the record source.

[tstutting]

Thanks mmccarthy that may be just what I'll have to settle for... I hate settling :). Anyway, I thought I'd mention another way to discribe my objective. I'm basiclly attempting to create a form that looks like a Pivot Table but allows me to modify or create records. If anyone knows how I might accomplish this, please point me in the right direction.

-Trevor

[comteck]

I'm sorry.. but I don't see anywhere where it's mentioned exactly what your problem is.

As well, could you provide a little more detail. What relationships are set up between the tables? What fields are included in the tables? What are the PKs, and/or FKs?

Thanks.
comteck