Trying to create a multi-column combo-box listing each employees qualifications

[bruh V2]

Hello all,



I am currently developing an access database which will be used to help my department supervisors plan work for the upcoming day as well as document changes on the fly when needed. Right now I a split form that has a column in the datasheet that will allow a supervisor to select a technician from a combo-box to assign them to that specific job. The goal was to have the column just show the techs name, however when you click on the box to select someone else, it displays their name and what qualifications (quals) they have. I have used multi column combo-boxes before so I understand how they work.


The issue is that the linked table that stores the qualifications each employee holds (a linked odbc table) has a separate record for each qualification that a tech holds. What ends up happening is that a Techs name shows up several times on the list depending on how many quals they have instead of displaying their name once and then each qualification in its own column on the same row. Please see the linked photo for a visual explanation of the situation.

Basically the combo-box will only display the active techs from Box 1 in the list to help keep the list small. Box 2 is the linked table that lists the quals for each technician (This linked table only updates the quals and their statuses once a day at midnight, the rest of the time the data is static). Box 3 is a loose idea of what you would ideally see when you click on the combo-box.

Example Photo

Any suggestions would be great.

[isladogs]

Create a crosstab query using both tables to get the box 3 result. Use that as the row source for your combo

[pbaldy]

Play around with the crosstab query wizard. I think you could get that result.

[bruh V2]

Create a crosstab query using both tables to get the box 3 result. Use that as the row source for your combo

Play around with the crosstab query wizard. I think you could get that result.

Thanks for the responses. Unfortunately it looks like that crosstab will only work if you are trying to calculate the fields somehow. Not sure if there is something i'm missing though. I'm no expert in Access so it could just be me.

[isladogs]

It should work.
Suggest you post a cut down version of your db with the relevant tables so one of us can create a suitable crosstab for you

[Micron]

Methinks you're confusing Crosstab with Totals. In my limited usage of crosstabs, I have found them too wonky to use with forms very much. One time, they produce 4 fields, the next time 6, the next time 8 (for example). That you could fit a volatile field count into a combo's columns and be able to rely on it is something I wouldn't expect. At the very least, you'd have to ensure that the number of fields returned never exceeds your combo column count.

[davegri]

bruh_sched_Qual-v1.zip
See attached. This should give you some ideas. The crosstab query supplied could be appended to an empty temp table (box 3) on a daily basis.
You would need to flesh out the example to include the qual names instead of the integer in the temp table if necessary.
The supervisors could update the temp table via a form with the temp table as the recordsource, with a combobox for each column.
The rowsource for the comboboxes would all be the same, based on a value list or a new lookup table for the qual areas.