simple (?) Access 2007 question

[mark]

I need help with planning and creating a good data base in Access 2007 for
the following situation.
There are approximately 50 employees. On each day they work they are
assigned to any of 3 locations. (Location A, Location B, Location C). The
assignments change every day, because the company wants to keep rotating
them. I need to be able to see, for example, who was not sent Location C
during last 7 days. What is the best approach to this task?
Thank you.
Mark

 

[Allen Browne]

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

 

[mark]

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

 

[mark]

In Assignment table I have Employee, Location and AssignDate. After using
lookup feature for Employee and Location, their data type was changed to
Number. I can't change it because it says that it is a part of relationship.
When I open Relationship window there is nothing there.

Mark

 

[Allen Browne]

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.)