Multi-select List Boxes

[Jaycee]

Two (hopefully) quick and easy questions:

1. The selections made on the first record apply to all
records. How do I make the selections unique to each
record?

2. The box is a list of degrees (Bachelor's, Associate's,
Ph.D., etc.). If the selection(s) are stored in the
field "Degrees," how does that show up on a report if
there's more than one selection per employee? As a
list? If I wanted to sum the degrees (6 Bachelor's, 2
Associate's, etc.), would I be able to do that? I guess
my question is this: Is a multi-select list box what I
need for this purpose?

Thanks in advance for any advice.

Jaycee

 

[James Goodman]

2. In a word no. You should never try to store more than 1 item of data in
any given field. If you do data-retrieval becomes much harder. You should
therefore look to re-structure your table so that it has:
EmployeeID
DegreeTypeID

You will then need to create a lookup table which contains all of the
degree-types.

A query would then be simple, such as

SELECT Count(EmployeeID) As NoGraduates, DegreeType
FROM tblDegrees D INNER JOIN tblLUPDegreeTypes DT
ON D.DegreeTypeID = DT.DegreeTypeID
GROUP BY DegreeType

This query would give you the number of employees with each of the different
degree-types...

SELECT * FROM tblDegrees
WHERE DegreeTypeID = 1
This type of query would give you the employees who have a DegreeTypeID of
1, which would be defined in tblLUPDegreeTypes

 

[Rick Brandt]

Two (hopefully) quick and easy questions:

1. The selections made on the first record apply to all
records. How do I make the selections unique to each
record?

2. The box is a list of degrees (Bachelor's, Associate's,
Ph.D., etc.). If the selection(s) are stored in the
field "Degrees," how does that show up on a report if
there's more than one selection per employee? As a
list? If I wanted to sum the degrees (6 Bachelor's, 2
Associate's, etc.), would I be able to do that? I guess
my question is this: Is a multi-select list box what I
need for this purpose?

It is improper database design to store multiple values in a single field.
What you should have are two tables with a one-to-many relationship. The
second table is where you store your multiple degrees with each one having
its own row in the new related table. A form/subform would typically be
used for the interface.

When you make a ListBox multi-selectable it loses the ability to store a
value. It will always have a value of null. Because of the principle
stated above, the reason to even have a multi-selectable is only for use in
a code routine where you traverse the ItemsSelected collection of the
ListBox and then "do something" with the values. It is not intended as a
device for storing data in a table.

 

[Wayne Morgan]

While you can probably make it work, since there are a limited number of
degrees I would probably just opt for a few Yes/No fields, one for each
degree, and use check boxes to indicate whether or not they have that type
of degree. This would also make it easy to sum, Yes or True is stored as -1
and No or False as 0. Since this is a number you can sum it and take the
absolute value (Abs()) to get a positive number.

 

[Jaycee]

Wayne,

That's actually how I had set it up to begin with, but
then I posted a question about how to sum the values and
was told I had it set up wrong.

Anyway, I was able to get the sums per degree but then
got stuck on how to calculate the percentage of employees
who held one or more degrees.

Thanks for your assistance!

Jaycee

 

[Wayne Morgan]

To get the percentage of employees holding one or more degrees you could use
DCount to get the count. For the parameter of the function you would use Or
statements to combine the Yes/No fields so that if any one of them was Yes
then that employee would be counted. You would then divide this number by
the total number of employees and multiply by 100.

Example:
intEmpWithDegrees = DCount("*", "tblMyTable", "Bach=True Or Master=True Or
Doc=True")