Advice on design

[Guest]

I would like some advice/help with a "Skills" database I'm trying to create.
I have a Volunteer (Name/ contact info) table and also a Skills table. (~ 50
skills)
Right now I have Skills as a subform in datasheet view on the Volunteer main
form, so all is fine there. My problem is on some of the skills there is a
sub category. ie. Date trained, org number, License number and date etc that
requires input. I’m not sure where to go from here. Is it possible that when
you select a Skill that has a subgroup that it will trigger another subform
to populate? I’m far from an expert with Access so the easier the better.
Thanks much for your help

 

[Steve Schapel]

TBoe,

My first question here would be whether the "sub category" data will
ever need to be used for anything other than reference purposes. For
example, sorting, selecting, calculating. If not, you could probably
just add one extra field to the VolunteerSkills (or whatever you called
it) table, add a corresponding control to the subform, and then just
enter the date trained, org number, License number, or whatever it is
that is required, depending on the Skill entered. If you wanted some
sort of visual reminder of what sub-category information each Skill
requires, this would be relatively easy to organise.

However, if you need more precise validation, or different data types
because of processing requirements, or more than one discrete
sub-category data element for any of the skills, then it will be more
complicated, and maybe your idea of an additional subform will be necessary.

If you need more explicit help, could you post back with some more
details about the sub-category data, with examples.

 

[Guest]

Thanks much for the reply Steve. I think it will only be needed for
reference purposes. But would be needed when printing out reports etc.
Here are some examples of the skills and sub info.

SKILLS SUB INFO

Shelter Assistant Date Trained: Date of last refresher:
Traffic Control
Crowd Control
Damage Assessment Date Trained Organization:
Shelter Operation Date Trained Organization:
Mass Care Date Trained Organization:
Fire Suppression Agency
Ham Radio License Tech License or Higer?(ckbox) EMCOMM (Ckbox)
Public Health Agency\Organization
Etc…….

I do have a “Skill Info†subform but I get an error when I try to type in
the information.
Is there something I have to do with the table or subform or could you
explain your suggestion in a little more detail. Thanks very much for your
help.

Tboe

 

[Steve Schapel]

Tboe,

Well, I don't want to cut across the approach you are already working
on. What is the table/query that the Skill Info subform is based on,
what fields are there, how is it related to your main form, and what is
the error message?

My original idea was to consider this... I assume you have a table for
the skills for each volunteer, with fields something like this:
VolunteerID
Skill
Am I right?

Ok, would it work to add another field, called SkillInfo, so if
Volunteer #86 has acquired skill "Damage Assessment", then in the
SkillInfo field you would enter the date trained and organisation info,
sort of as a Note field, which would simplify the design, but that would
limit the ways this data could be used. However, now that I see the
examples you provided, it looks like this idea would leave you too much
at risk of info being omitted, or data entry more long-winded.

So, let's see how you have your data organised so far, before moving on.

 

[Guest]

Wow! Why didn't I think of that. You were correct on the Skills table. I
also
had a Skills info table and at one time I had it working like this. If I
were to choose a skill in the Skill sub form the corresponding skill info
would come up on the Skill info form. Don't ask me how I did that though.
That's way over my head. I wonder
why I can add text to the Skill info field? This is the error message that
I get.
"The text you entered isn't an item from the list" Maybe I shouldn't
create a list
and just input the skill info manually. Thanks again. I'll keep hacking
away.

 

[Steve Schapel]

Tboe,

Ok, well I still don't have a good picture of what you've got there.
You'll need to give me the details of your tables, and what you've got
on the forms so far.

It sounds like you have a Combobox for the entry of the Skill Info.
What is supposed to be getting entered in here, and what are the items
in the drop-down list?

 

[Guest]

This is what I had Steve. The form included the contact info and 2 subforms.
The 1st sub form was a combo box in datasheet view that listed the skills from
my Skills table. (SkillID, auto number, Skill, text) I then had another sub
form
with a combo box based of the skillsinfo table(SkillinfoID, auto number,
SkillID, number, Skillinfo, text). The fields for this included things like
Date,
org number etc, that required input to coincide with a particular skill. I
was hoping
it would work when you select a skill, the Skills info would pop up on the
Skill info
sub form and that you'd be able to populate the data. That is way over my
head. So what I did was create the SkillsInfo sub form and inserted a text
box to enter the info. It get's the job done but I'm sure it could be a lot
better. Thanks much,

Tboe