Employee Names in Table

[Confused]

New to Access

I have a table "customers" that has Customer name and who support them with
about a 1000 records. The who supports them is in three columns with names
only, i.e Account Manager, Project Manager, Product Manager.

What is the best way to make it so that when an employee changes I can
update the records? I have a separate table with employee ID and name.
Again, the customer's table only has names.

The ultimate goal is to be able to search based on Employee name rather
three different searches.

 

[Jeff Boyce]

From your description, it sounds like you have experience using a
spreadsheet.

Unfortunately, committing spreadsheet on Access only confuses both you and
Access!

As a relational database, Access "expects" well-normalized data for its
features/functions to work well. If "normalized" and "relational" are not
familiar terms, plan to spend some time coming up to speed on them.

It sounds like you have customers, employees, roles, and
customers-supported-by. This implies four tables in Access.

Because you could (theoretically) have more than one customer with the same
name (e.g., John Smith), I'd suggest you use an Autonumber field as a
primary key in your [Customer] table. Similar arguement for the [Employee]
table.

Your [Roles] table is a lookup table, listing the valid roles your employees
can support in ... and NOTE! You have only three roles today ... with your
design, you have to remake the database/application if you ever add a
fourth. With the [Roles] table, you only need to add a role!

Your [Customers-Supported-By] table would hold the CustomerID, the
EmployeeID, and the Role (or RoleID, if you decide to do that). You might
want to include two date/time fields (FromDate, and ToDate), to indicate
when the Customer was supported by the Employee serving in that Role. Note
that one customer could have multiple [Customers-Supported-By] records, one
for each "Role" of support (and for each time frame, if you include that).

Confused more now?!<g>

By the way, plan on NOT working directly in the tables. Access tables may
look like spreadsheets, but they aren't. Use forms to handle adding/editing
data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Confused]

So for simplicity what about having the employee table have Employee ID,
Name, and Title(to match what's in the supported-by table)? So in order to
make it relate, should I do an update query e.g, add this employee ID to
this column where name = Mary Jane? I didn't know if it would be necessary
to have three columns each containing a corresponding ID or would I? What
steps would I take to make this happen?

From your description, it sounds like you have experience using a
spreadsheet.

Unfortunately, committing spreadsheet on Access only confuses both you and
Access!

As a relational database, Access "expects" well-normalized data for its
features/functions to work well. If "normalized" and "relational" are not
familiar terms, plan to spend some time coming up to speed on them.

It sounds like you have customers, employees, roles, and
customers-supported-by. This implies four tables in Access.

Because you could (theoretically) have more than one customer with the same
name (e.g., John Smith), I'd suggest you use an Autonumber field as a
primary key in your [Customer] table. Similar arguement for the [Employee]
table.

Your [Roles] table is a lookup table, listing the valid roles your employees
can support in ... and NOTE! You have only three roles today ... with your
design, you have to remake the database/application if you ever add a
fourth. With the [Roles] table, you only need to add a role!

Your [Customers-Supported-By] table would hold the CustomerID, the
EmployeeID, and the Role (or RoleID, if you decide to do that). You might
want to include two date/time fields (FromDate, and ToDate), to indicate
when the Customer was supported by the Employee serving in that Role. Note
that one customer could have multiple [Customers-Supported-By] records, one
for each "Role" of support (and for each time frame, if you include that).

Confused more now?!<g>

By the way, plan on NOT working directly in the tables. Access tables may
look like spreadsheets, but they aren't. Use forms to handle adding/editing
data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

New to Access

I have a table "customers" that has Customer name and who support them
with
about a 1000 records. The who supports them is in three columns with
names
only, i.e Account Manager, Project Manager, Product Manager.

What is the best way to make it so that when an employee changes I can
update the records? I have a separate table with employee ID and name.
Again, the customer's table only has names.

The ultimate goal is to be able to search based on Employee name rather
three different searches.

 

[Jeff Boyce]

I'm not familiar with your scenario, so I can't tell you if having an
employee record with [Title] makes sense. For instance, it may be that you
have an employee named Sue Smith who was an Account Rep last year but is a
Manager this year. For your purposes, do you care that she, as an Account
Rep, helped customer #99 last year, and is helping that same customer this
year, but as a Manager?

How you design your table structure depends on what you need to do with the
data.

In the example above, you would NOT include [Title] in the employee record.
You would include CustomerID and EmployeeID in the "customer-supported-by"
record, along with the RoleID and the DateSupported.

Focus first on defining the entities (things about which you need to keep
data) and the relationships among those entities.

Then start on the forms...

Regards

Jeff Boyce
Microsoft Office/Access MVP

So for simplicity what about having the employee table have Employee ID,
Name, and Title(to match what's in the supported-by table)? So in order
to
make it relate, should I do an update query e.g, add this employee ID to
this column where name = Mary Jane? I didn't know if it would be
necessary
to have three columns each containing a corresponding ID or would I?
What
steps would I take to make this happen?

From your description, it sounds like you have experience using a
spreadsheet.

Unfortunately, committing spreadsheet on Access only confuses both you
and
Access!

As a relational database, Access "expects" well-normalized data for its
features/functions to work well. If "normalized" and "relational" are
not
familiar terms, plan to spend some time coming up to speed on them.

It sounds like you have customers, employees, roles, and
customers-supported-by. This implies four tables in Access.

Because you could (theoretically) have more than one customer with the
same
name (e.g., John Smith), I'd suggest you use an Autonumber field as a
primary key in your [Customer] table. Similar arguement for the
[Employee]
table.

Your [Roles] table is a lookup table, listing the valid roles your
employees
can support in ... and NOTE! You have only three roles today ... with
your
design, you have to remake the database/application if you ever add a
fourth. With the [Roles] table, you only need to add a role!

Your [Customers-Supported-By] table would hold the CustomerID, the
EmployeeID, and the Role (or RoleID, if you decide to do that). You
might
want to include two date/time fields (FromDate, and ToDate), to indicate
when the Customer was supported by the Employee serving in that Role.
Note
that one customer could have multiple [Customers-Supported-By] records,
one
for each "Role" of support (and for each time frame, if you include
that).

Confused more now?!<g>

By the way, plan on NOT working directly in the tables. Access tables
may
look like spreadsheets, but they aren't. Use forms to handle
adding/editing
data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

New to Access

I have a table "customers" that has Customer name and who support them
with
about a 1000 records. The who supports them is in three columns with
names
only, i.e Account Manager, Project Manager, Product Manager.

What is the best way to make it so that when an employee changes I can
update the records? I have a separate table with employee ID and name.
Again, the customer's table only has names.

The ultimate goal is to be able to search based on Employee name rather
three different searches.