simple (?) Access 2007 question

You're on the right track, but it looks like that Lookup Wizard messed you
up. Just skip the wizard, and create your own tables and relationships.

Presumably you can give each location a unique name, so create a Location
table with one field like this:
- LocationID Text (24 char) primary key
Then enter the names of each location.
(You can have other fields as well if you need them.)

Employees are not quite so clear. There could conceivably be 2 employees
with the same name, so you probably can't use names as the primary key. I
suggest an Employee table with fields:
- EmployeeID AutoNumber primary key
- Surname Text family name
- Firstname Text Christian name
- Inactive Yes/No a box to check when the person leaves.

Your main table with then have an EmployeeID (number) field, and a
LocationID (text) field. To select the employee, you can use a combo to
select the employee.

To do that, you need a query to use as the RowSource for the combo. Create
this query:
SELECT EmployeeID,
Surname & ", " + FirstName & IIf([Inactive], " (inactive)", Null) AS
FullName
FROM tblEmployee
ORDER BY Inactive DESC, Surname, Firstname, EmployeeID;
Save the query as (say) qryEmployee.

So, the combo's properties will be:
Row Source qryEmployee
Bound Column 1
Column Count 2
Column Widths 0
The combo has 2 columns, but the first one (the number) is zero-width, so it
displays the name to choose from, but saves the correct number in your
table.

Don't forget to create the relationships between your 3 tables.
(Relationships window.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mark" wrote in message
...
Thank you Allen very much.
For data entry purposes - it would be easier to enter info into Assignment
table if Assignment table had fields: EmployeeName, LocationName,
AssignDate. Will it work if I create field EmployeeName instead of
EmployeeID and LocationName instead of LocationID?
Mark


"Allen Browne" wrote in message
...
3 tables:
a) Employee table: one record for each staff member (EmployeeID primary
key)

b) Location table: one record for each location (LocationID primary key)

c) Assignment table: one record for each staff member assigned on each
date. Fields:
- EmployeeID which staff member
- LocationID what location
- AssignDate when they were assigned.

You can then use a subquery to select the employees who have not been
assigned to a location in a period:
http://allenbrowne.com/subquery-01.html#NotThere