Combo box to query

[crmorgan]

I have a table that has a list of employees and the locations they know. The locations are fields. The data is in yes/no check boxes. For example, John knows Atlanta, James knows Atlanta/Miami/Nashville, and Jordan knows Nashville.

Atlanta Miami Nashville
John yes
James yes yes yes
Jordan yes

I created a combo box on a form with a list of locations. How can I create a query that will give me the employees that know the location that is selected in the combo box. For example, if I click on Atlanta, the query will pull both John and James and I can run a report from that.

 

[jrbarnett]

Normalise your data.

You want three tables:

Employees
EmployeeName

Locations
LocationName

then a third table: EmployeeLocations

with
EmployeeName and LocationName.

Adding the ability of an employee to "know" a location is simply a matter of adding a record to this table. Likewise, if they move to concentrate on other things, removing this record will stop their knowing about it.

Finding out which employees know which locations is then simply something like:

Select employeename
from employeelocation
where location = 'Nashville'

With your current system - you will need different queries for different locations, and also need to add another field to your locations table if another location needs to come along (if you need to add Washington or Dallas for example).

John

 

[crmorgan]

I used this formula to generate my data for one [Location]:

IIf([Forms]![Frm_OpenReports]![Combo12]="Atlanta",False,True)

How can I get it to work for all locations in the query? For example, I want one query to pull data for either Atlanta, Miami, or Nashville. Once the locatin is selected in the combo box, the data will pull. Again, it works individually for each [Location]

 

[dhookom]

Are you unable or unwilling or unknowledgeable regarding jrbarnett's suggestion about normalizing your data?

You can try a normalizing union query like:

SELECT Employee, "Atlanta" as Location
FROM tblNoNameGiven
WHERE [Atlanta]=True
UNION ALL
SELECT Employee, "Miami"
FROM tblNoNameGiven
WHERE [Miami]=True
UNION ALL
SELECT Employee, "Nashville"
FROM tblNoNameGiven
WHERE [Nashville]=True;

You can then use this as the Row Source of your combo box

SELECT Location
FROM quniEmpLocs
WHERE Employee = [Forms]![Frm_OpenReports]![cboEmployee]

Duane
Hook'D on Access
MS Access MVP