One form with many questions

[Cornelia]

I have a database that collects information for out local Search and Rescue
volunteers. I have the following table; volunteer information,
city/community, courses, teams, roles, outcome, equipment used, search
information.

I would like to design a form with the seach information being the prime and
on that formm but also indicate which pieces of equipment were used and which
volunteers in which role participated.

I would also like to design a form for the courses and be able to list the
volunteers.

I have tried subforms but I am not able to pick up the information from the
table with the voluneer or equipment information on it.

Anyone have any suggestions

 

[John W. Vinson]

I have a database that collects information for out local Search and Rescue
volunteers. I have the following table; volunteer information,
city/community, courses, teams, roles, outcome, equipment used, search
information.

I would like to design a form with the seach information being the prime and
on that formm but also indicate which pieces of equipment were used and which
volunteers in which role participated.

I would also like to design a form for the courses and be able to list the
volunteers.

I have tried subforms but I am not able to pick up the information from the
table with the voluneer or equipment information on it.

Anyone have any suggestions

It all starts with the tables! Data is stored in tables; tables are
fundamental; a form is just a tool to manage data in a table. You will
probably need some more tables! Could you describe the nature and
relationships of the tables that you do have?

 

[Cornelia]

Volunteer information is where their personel information goes into
city/community is for the volunteer information
team is for the volunteer information

course is to record the courses offered and would like to link somehow to
show which volunteers have taken

search is to record every search
equipment is for the search
outcome is for the search
team is for the search
outcome is for the search
volunteer is for the search
role is for the volunteer at the search

Also we are using the 2002 version of access if this helps

 

[John W. Vinson]

Volunteer information is where their personel information goes into
city/community is for the volunteer information
team is for the volunteer information

course is to record the courses offered and would like to link somehow to
show which volunteers have taken

You need another table to manage the many to many relationship:
Enrollment
EnrollmentID <autonumber primary key>
VolunteerID <link to Volunteer Information's primary key, who took the
course>

search is to record every search
equipment is for the search

Do you have a table of equipment? If so you need another many-to-many table
with fields for SearchID and EquipmentID.

outcome is for the search
team is for the search

What's the structure of the Team table? VolunteerID and SearchID and Role I'd
suggest...

outcome is for the search

ummm... twice? Is that a memo field, a list of values, ...?

volunteer is for the search
role is for the volunteer at the search

Also we are using the 2002 version of access if this helps

Makes very little difference. At this level you could be using Access 2007 or
Access 1.0.

 

[Cornelia]

Good Morning John, thanks for all of this help. I created the enrollment
table as suggested.

On the equipment suggestion, I am guessing that neither are the primary key,
should this be an automatic number?

Do you have a table of equipment? If so you need another many-to-many table
with fields for SearchID and EquipmentID.

On the Team Question, it is a list of Team Names and is a list for the
volunteer information table.

What's the structure of the Team table? VolunteerID and SearchID and Role I'd
suggest...

On the Outcome Question, this is a list of 3 different outcomes for the
searches