Scheduler database / crosstab form

[vandaliarental]

I am looking to create a database to maintain our employees schedule. I've got a table created with employee number, date, time in & time out, and have created a crosstab query, combining the time in & time out so it shows as the value.

Table:
Emp# Date TimeIn TimeOut
1 12/6/04 7:00 17:00
2 12/6/04 7:00 17:30
1 12/7/04 6:45 16:45
2 12/7/04 7:15 17:30

Crosstab results:
Emp# 12/6/04 12/7/04
1 7:00 - 17:00 6:45 - 16:45
2 7:00 - 17:30 7:15 - 17:30

I would like to be able to edit the schedules from the crosstab results. I've tried putting this into a form, but the dates are restricted across the top (I need to be able to scroll out to see future dates). Also, we need to be able to edit the times in this form.

Any ideas?

Thanks,
Connie
Vandalia Rental

 

[MichaelRed]

CrossTab results sets are aggregate function (even if no aggregation is actually involved) and, as such cannot be edited. To overcome this, to a limited extent, you can 'convert' the CrossTab Results set to table data (MakeTable, Append or Update) the current results to a table type recordset. That, however, doesn't facillitate returning any changes you might make to the original tables from whence it originated. Nor does it provide a
"projection" to future dates. Again, partial relief is available, using a seperate recordset with the requsite dates in a left join, and using this date (column) as the piviot column of the CrossTab. This, however, requires the generation / selection of the dates to include.

The 'exercise' 'sounds like' a cross between two rather typical (read mundane) schedualing exercises; Calculate hours worked from time card information and generate a schedual of irregular emploees based on a sign up sheet (including providing space for additional data entry). Either of these exercises can be found within the 'archives' of these (Tek-Tips) fora with the dilligent use of the "Search" feature. Combining them might be an interesting exercise for the class.





MichaelRed

 

[vandaliarental]

Michael:

Thanks for your reply, although you don't give me much hope for accomplishing what I'd like to do.

I already have a database that reads our unix data & prints reports. We also have a scheduler program that no one really likes; it's not really user friendly. The boss basically wants an assortment of reports between the two that will compare data (ie - who's late, etc).

So I didn't think it would be too much to do to just do it all in Access.

Here's what I've done so far...

I created a table filled with just dates (DatesTbl-with the Date field being the primary key; I'll auto generate it with a function in vb). I also have the table above (ScheduleTbl) showing the schedule.

Next, I created a form based on the dates table and added a subform to list the employees and times scheduled to work on that date. This gets all the days we're open, even if no one is scheduled yet. When I try to add an employee to the schedule, I get an error "Can't add record(s); join key of table 'ScheduleTbl' not in recordset".

The form & subform are linked on the 'Date' field from each table, but I don't have a relationship defined between the two.

I know this has something to do with the referential integrity, but if I don't have it defined, I'm not sure why I'm getting this error.

Thanks,
Connie

 

[MichaelRed]

it isn[t referential integrity. It is the failure of Ms. A. to be able to 'seperate' the aggregation of the crosstab.

If the form is solid, you can get the requsite info to 'build' a record and add it to the table(s) with a seperate "append" query. Likewise, if the necessary fields/info are available, you can build an Update to the recordset (again seperatly) and change the source data via an Update query. In either (both) instances, you need to then refresh/requery the form(s).

To, perhaps, improve your chances, search these (Tek-Tips) fora using "schedual" (and other terms). There are some reasonable soloutions for the 'light weight' versions here. Aggregate the best of several might be sufficient.





MichaelRed