Ptoject management design help needed

[stuartnz]

I am trying to setup a small DB to keep track of staff performance and quota
management in various projects I supervise. This link is to a screenshot from
another DB application taken before I got Access 2007, but it does show where
I'm at in terms of design:
http://maxqnzs.com/screenshots/layout1.jpg

The "kits" have two different sets of quota. One is the Age/Gender set,
which is always as shown in the screenshot. The other is for "Wards". The
number of wards varies from job to job. Would it be OK to add fields like
"Ward1Quota", "Ward2Quota", "Ward3Quota", etc. to the "Kits" table but leave
all except "Ward1Quota" set to "Entry Required=No"?
The age/gender fields are quotas, which is why I have put them as separate
fields. Each kit may have different quotas for age AND for gender. Each kit
also has quotas for the "Wards", but the number of wards may vary by job and
kit. Here's an example:

Kit one has a quota of 5 Males under forty, 4 females over forty, 5 females
under forty and 8 males over forty. It also has a quota of 6 from Ward One, 5
from Two, 4 from Three, 3 from Ward Four, 2 from Ward Five and 2 from Ward
Six.

Kit two has a quota of 3 males under forty, 9 females over forty, 5 males
over forty, and 5 females under forty. It also has a quota of 6 from Ward
One, 5 from Ward Two, 3 from Ward Three, 5 from Ward Four, and 3 from Ward
Five.

I hope those examples show why I haven't set up a single field each for
Ward, Age and Gender. If I have misunderstood how to set them up, I will be
grateful for correction.
The "ward" is in the political sense, a division of a city council or other
local government body. A "project" refers to a particular council survey. We
do several a year for different councils. Each time we do, we get assigned a
certain number of kits. The "kit" is just that. Each of my staff gets a kit
containing 20 or so questionnaires. Each kit has its own number. The quota
refers to the hoped for number of respondents in each demographic segment. So
if "M18-39"=5, then the interviewer with that kit is expected to interview 5
males in that age group.

Up until now, I've been basically using a small template database and making
a new one for each new project. I want to be more efficient, and be able to
track interviewer's progress, and so want to have one database that covers
them all, allowing for the variation in the number of wards and other quota
that may or may not be required, including agge and ethnicity.
I have two main objectives for this database. One is the performance
analysis, the other is progress monitoring during the field dates of each
project. My staff have to update me every couple of days with details of
interviews achieved, and I need to be able to see how we're doing. So, for
example, if we're halfway through the period in which the project is in the
field, and I find that interviewer 3 has done one extra 18-39 yr old male in
Ward 4 than their quota required, I can tell interviewer 5 that they don't
need to worry about the fact that they are one short.

That's why I want the database, because each job has a different number of
wards, different age/gender/ethnicity requirements, and at the moment, I am
effectively just recording details on a piece of paper and juggling the
quotas mentally. The time spent fiddling with this DB will pay off when it's
up and running in efficient project management and the ability to track staff
performance histories will be a nice bonus.

After that little novella, anybody who can help in any weay at all will have
my undying gratitiude.

 

[Fred]

If you will forgive the direct wording done in an attempt to be helpful.

I read this twice and still don't understanding the real world items /
processes / objectives that you are trying to DB. You only spent one
somewhat vague paragraph on that, and that used terms that you tried to
define but never really did. May I suggest you give us more on these items
including solid definitions on any unique-to-you terms?

Lots of times the hurdles in thought processes to writing down the question
are the same ones to creatiing the DB structure.

 

[stuartnz]

If you will forgive the direct wording done in an attempt to be helpful.

I read this twice and still don't understanding the real world items /
processes / objectives that you are trying to DB. You only spent one
somewhat vague paragraph on that, and that used terms that you tried to
define but never really did. May I suggest you give us more on these items
including solid definitions on any unique-to-you terms?

Lots of times the hurdles in thought processes to writing down the question
are the same ones to creatiing the DB structure.

I have no problem with frankness. I am happy to try again, although I know
for a fact that I did provide definitions for any "unique to me" terms I used.

Here goes my repetition and restatement:

The company I work for does several resident satisfaction surveys a year for
various councils. Each interviewer on my team gets a kit of 20 or so
interviews to do. Each kit has a variety of quotas, as outlined in the
example I gave. I currently build a new DB for each job, but would like to be
able to use a master DB that could retain the information for all jobs. The
challege is that some of the required fields change from job to job. For
example the number of wards (definition provided in the first post) varies
from job to to job. Some jobs have a requirement to get get a certain ethnic
quota, while others don't. I want to build a DB that lets me record the
ongoing progress of each individual job so that I can monitor the quota
completion across the team (example provided in previous post), but I also
want one that doesn't have to be built from scratch for each new job.

I am very grateful to you for taking the time to reply, but I have to say
that if this post hasn't cleared it up for you, I am at a loss. The reason
my first post was so lengthy was tht I included examples to illustrate what I
was saying, and provided definitions of any specific terms I used. This much
shorter post is simply a restatement without either examples or definitions,
since you stated that the first post was unclear.

 

[Fred]

Those additions helped.

I think that your having to describe this will prove to be harder then the
DB design.

Plus I think I figured our one more thing. I always knew wards to be
geographic divisions, but you said it was a division of a council. I think
that I figured out that you are dealing only with places where there is a
correspondence between the two (unlike where I live where the city council
persons are all "at large") and by division of the council you mean
geographic division (of where the respondents live)? If so:

I'm also assuming that the "20" is merely the number of forms and and merely
goal for a number of interviews. I.E. what matters (and needs tracking)
is interviews and not the 20 forms per se. So, if the person gets 20 forms,
and does 19 interviews, you are not trying to track / record that unused form
as an individual entity.

I'm also assuming that there is never more than one interviewer involved
with a kit. And that kit numbers and project numbers can be made
universally unique. (if not, you'll need to make your own numbers up for
those in the DB which will be PK's.

If the above is right, then I think that a structure with three main linked
tables would be a good one. (With lookup or secondary tables addeed where
useful) I view them as a "pyramid."

At the base is table of conducted interviews. A record for each
conducted interview. And a field for each attribute that must be separately
tracked for quota purposes.

"Ward" and "Ethnicity" would certainly be separate fields. If your age
range /sex quotas ever deal with those separately, then separate age range &
sex fields. If not combine those into a single field. One field is the
KitNum and it it linked to the PK kit # in the kit table.

Next up is a kit table. This should include a PK KitNum field, a projectNum
field, (linked ot the PK projectNum in the projects DB) the name of the
person who's doing the interviews, plus any other info on the kit you want to
record.

Next up is the project table with a PK field ProjectNum plus field to record
whatever you want that relatres to the project.

From this, all of the information that you described that you want can
easily be put together by reports.

 

[stuartnz]

Thank you very much for your patient help, Fred!

 

[Evi]

Would there be a way that Stuart could include a KitAttribute table so that
he can list the attributes required by that Kit rather than having them as
field names in the kit; the foreign key field would be KitID the Attributes
would include a field called Sex (not a yes/no field! - one called
MinAge and one called MaxAge, One called Ethnicity, one called QuotaRequired
and one called QuotaDone where you can keep count how many of each were
done..
So record 1 might say Male, 18, 24, White, 50
Record 2 might read Male, 18, 24, Black, 20

It might be that all wards require the same attributes for their candidates
but,.if this ever chang then this might give more flexibility for different
criteria. Or would this be unnecessarily complex?
Evi

 

[stuartnz]

Would there be a way that Stuart could include a KitAttribute table so that
he can list the attributes required by that Kit rather than having them as
field names in the kit; the foreign key field would be KitID the Attributes
would include a field called Sex (not a yes/no field! - one called
MinAge and one called MaxAge, One called Ethnicity, one called QuotaRequired
and one called QuotaDone where you can keep count how many of each were
done..
So record 1 might say Male, 18, 24, White, 50
Record 2 might read Male, 18, 24, Black, 20

It might be that all wards require the same attributes for their candidates
but,.if this ever chang then this might give more flexibility for different
criteria. Or would this be unnecessarily complex?
Evi

Thank you, Evi. I've just found your reply now, and the "record 1/record 2"
scenarion you outline is what I'm looking for. I will try both methods and
see which fits best. Much obliged!