advice please - design (1 Viewer)

[rossdagley]

Hi guys

Long time lurker, first time poster. Would really appreciate your help.

I've got a requirement to have a form (and a report) that the input area and output look like the following excel screenshot. I can probably figure out the report for the output, but making the form work how I want for inputting the data is driving me nuts.

In simple terms, I've a table with the names of the staff in (Tess, Kim etc), and a table with the regions in (london, gist, midlands etc).

The spreadsheet shows which member of staff is responsible for which regions for the coming two weeks.

One staff member might be responsible for more than one region, or alternatively might be on annual leave (AL). I want the form to allow me to chose a date period (ideally at the top of the form) and to populate the dates automatically, leaving me to just choose the staff member and region in the matrix.

Could someone give me some help with how best to go about this?

Thanks - and hopefully I've been clear enough, but of course, if I havent, then please let me know.

Excuse the format of the link to my image, but as I've less than 10 posts I cannot just link to it

Remove the spaces from the link below and it should work

Cheers!

Ross

img257 . imageshack . us/img257/2751/coverxh.jpg

 

[jzwp22]

Welcome to the forum!

Frankly, I'm not sure how you would go about creating the form in the format you want, but I think you need a couple of additional tables in your table structure.

You need a table to relate the staff with the regions to which they are responsible

tblStaffRegions
-pkStaffRegionID primary key, autonumber
-fkStaffID foreign key to tblStaff
-fkRegionID foreign key to tblRegion
-dteEffective (date field)

You will probably also need a table that defines holidays

tblHolidays
-pkHolidayID primary key, autonumber
-dteHoliday (date field)
-txtHolidayDesc

To get the regions to which a person is assigned, you will probably need a custom function that loops through the tblStaffRegion for each staff member/date

In general terms, I think you will probably need a cross-tab query in order to create the form, but I'm just not real sure from there.