Basic table/query design

[Guest]

Hi all. I would appreciate any advice you could give on this and apologise if
it seems rather obvious.

I'm trying to set up a simple database to handle staff details within our
office (approx 500 people.) Agents frequently move between teams and one of
the main things I want to be able to see is a historical trail of what
managers they have worked for and the relevant dates.

I currently have an Agents table (Pay Number, Name, DoB, Contract start and
end date) and a Team table (AutoID, Agent Pay No, Manager , Start and End
Dates in team.) What I am having problems with is working out the best way to
validate and restrict the entries in the Team!Manager field.

The list of possible managers is a subset of the list of all employees. I've
tried adding a Boolean field to the Agent table, running a query against that
to provide a list of valid managers then setting this as a lookup for the
Team!Manager field, but it still let me enter anything I wanted. I also tried
creating a third "managers" table with just a list of valid managers pay
numbers, but I couldn't find an effective way of keeping this updated as the
list of managers changes.

I want to be able to track a number of things in the same way, but getting
this first one done properly should show me the way to go with this.

Thanks, Pete

 

[Ed Robichaud]

Either of your strategies would work. The first one (having a Yes/No for
managers in the tblAgents) is probably the easiest to maintain. As you
already discovered, use a query that filters on a "yes" value as the record
source of your combo-box. Make your combo "limited to list=yes" (see the
properties list). This will force users to enter only an agent who is also
a manager.

-Ed

 

[Guest]

Can each agent have multiple Contract Start and Contract End dates? If so,
Contracts should be a separate table. Similar question for Managers: can a
manager be associated with multiple contracts? Does a contract involve more
than one agent ? Answers to these questions will guide the structure of your
database. In general, a good way to limit the combo box list to managers is
to add a Manager Yes/No field to the Employees table and use a query limited
to Employees with the box checked as the row source for the combo box.