Multiselect list box advice needed.

[Dinger_80]

I have a more general question then a specific one. Kind of looking for some guidance and opinion of what to do. To give a bit of back ground to understand where I am coming from and would like to go. I use access to gather information on testing that is conducted at my company. In the database I designed I set up a multiselect list box for when the people who conducted the test can check off the test equipment that they used. I did this because at anytime any number of different items can be used. Though I don't think we have ever used more then 10 test items. The reason for tracking the items that were used is if they, when out for yearly calibration, come back out of spec we can look at what tests used that equipment. As I further develop my system, I am starting to ask for more information from the requesters. I would like them to be able to check off what individual items are in the item being tested. Creating that list isn't the hard part for me.

My question comes in here. I know that using a multiselect list box is frowned upon as they can be sloppy to deal with when looking to extract data. Being as I am looking to expand my database, I was looking for advice on what to do. I would still like to give the option of a multiselect list box, but should I create more fields and have the items used put into these new fields? Should I do the same for equipment that is used for the testing. If not and the list box is ok, how best to go through all the records to gather what ones used the specified equipment? Any advice on what to do, or other ideas on how to keep the simplicity and keeping or adding functionality moving forward would be appreciated.

 

[jdraw]

Your question is a little too abstract for me. Can you work through an example using some sample data? What exactly is your database about? What tables are involved, users etc?

Sounds like you have:

Tests
-testId
-testName
-testNotes


Equipment
-equipID
-equipName
-equipLocation


TestInvolvedEquipment
-testEquipID
-testID FK
-equipID FK
-testDate
-testDoneBy

You might even have

tblEquipCalibration
-equipCalibrationID
-equipID FK
-calibrationDoneBy
-calibrationDate
-otherCalibrationInfoForThisEquip

Just some thoughts for consideration.

 

[Dinger_80]

I have about a dozen total tables. For the purpose of this discussion I will just go over the ones that matter. I have 1 main table that collects the information about the test to be conducted, I am giving a briefer list of items on the table, this is just an idea of how I have it set up. I will also list the other tables that are relevant to this.
TestRequestTable
TestRequestNumber - PK
TRNumber - a made up number used to identify the requests
Requester FK
TypeOfTest FK
CatalogNumber FK
EquipmentUsed - multiselect list box - FK

CalibratedEquipmentTable
Manufacturer
ModelNumber
Description
SerialNo
LastCal
CalDue

CatalogNumberTable
ID - PK
CatalogNumber
ProductName
ProductLine
Manufacturer
NumberOfComponents

At my company we have well over a thousand different catalog numbers. Try as I might, I can't get users to put catalog numbers into the system correctly enough to uniquely identify what is being tested. So what I would like to do is, since each test request on any given catalog number can have up to 5 different components and combination of said components, to create a field(s) in the test request table that gathers the items being tested. So if it is a lighting product it might have drivers, LED's and a lens. Then have a simple way for users to select the parts that go with the test they want done. I am almost thinking that having multiple fields would be better but I don't know now.

My equipment list was done as a multiselect list box because it make it easy for technicians, such as myself, to click on the items being used. I list that has the information next to it and put a pdf report on the server of the items used works great. I just know that when trying to go through the multiselect list box to gather up what items were used to test and item, it gets a bit messy. I guess I am looking for ideas on if, since I am adding on to the database, should I create a multiselect list box for people to select the components they will test, or create multiple fields and just make a drop down for each type of item that may be tested?