List box based on query results

[Batman1458]

I apologize, but it has been a few years since I wrote an Access DB and am a bit rusty. I have a schedule DB I am writing. Only certain employees are allowed to work at certain locations. I have a tables: [Employee], [Locations], [Schedule]. In the [Employee] table, I have a field that allows the user to check a box, to identify which locations the employee is authorized to work at, based on a lookup, from the [Locations] table. I can query this and return the results of which employee is listed for which location.

SELECT e.FullName, l.locname
FROM Locations AS l INNER JOIN Employee AS e ON l.ID = e.EmpLocation.Value
GROUP BY e.Fullname, l.locname;


On the [Schedule] table, I am creating a form, in which you pick the location requiring service (schlocation). Based on the location entered in this field, I would like to limit the results that show in the next field (schemployee), based on who is authorized to work in that location, which is defined in the [Employee] table previously mentioned.

Thank you for the help.

 

[PHV]

What is the data type of Employee.EmpLocation ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Batman1458]

It is a lookup field, list box to the [Locations] table.

 

[BigRed1212]

Your data structure isn't clear to me. Are you storing locations at which an employee can work as part of the record for each employee in the employee table? So a series of location fields with yes and no in them? That's what is sounds like.

If you are, I don't think you should. I would have a table of employees, a table of locations, and a table relating employees to locations.

Could you clarify?

 

[Batman1458]

I have a location table that lists criteria for the locations. Employee table, that has a lookup field, linked to the location table and allows multiple locations to be selected by check box. The employee table is where the authorized locations list is kept, for each employee.

Thanks.

 

[PHV]

Have a look here:

http://access.mvps.org/access/lookupfields.htm

http://www.r937.com/Relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BigRed1212]

See if this is helpful:

http://www.sqlfiddle.com/#!2/8e4f1/16

What is there:

CREATE TABLE employees
(`empid` int, `empname` varchar(7), `address` int, `annivdate` datetime)
;

INSERT INTO employees
(`empid`, `empname`, `address`, `annivdate`)
VALUES
(1, 'able', 123, '2011-12-31 18:00:00'),
(2, 'baker', 234, '2013-01-31 18:00:00'),
(3, 'charlie', 345, '1962-01-03 18:00:00'),
(4, 'delta', 456, '1998-05-05 19:00:00'),
(5, 'echo', 567, '2003-06-01 19:00:00'),
(6, 'foxtrot', 678, '2012-12-24 18:00:00')
;

CREATE TABLE locations
(`locid` int, `locname` varchar(9), `locfeature` int)
;

INSERT INTO locations
(`locid`, `locname`, `locfeature`)
VALUES
(1, 'warehouse', 5),
(2, 'shack', 6),
(3, 'store', 7)
;

CREATE TABLE locemp
(`recid` int, `empid` int, `locid` int)
;

INSERT INTO locemp
(`recid`, `empid`, `locid`)
VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 2, 1),
(5, 3, 1),
(6, 4, 1),
(7, 5, 2),
(8, 6, 2),
(9, 6, 3)
;

-- list of all employees
select empname
from employees;

EMPNAME
able
baker
charlie
delta
echo
foxtrot


-- list of locations
select locname
from locations;

LOCNAME
warehouse
shack
store


-- where is baker authorized to work?
select a.locname
from locations a
inner join locemp b on a.locid=b.locid
inner join employees c on b.empid=c.empid
where c.empname='baker';

LOCNAME
warehouse


-- who can work the shack?
select a.empname
from employees a
inner join locemp b on a.empid=b.empid
inner join locations c on b.locid=c.locid
where c.locname='shack'

EMPNAME
able
echo
foxtrot

 

[Batman1458]

BigRed1212,
Thanks for the post. I followed your logic and it works, but my setup is a little different. Instead of using a table to determine what location each employee can work at, I am using a list box, within the employee table. Is there anyway to accomplish the same thing, while using the list box entries? If not, is there a way to have the list box entries, populate and update a table, that can be queried, when changes are made?

any thoughts?

 

[BigRed1212]

See PHV's post about the evil of list boxes above. You really can't do anything at all with them as near as I can tell. I would get rid of the list boxes if you can.

There might be a way to query the records with list box selections and use that query to create and populate a proper table. I don't know. I'll look at it, but in the meantime maybe somebody else will know.

 

[BigRed1212]

This looks promising:

http://www.fontstuff.com/access/acctut11.htm