How do I select a combination of options?

[Patty Stoddard]

How do I create a field on a form that will allow the user to select more
than one option in the list? For example, the question asks the user to
check all that apply. I don't know how the table could store multiple values.

 

[Allen Browne]

If a respondant can give multiple answers to one question, then your
Responses table will need fields like this:
QuestionID which question is this an answer to
AnswerID which answer did the respondant choose
RespondantID who gave this answer to this question
AnswerText use where a text answer is required
(e.g. for "Other, please specify")

For an example of how to set up a database to handle survey questions and
answers, see:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

The Access 97 version is at:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 97'

 

[peregenem]

How do I create a field on a form that will allow the user to select more
than one option in the list? For example, the question asks the user to
check all that apply.

The usual front end devices are multiple checkboxes in a frame or two
listboxes with buttons to move items between the 'available items' list
box and the 'selected items' listbox. However ...

I don't know how the table could store multiple values.

.... you cannot begin to design the front end application until your
requirements have been defined. For the database design, research
many-to-many relationship tables.

 

[Patty Stoddard]

Thanks! That's exactly what I needed.

If a respondant can give multiple answers to one question, then your
Responses table will need fields like this:
QuestionID which question is this an answer to
AnswerID which answer did the respondant choose
RespondantID who gave this answer to this question
AnswerText use where a text answer is required
(e.g. for "Other, please specify")

For an example of how to set up a database to handle survey questions and
answers, see:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

The Access 97 version is at:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 97'

 

[Patty Stoddard]

This is an employee applicant database.
I have set up a separate tables for skills. How do I set up the junction
table to create records matching employeeID with skillID?

 

[Allen Browne]

The 3rd table will contain fields:
EmployeeID relates to Employee.EmployeeID
SkillID relates to Skill.SkillID

To interface it, create a main form bound to the Employee table, with a
continuous subform bound to this junction table. In the subform, put a combo
box that has the Skill table as its RowSource. The user can then select a
skill for the employee on the first row of the subform, and then another
skill on the 2nd row, and so on.

To read up on another example of creating junction tables, see:
Relationships between Tables
at:
http://allenbrowne.com/casu-06.html

 

[peregenem]

The 3rd table will contain fields:
EmployeeID relates to Employee.EmployeeID
SkillID relates to Skill.SkillID

To interface it, <<front end design snipped>>

Something not mentioned (even in the link) is that the table requires
constraints.

UNIQUE (EmployeeID, SkillID)

would allow one employee to have multiple but not duplicate skills
(probably the OP's intention).

UNIQUE (EmployeeID)

would allow one employee zero or one skill.

The absence of a UNQUE constraints would allow an employee to have
duplicate skills which I assume is not the OP's intention.

Both EmployeeID and SkillID would require FOREIGN KEY constraints with
appropriate declarative referential integrity (presumably CASCADE for
both DELETE and UPDATE actions) to ensure only actual employees may
have known skills.

It probably goes without saying that both columns should be NOT NULL.

Controls in front end applications cannot be trusted to maintain data
integrity.

 

[Patty Stoddard]

How do I establish a foreign key constraint?

Something not mentioned (even in the link) is that the table requires
constraints.

UNIQUE (EmployeeID, SkillID)

would allow one employee to have multiple but not duplicate skills
(probably the OP's intention).

UNIQUE (EmployeeID)

would allow one employee zero or one skill.

The absence of a UNQUE constraints would allow an employee to have
duplicate skills which I assume is not the OP's intention.

Both EmployeeID and SkillID would require FOREIGN KEY constraints with
appropriate declarative referential integrity (presumably CASCADE for
both DELETE and UPDATE actions) to ensure only actual employees may
have known skills.

It probably goes without saying that both columns should be NOT NULL.

Controls in front end applications cannot be trusted to maintain data
integrity.

 

[Allen Browne]

I'm not clear what you need here.

To create the relation with referential integrity, use the Relationships
window (Tools menu.)

If you want to make sure the combination of EmployeeID + SkillID is not
duplicated, then use the combination of the 2 fields are the primary key.

 

[peregenem]

I'm not clear what you need here.

I assume the OP needs a way of ensuring that only an EmployeeID that
exists in their Personnel tables is used in their 'junction' table and
that when EmployeeID is UPDATEd in the Employees table the change is
automatically made in their 'junction' table too (ON UPDATE CASCADE)
and a DELETE is similarly propagated (ON DELETE CASCADE). Same goes for
SkillID.

How to I establish the foreign key constraint? That's a lifestyle
choice: GUI tools, SQL code, DAO, ADO, etc. I'd recommend doing it in
SQL DDL (data definition language) when creating the table (via the OLE
DB provider for Jet 4.0 is best) e.g.

CREATE TABLE Skillsbase (
EmployeeID CHAR(10) NOT NULL
REFERENCES Personnel (EmployeeID)
ON DELETE CASCADE
ON UPDATE CASCADE,
SkillID CHAR(10) NOT NULL
REFERENCES Skills(SkillID)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (EmployeeID, SkillID));