database design help

[Logsheet the pencil pusher]

We oversee fifty six subgroups and generate a report on each group using data
we receive on a monthly and quarterly basis. Each report has 23 categories,
two per staffer, and the reports are provided to management on demand.

Currently we are using one excel workbook per group, and the data is a
mixture of text and numbers. File sharing hasn't worked well for our senior
staffers who work from home. My thought was, use twenty three entry sheets
and link them to an access database that we can generate reports with. Our
current report format uses six columns and twenty three rows.

I've trying to figure out how to make the database flexible so if I query
a report on SG1, it gives me back comments for all 23 categories in
descending order and the date of entry.

Any table or query format suggestions would really be appreciated, as none
of my table formats or query logic is working.

 

[Fred]

I noticed that nobody answered. One challenge is that you are basically
asking a broad question - how to develop and entire application. A
respondent would need to ask a lot more questions and then write instructiosn
for your next 30 hours of work.

Step one is to clearly define (at leas to yourself, if not the forum) the
nature and organizaiton of the data that you want to record, and also what
you want to accomplish. Due to loose use of terms / and or not defining
them, ("group vs. sub-group", "Staffer") your posts got a start on this but
accomplish this.

Step two would be to define a table structure which supports accomplishment
of what you defined in step one.


Taking a few guieeses ( the group = subgroup, that the question really
relate to individual staffers, and then there are two staffers in a group,
then y first guess is that a good set of main tables would be:

Table that is a list of Groups/SubGroups, PK = GroupIDNumber,
Table that is a list of staffers PK = StafferIDNumber, FK & link is
GroupIDNUmber
Table that is a list of reports FK & Link is StafferIDNumber. Each record
would have the 23 fields, plus a field identifying it as Monthly vs
quarterly, a field with the date and or period that it is for.



Then everything else will build on that.