Rolling views of cells?

[Bob Seymour]

Please help!!!

I have a database for weekly 'Traffic Light' reporting on a project. Each
manager (there are 20+) on the project has their own table and a linked form
for data input.

The table headings (this is just a part) look like this:

ID - Manager - Area - Reporting_Period - Overall - Schedule - Finance -
Resource - Technical - IT
(The 'Reporting_Period' is a week ending date and the O, S, F, R, T & I
cells are input with R, Y or G as appropriate and conditional formatting on
the form changes the cell colours to suit - so that it looks pretty!)

What I need is for the form to look back at the 'traffic lights' from the
previous 5 weeks on the underlying table and show them on the form so that
any trend is immediately visible. I'd like it to do this without resorting
to a myriad of queries if possible.

Thanks in advance.
Bob S.

 

[BruceM]

What you have described is more suitable to a spreadsheet than to a
relational database. If the managers are inputting the same type of data,
they should be using a single table.
Each manager (or employee, for that matter, if other employees are included
in any of the data) needs to be a separate entry in an Employees table.
Employee information should be the only data in that table. Without knowing
just what you need to accomplish I can only guess about what other tables
should be included, but you may need an Area table, and a Report table (the
weekly report would be a record in this table). If the rest of the fields
you listed are summary information about Overall, Schedule, etc. then it may
work to include that information in the Report table, but if for instance
there are numbers or calculations involved in obtaining the Overall
information that will need to be included in a table as well.
A widely-recommended book about relational database design is Database
Design for Mere Mortals. The author is named Hernandez, as I recall; in any
case I am pretty sure of the title. I think you would do well to research
database design before getting yourself too deeply into a project that could
quickly become very tangled.

 

[Guest]

Bob

As Bruce says, you need to get the table schema right first but if you want
to show your data with different colours for each value then a Flex Grid
control may be more flexible (and pretty). See my Flex Grid Demo program for
some typical examples at :-

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hibbs,Peter S

 

[Bob Seymour]

Thank Peter (and BruceM), I'll check out the book.
Bob S.