Hello, I wan't sure which forum to post in, so thought I would post here.

I have Access and Excel installed on my system.

I am a bus driver by trade. Most work places that have shifts have a handful of shifts and each shift is the same for the scores of employees on it.

Where I am however, we have (due to the nature of the work) over 300 individual shifts.

We also have a rota, each rota line lists rest days and duty numbers, for example:

Code:
Line | Sun | Mon | Tue | Wed | Thu | Fri | Sat
0001 | R D | 001 | 001 | 001 | 001 | 001 | R D
0002 | 001 | R D | R D | 002 | 002 | 002 | 001
0003 | 002 | 002 | 002 | R D | R D | 003 | 003
0004 | 003 | 003 | 003 | 003 | 003 | R D | R D
etc...
We are assigned a rota line, and then proceed to move down 1 line each week.

The duties themselves consist of quite a bit of data, for example all the fare stages and timing points. However, the only information that I require to put into this table is the start and finish times and locations.

For example, duty 001 starts at Depot at 0700, breaks for lunch at Station at 1200, reconvenes at 1300 and travels back to Depot and finishes at 1800. Some duties may have a 2 or more breaks.

An example table might look like

Code:
     |         Period A          |         Period B          |         Period C          |
Duty |    Start    |     End     |    Start    |     End     |    Start    |     End     |
     | Loc. | Time | Loc. | Time | Loc. | Time | Loc. | Time | Loc. | Time | Loc. | Time |
------------------------------------------------------------------------------------------
 001 | Dep. | 0700 | Stat | 1200 | Stat | 1300 | Dep. | 1800 |      |      |      |      |
 002 | Dep. | 0600 | Stat | 0930 | Stat | 1000 | Stat | 1300 | Stat | 1330 | Dep. | 1700 |
I can get both these tables input without issue, either as spreadsheets in Excel or as tables in Access.

What I can't work out is how to design the form or report so that it reads the data in the rota table, and displays the data from the duty table.

For example, using the above information - I would like to see the following

Code:
Line |        Sunday       |        Monday       |       Tuesday       |      Wednesday      |      Thursday       |        Friday       |       Saturday
----------------------------------------------------------------------------------------------------------------------------------------------------------------
     |                     | Duty 001            | Duty 001            | Duty 001            | Duty 001            | Duty 001            |                      
     |       Rest Day      | Dep. 0700-1200 Stat | Dep. 0700-1200 Stat | Dep. 0700-1200 Stat | Dep. 0700-1200 Stat | Dep. 0700-1200 Stat |       Rest Day
 001 |                     | Stat 1300-1800 Dep. | Stat 1300-1800 Dep. | Stat 1300-1800 Dep. | Stat 1300-1800 Dep. | Stat 1300-1800 Dep. |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
     | Duty 001            |                     |                     | Duty 002            | Duty 002            | Duty 002            | Duty 001            
     | Dep. 0700-1200 Stat |       Rest Day      |       Rest Day      | Dep. 0600-0930 Stat | Dep. 0600-0930 Stat | Dep. 0600-0930 Stat | Dep. 0700-1200 Stat 
 002 | Stat 1300-1800 Dep. |                     |                     | Stat 1000-1300 Stat | Stat 1000-1300 Stat | Stat 1000-1300 Stat | Stat 1300-1800 Dep. 
     |                     |                     |                     | Stat 1330-1700 Dep. | Stat 1330-1700 Dep. | Stat 1330-1700 Dep. |


What I am intending, in summary, is a 2 table database.

Table 1 contains the rota - a list of weeks with a unique id number for each line and for each line to contain a mixture of 5 duty numbers and 2 rest days.

Table 2 contains the data for each duty, namely the start and finish times and locations for each part of that duty

And then some form of report that combines the data from both tables.

Suggestions?