Are checkboxes a good solution for multiple categories of people?

[dwAccessUser32]

I have a form where information on a person is entered (e.g., first name, lastname, dob, etc.)

I want to categorize the people by the type of person that they are on this form (e.g., employee, contractor, agent, salesman, etc.)

There are around a dozen categories of people coming from a lookuptable, tluPersonType.

The tricky part is that sometimes a person is a member of multiple categories (e.g., he might be an employee and a salesman) so I need to determine how to on one form let a person select multiple person categories.

I have seen listboxes on the web that allow people to make multiple selections. How can I do this with my existing table structure?

tblPeople
PersonID
PersonTypeID
FirstName
...

tluPeople
PersonTypeID
PersonType

Does anyone have an idea on how to solve this problem? Any advice will be greatly appreciated.

 

[traingamer]

How can I do this with my existing table structure?

You can't.

Your table is designed to hold a single piece of information.
One possible solution is to have another table:
e.g.
tblPeopleType
PersonID
PersonTypeID

that contains all of the Persontypes that apply.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[dwAccessUser32]

Thank you for your reply.

So, if I made a junction table this might be the way to solve it?

tluPersonType
PersonTypeID
PersonType

tblPerson
PersonID
PersonTypeID

txj
PersonPersonTypeID
?

Can you give me any guidance on how to set this up properly? Thanks again for your help.

 

[hwkranger]

Don't put all the possibilities in 1 table....

you need:

Person Table >-< Type Table

So it looks like

Person >- PersonType -< Type

meaning,

A person can have many types.

It's a Many-to-Many relationship.

So You'll have

Person
------
PersonID (Pk)
DoB
ETC

Type
------
TypeID (Pk)
Name
Desec

PersonType
------
TypeID (Fk)
PersonID (Fk)

Combo of TypeID/PersonID (Pk)

*Pk -Primary Key
*Fk - Foreign Key

This is about the best I can do in a text description. Hope it helps.

Randall Vollen
Merrill Lynch

 

[dwAccessUser32]

Thanks for the help, I really appreciate it. So, I built the junction table and now have an unbound subform that has check boxes with all my person types.

The only part I can't figure out is the after update event of each check box to append or remove the record.

Do you have any idea how to set the after update event so that it works?

Thanks again!

 

[hwkranger]

use SQL

After the update,

if checked -- INSERT INTO (TABLE) VALUES...
if checked if unchecked -- DELETE FROM (TABLE) WHERE (PKEY) = ...


Randall Vollen
Merrill Lynch

 

[dwAccessUser32]

Thanks again for your excellent help.

Ok, I have it like this in the code:

Private Sub Check0_AfterUpdate()
If
checked INSERT INTO tjxPersonPersonType VALUES
(PersonTypeID,PersonID)
Then

EndIf
End Sub

Does that look about right? If I can figure out how to do one, I can get the delete with no problem.

 

[hwkranger]

That look sabout right... but the if is wrong.. And you don't have columns..

it's

INSERT INTO TABLENAME
( COLUMN1, COLUMN2 )
VALUES
( VALUE1, VALUE1)



Randall Vollen
Merrill Lynch

 

[Remou]

And don't forget, it will be an SQL string in VBA:

[tt]strSQL="INSERT INTO TABLENAME " _
& "( Field1, Field2 ) " _
& "VALUES ( " & VALUE1 & ", " & VALUE2 & " )"
DoCmd.Execute strSQL[/tt]

Don't forget the delimiters for text and date.

 

[dwAccessUser32]

Thanks!