Basic table/query design

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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.
 
Back
Top