Combobox showing Staff with relevant qualifications

[Mark Heptinstal]

Hi

I am having some trouble coming up with a solution so that a combobox only lists staff members at a particularly who have the relevant qualifications associated with a service.

Perhaps I have the table design wrong (please see the link at the bottom of my question for the relationship image) to do this kind of thing:

Here's what I am looking to achieve:

A pharmacy provides a service which may have associated qualification(s)/accreditation associated with that service. In order for the pharmacy to provide that service they need to notify which members of staff are going to provide the service which have these qualifications/accreditation, this could be more than 1 person.

The database stores details of staff members along with there qualifications.

I have a continuous form when the user can input/edit the services provided by a pharmacy. I would also like to select the members of staff in the pharmacy who are responsible for delivering to the service. This would either by via a sub form or a pop-up form where the user can select the members of staff associated with that pharmacy, but only those members of staff that meet the qualification(s)/accreditation criteria.

I've tried using the fConcatChild function found here: http://www.mvps.org/access/modules/mdl0004.htm

Though when I use this with StaffQualification the qualifications are not in order so I could not use the concatenated value from the QualificationService as criteria in the query.

I can look to provide an example database if this will help out.
Any help is greatly appreciated.


N.B. Please ignore the relationships not showing in the image, these are the original tables in the system which I am replacing.

http://i54.tinypic.com/7099wy.jpg

[ADezii]

Just subscribing for now, will have a closer look at this scenario, and will return later.

[Mark Heptinstal]

Hi there, thanks.

I've actually managed to come up with a solution using VBA, which when a pop-up form loads it creates an SQL string based on values it pulls from the currently open form.

The key bit I was missing was the "In (x,y,z)" to use the query criteria as i'd never used this before.