Seemingly simple problem, causing BIG problems

[Guest]

Maybe an outside view on this will help me...

A DB I working on has a set of tables and forms that utilize Staff
information. For a variety of reasons I had to separate staff "first" &
"last" names into separate feilds.

I am running into trouble now because I have people with similar first &
last names on staff. When I select the desired staff member on one form.. ie.
Mike Smith, the information saves to a table properly; but when opened on a
different form the Staff Name Combo box automatically selects the first
"Mike" in the list... rather than the one origionally assigned to the booking.

Also, queries seem to duplicate results when the staff name is included. If
a booking is made on a specific date, the booking will often show up twice or
three times... once for each occuance of the staff first name.

I thought about switching over to a staff #.... however this simply does not
seem to be a feasible option for those who plan to use the database.

Any suggestions would be appreciated.
Thanks

 

[BruceM]

Maybe an outside view on this will help me...

A DB I working on has a set of tables and forms that utilize Staff
information. For a variety of reasons I had to separate staff "first" &
"last" names into separate feilds.

A good practice in any case. You can use a query, text box, or code to
combine them for viewing.

I am running into trouble now because I have people with similar first &
last names on staff. When I select the desired staff member on one form..
ie.
Mike Smith, the information saves to a table properly; but when opened on
a
different form the Staff Name Combo box automatically selects the first
"Mike" in the list... rather than the one origionally assigned to the
booking.

Your Staff table needs something to distinguish staff members from each
other. Is there an employee ID? If so, that could be part of each
employee's record. If not EmployeeID, you will need something to uniquely
identify that person. For obvious reasons, the person's name is not
guaranteed to be unique. Once you have that unique identifier, make it the
primary key for the Staff table (do this in table design view).

You can build a query based on the Staff table, including the primary key
field. In a blank column you could put FullName: [LastName] & ", " &
[FirstName]
You could do first name, then last name. It's up to you. Use that query as
the combo box row source. If the query contained only the primary key field
and the constructed (Last, First) field in that order from left to right in
the query design grid, you would specify that the column count is 2, the
column widths are 0"; 1" (or whatever for the second column), and the bound
column is 1. This will bind the unique identifier to the record, while
letting you see the employee's name in the combo box. If the employee's
name changes, all previous records will now be associated with the person's
new name.

If part of the problem is that there are two people with the same first and
last name, you will need to use something else (middle initial, department,
etc.) to tell them apart.

Also, queries seem to duplicate results when the staff name is included.
If
a booking is made on a specific date, the booking will often show up twice
or
three times... once for each occuance of the staff first name.

I can't tell from the description what you are doing. What tables are in
your database, and how are they related? What is the database's purpose?
If you are using lookup fields in your tables, reconsider:
http://www.mvps.org/access/tencommandments.htm

I thought about switching over to a staff #.... however this simply does
not
seem to be a feasible option for those who plan to use the database.

As described above, you can use StaffID numbers to distinguish employees
from each other, while letting the user work with the names.

 

[Guest]

Thanks Bruce

Im trying a few things....

The Staff ID as a Primary Key causes some problems. Even though it is never
seen on a user level.... I have a rediculously complex set of tables &
relationships that track employee availability, hourly by the day of the
week..... relating an auto# primary key accross each of these tables has
proven to be a nightmare... and therefore I have had to attack from a
different approach...

I am going to look into your suggestions and see what I can work out.

Thanks... i may update looking for a little more assistance.... !!

Cheers

Maybe an outside view on this will help me...

A DB I working on has a set of tables and forms that utilize Staff
information. For a variety of reasons I had to separate staff "first" &
"last" names into separate feilds.

A good practice in any case. You can use a query, text box, or code to
combine them for viewing.

I am running into trouble now because I have people with similar first &
last names on staff. When I select the desired staff member on one form..
ie.
Mike Smith, the information saves to a table properly; but when opened on
a
different form the Staff Name Combo box automatically selects the first
"Mike" in the list... rather than the one origionally assigned to the
booking.

Your Staff table needs something to distinguish staff members from each
other. Is there an employee ID? If so, that could be part of each
employee's record. If not EmployeeID, you will need something to uniquely
identify that person. For obvious reasons, the person's name is not
guaranteed to be unique. Once you have that unique identifier, make it the
primary key for the Staff table (do this in table design view).

You can build a query based on the Staff table, including the primary key
field. In a blank column you could put FullName: [LastName] & ", " &
[FirstName]
You could do first name, then last name. It's up to you. Use that query as
the combo box row source. If the query contained only the primary key field
and the constructed (Last, First) field in that order from left to right in
the query design grid, you would specify that the column count is 2, the
column widths are 0"; 1" (or whatever for the second column), and the bound
column is 1. This will bind the unique identifier to the record, while
letting you see the employee's name in the combo box. If the employee's
name changes, all previous records will now be associated with the person's
new name.

If part of the problem is that there are two people with the same first and
last name, you will need to use something else (middle initial, department,
etc.) to tell them apart.

Also, queries seem to duplicate results when the staff name is included.
If
a booking is made on a specific date, the booking will often show up twice
or
three times... once for each occuance of the staff first name.

I can't tell from the description what you are doing. What tables are in
your database, and how are they related? What is the database's purpose?
If you are using lookup fields in your tables, reconsider:
http://www.mvps.org/access/tencommandments.htm

I thought about switching over to a staff #.... however this simply does
not
seem to be a feasible option for those who plan to use the database.

As described above, you can use StaffID numbers to distinguish employees
from each other, while letting the user work with the names.

Any suggestions would be appreciated.
Thanks

 

[BruceM]

You don't need EmployeeID as such, but you do need a unique identifier.
Without that you can't create a sound database. If you have a "ridiculously
complex set of tables and relationships" you may be making things more
difficult than is necessary. Why don't you describe the situation?

Thanks Bruce

Im trying a few things....

The Staff ID as a Primary Key causes some problems. Even though it is
never
seen on a user level.... I have a rediculously complex set of tables &
relationships that track employee availability, hourly by the day of the
week..... relating an auto# primary key accross each of these tables has
proven to be a nightmare... and therefore I have had to attack from a
different approach...

I am going to look into your suggestions and see what I can work out.

Thanks... i may update looking for a little more assistance.... !!

Cheers

Maybe an outside view on this will help me...

A DB I working on has a set of tables and forms that utilize Staff
information. For a variety of reasons I had to separate staff "first" &
"last" names into separate feilds.

A good practice in any case. You can use a query, text box, or code to
combine them for viewing.

I am running into trouble now because I have people with similar first
&
last names on staff. When I select the desired staff member on one
form..
ie.
Mike Smith, the information saves to a table properly; but when opened
on
a
different form the Staff Name Combo box automatically selects the first
"Mike" in the list... rather than the one origionally assigned to the
booking.

Your Staff table needs something to distinguish staff members from each
other. Is there an employee ID? If so, that could be part of each
employee's record. If not EmployeeID, you will need something to
uniquely
identify that person. For obvious reasons, the person's name is not
guaranteed to be unique. Once you have that unique identifier, make it
the
primary key for the Staff table (do this in table design view).

You can build a query based on the Staff table, including the primary key
field. In a blank column you could put FullName: [LastName] & ", " &
[FirstName]
You could do first name, then last name. It's up to you. Use that query
as
the combo box row source. If the query contained only the primary key
field
and the constructed (Last, First) field in that order from left to right
in
the query design grid, you would specify that the column count is 2, the
column widths are 0"; 1" (or whatever for the second column), and the
bound
column is 1. This will bind the unique identifier to the record, while
letting you see the employee's name in the combo box. If the employee's
name changes, all previous records will now be associated with the
person's
new name.

If part of the problem is that there are two people with the same first
and
last name, you will need to use something else (middle initial,
department,
etc.) to tell them apart.

Also, queries seem to duplicate results when the staff name is
included.
If
a booking is made on a specific date, the booking will often show up
twice
or
three times... once for each occuance of the staff first name.

I can't tell from the description what you are doing. What tables are in
your database, and how are they related? What is the database's purpose?
If you are using lookup fields in your tables, reconsider:
http://www.mvps.org/access/tencommandments.htm

I thought about switching over to a staff #.... however this simply
does
not
seem to be a feasible option for those who plan to use the database.

As described above, you can use StaffID numbers to distinguish employees
from each other, while letting the user work with the names.

Any suggestions would be appreciated.
Thanks

 

[Guest]

Well... the situation is this.

Employee contact info is stored on one table...
I have a 2 field Primary Key (First & Last) names.

Their availability is stored on 7 different tables... 1 per day of the week.
Each day is broken down into hours. Ranging from 9:00 am - 9:00 pm.

A form is used to gather the employee's contact info, as well as to prompt
them to select thier availability for each day of the week.

There are multiple queries that run searches to locate which staff are
available on which hrs, and which days

As such, each table must be related between staff names, days of the week,
as well as to the availability options they have to choose from.

If I add an employee ID auto#, the form that accepts availability no longer
works. This is because each of the daily tables would require the staff ID
Auto #.... I have however run into some problems whenever I add the staff ID
# to these daily availability tables.

I am sure this sound more than confusing.

You don't need EmployeeID as such, but you do need a unique identifier.
Without that you can't create a sound database. If you have a "ridiculously
complex set of tables and relationships" you may be making things more
difficult than is necessary. Why don't you describe the situation?

Thanks Bruce

Im trying a few things....

The Staff ID as a Primary Key causes some problems. Even though it is
never
seen on a user level.... I have a rediculously complex set of tables &
relationships that track employee availability, hourly by the day of the
week..... relating an auto# primary key accross each of these tables has
proven to be a nightmare... and therefore I have had to attack from a
different approach...

I am going to look into your suggestions and see what I can work out.

Thanks... i may update looking for a little more assistance.... !!

Cheers

Maybe an outside view on this will help me...

A DB I working on has a set of tables and forms that utilize Staff
information. For a variety of reasons I had to separate staff "first" &
"last" names into separate feilds.

A good practice in any case. You can use a query, text box, or code to
combine them for viewing.

I am running into trouble now because I have people with similar first
&
last names on staff. When I select the desired staff member on one
form..
ie.
Mike Smith, the information saves to a table properly; but when opened
on
a
different form the Staff Name Combo box automatically selects the first
"Mike" in the list... rather than the one origionally assigned to the
booking.

Your Staff table needs something to distinguish staff members from each
other. Is there an employee ID? If so, that could be part of each
employee's record. If not EmployeeID, you will need something to
uniquely
identify that person. For obvious reasons, the person's name is not
guaranteed to be unique. Once you have that unique identifier, make it
the
primary key for the Staff table (do this in table design view).

You can build a query based on the Staff table, including the primary key
field. In a blank column you could put FullName: [LastName] & ", " &
[FirstName]
You could do first name, then last name. It's up to you. Use that query
as
the combo box row source. If the query contained only the primary key
field
and the constructed (Last, First) field in that order from left to right
in
the query design grid, you would specify that the column count is 2, the
column widths are 0"; 1" (or whatever for the second column), and the
bound
column is 1. This will bind the unique identifier to the record, while
letting you see the employee's name in the combo box. If the employee's
name changes, all previous records will now be associated with the
person's
new name.

If part of the problem is that there are two people with the same first
and
last name, you will need to use something else (middle initial,
department,
etc.) to tell them apart.

Also, queries seem to duplicate results when the staff name is
included.
If
a booking is made on a specific date, the booking will often show up
twice
or
three times... once for each occuance of the staff first name.

I can't tell from the description what you are doing. What tables are in
your database, and how are they related? What is the database's purpose?
If you are using lookup fields in your tables, reconsider:
http://www.mvps.org/access/tencommandments.htm

I thought about switching over to a staff #.... however this simply
does
not
seem to be a feasible option for those who plan to use the database.

As described above, you can use StaffID numbers to distinguish employees
from each other, while letting the user work with the names.

Any suggestions would be appreciated.
Thanks

 

[BruceM]

Well... the situation is this.

Employee contact info is stored on one table...
I have a 2 field Primary Key (First & Last) names.

You have to hope there is never a duplicate name. I am not very familiar
with composite keys, but if you have a composite primary key in the parent
table then you need corresponding fields in the child table. I have not
worked much with cascading updates of related fields (a Relationships
option), but I believe that would need to be set in case somebody's name
changes; otherwise old records would no longer be associated with the new
name. You would do better, in my view, to use the employee ID or some other
unchangeable number as the primary key.

Their availability is stored on 7 different tables... 1 per day of the
week.
Each day is broken down into hours. Ranging from 9:00 am - 9:00 pm.

You should have one table for availability. Do employees have several
blocks of availability each day, or just one? I will assume you do not need
to keep track of availability for past weeks, but if you do, no big deal.
There are several approaches here. One option is to have an Availability
table. You would probably have the most flexibility if you have a different
record for each day of the week, but if there is only one time block per day
you could use seven fields in a single record. Employees could delete the
old data when they enter the information for a new week, or you could save
the records for weeks past. How you proceed depends on the details. There
are too many possible nuances to cover all of them here.

A form is used to gather the employee's contact info, as well as to prompt
them to select thier availability for each day of the week.

There are multiple queries that run searches to locate which staff are
available on which hrs, and which days

As such, each table must be related between staff names, days of the week,
as well as to the availability options they have to choose from.

If I add an employee ID auto#, the form that accepts availability no
longer
works. This is because each of the daily tables would require the staff ID
Auto #.... I have however run into some problems whenever I add the staff
ID
# to these daily availability tables.

You don't need employeeID to be autonumber. If you already have employee ID
numbers (for payroll, for instance) you could use those in the employees
table. Best if you don't use SS numbers, by the way, as there are privacy
concerns associated with their use. You probably know that, but I offer it
as a sort of standard disclaimer. If you use an autonumber it is something
that only the database uses. It should not be visible to the users, let
alone something with which they will work.

You could have something like the following for your tables:

tblEmployees
EmployeeID (primary key, or PK)
FirstName
LastName
(and other personal information)

tblAvailability
AvailabilityID (PK)
EmployeeID (FK)
Date
Time
etc.

If you use a composite primary key you will need corresponding fields as
foreign keys in the other table. If they are text fields in tblEmployees
make sure they are text fields in tblAvailability. Remember that the PK is
defined in table design view, but the FK is just a field that is related to
a PK field. You don't define a FK as you would a PK.

Click Tools > Relationships. Add both tables to the relationships window.
Drag EmployeeID from one table on top of EmployeeID in the other table.
Click Enforce Referential Integrity. Create a form from each table (or from
a query based on the table, if you would like to sort the records, or
combine first and last names, or things like that). In form design view
drag the form based on tblAvailability (frmAvailability) onto the form based
on tblEmployees (frmEmployees). Arrange the fields to your liking. You may
want to set the default view of frmAvailability to Continuous, allowing you
to see multiple records. Now when you go to an employee's record you can
see information about their availability, neatly arranged on the subform.

I am sure this sound more than confusing.

It's not that bad. You are making it harder than it needs to be.

You don't need EmployeeID as such, but you do need a unique identifier.
Without that you can't create a sound database. If you have a
"ridiculously
complex set of tables and relationships" you may be making things more
difficult than is necessary. Why don't you describe the situation?

Thanks Bruce

Im trying a few things....

The Staff ID as a Primary Key causes some problems. Even though it is
never
seen on a user level.... I have a rediculously complex set of tables &
relationships that track employee availability, hourly by the day of
the
week..... relating an auto# primary key accross each of these tables
has
proven to be a nightmare... and therefore I have had to attack from a
different approach...

I am going to look into your suggestions and see what I can work out.

Thanks... i may update looking for a little more assistance.... !!

Cheers

:


message
Maybe an outside view on this will help me...

A DB I working on has a set of tables and forms that utilize Staff
information. For a variety of reasons I had to separate staff
"first" &
"last" names into separate feilds.

A good practice in any case. You can use a query, text box, or code
to
combine them for viewing.

I am running into trouble now because I have people with similar
first
&
last names on staff. When I select the desired staff member on one
form..
ie.
Mike Smith, the information saves to a table properly; but when
opened
on
a
different form the Staff Name Combo box automatically selects the
first
"Mike" in the list... rather than the one origionally assigned to
the
booking.

Your Staff table needs something to distinguish staff members from
each
other. Is there an employee ID? If so, that could be part of each
employee's record. If not EmployeeID, you will need something to
uniquely
identify that person. For obvious reasons, the person's name is not
guaranteed to be unique. Once you have that unique identifier, make
it
the
primary key for the Staff table (do this in table design view).

You can build a query based on the Staff table, including the primary
key
field. In a blank column you could put FullName: [LastName] & ", " &
[FirstName]
You could do first name, then last name. It's up to you. Use that
query
as
the combo box row source. If the query contained only the primary key
field
and the constructed (Last, First) field in that order from left to
right
in
the query design grid, you would specify that the column count is 2,
the
column widths are 0"; 1" (or whatever for the second column), and the
bound
column is 1. This will bind the unique identifier to the record,
while
letting you see the employee's name in the combo box. If the
employee's
name changes, all previous records will now be associated with the
person's
new name.

If part of the problem is that there are two people with the same
first
and
last name, you will need to use something else (middle initial,
department,
etc.) to tell them apart.

Also, queries seem to duplicate results when the staff name is
included.
If
a booking is made on a specific date, the booking will often show up
twice
or
three times... once for each occuance of the staff first name.

I can't tell from the description what you are doing. What tables are
in
your database, and how are they related? What is the database's
purpose?
If you are using lookup fields in your tables, reconsider:
http://www.mvps.org/access/tencommandments.htm

I thought about switching over to a staff #.... however this simply
does
not
seem to be a feasible option for those who plan to use the database.

As described above, you can use StaffID numbers to distinguish
employees
from each other, while letting the user work with the names.

Any suggestions would be appreciated.
Thanks