The right structure to get the right report

[fat controller]

BACK STORY - please go to post 2 if you don't want to know

After approximately 10 years in the same role, being in charge of the same department, the time came for me to move on. Initially, I was looking at going back to the 'shop floor' which would have removed me completely from any administration or management, and in fact for the past five weeks that is exactly what I have been doing, and enjoyed it very much (no emails at silly o'clock, no emergency call outs, no taking work home).

However, I was asked instead if I would consider a secondment to a managerial role in an entirely different department, with a view to that secondment possibly becoming permanent depending on how I felt and how I 'fitted in'. This particular department is a complete and utter departure for me in as much as it is all back-room work, vast amounts of data in various forms, and much of it with legal implications attached to its accuracy. My prior role was managing the day to day 'nut and bolts' of the customer facing operation.

It transpires that a number of the staff in this department had actually asked specifically for me when they heard that I had become available, which is very flattering indeed, and part of the reason is that they had heard about some of the processes I had implemented elsewhere, including a whole site Access database, which continues to be successful and popular with its users.

 

[fat controller]

Today, it became abundantly clear that there is a need in my new role for a database of some form or other - - there is a degree of disarray in some areas that I can see where a database would help. However, as I am at the start, I am aware that getting the right structure for tables/queries etc now will determine the reports I get at the other end - - however, I know what I want to get out of the report, so it is a case of working backwards from there (I think).

One of the reports is the production of a specific document known as an "RRR" (Access report), that is standard in terms of layout, but the content there-in can vary massively.

To try and give an idea what I am trying to achieve, we have:

- A number of runs where the exact routing used (what roads etc) has to be declared and abided by; each run is identified by its contractual ID

- Each of those runs can have a number of contingency routings, or short terminations, each of which has 9 different pieces of information (all free text/memo)

- Some runs can have only two or three of these contingency/short terminations, others can have ten or more.

Is there a way that I can have a standard report in Access that will include only the number of contingency/terminations that have been populated - as in, those with three are not then followed by 12 blank spaces on the final report? Also, these contingency/short runs can change, but we need to have each document preserved as it was at the time it was valid.

How best to arrange tables/queries to achieve this?

I am thinking something along the lines of:

RouteContractID table

Contingency/Short Runs Table (include start and end dates for when each one was valid)

RRRTable for the other bits of info

Any and all suggestions and advice gratefully received