Display & Populate in 2D matrix

[ms_access_noobie]

Morning all,

I’ve been asked to consolidate data from a number of different sources, rationalise and set up some access controls to restrict viewing/editing. The raw data is combination of personal data, cost codes and dates.
My company supply labour and materials to offshore facilities, where our staff work on a rotational basis. These swings typically run 2 weeks on/2 weeks off, but often can run shorter periods. We need to track who is offshore and at which facility, both to prevent double bookings and identify when we can book people in for training etc. This data also needs to form the basis for our timekeeping application.
I’ve created the base tables successfully, rationalising where possible, however I cannot for the life of me figure out how to replicate the current process of assigning people to their rotation.

The travel team currently use a matrix where the user info is broken down by trade and name in the left hand column, then the dates are displayed in a row across the top. The process of allocation is then simply colour code the dates where the person is booked on or off. Apologies this isn’t very pretty, but will give you an idea;

__________________|1 Feb|2 Feb|3 Feb|
Electrician__________|____|____|_____|
___Fred___________|BLUE|BLUE|_____|
___Tom___________|____|____|BLUE_|
Mechanic__________|____|____|_____|
___Dave___________|____|BLUE|BLUE_|

Is this possible in Access at all, or if not, is there a means of at least displaying the data like this?
:banghead::banghead:

 

[Cronk]

A cross tab query can give you True / False values for on/off in your format. Then a report with conditional formatting will give Blue/White text boxes.

 

[burrina]

Your going to need a lot more than a cross tab query to get what you described.
Would need a lot more information to advise further.

 

[ms_access_noobie]

I've attached a copy with some basic data populated.

The mobilisation and demobilisation dates are contained in the 'people' table. Currently the travel team allocate these dates from a calendar type matrix in Excel - names horizontally and each day of the year listed vertically as shown in the example jpg.
If the dates cannot be populated like this, we can possibly work around it, but the data definitely needs to be able to be displayed this way to allow a simple visual display of who is on or off rotation