Seemingly simple problem, causing BIG problems

[Trial & Error]

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.

 

[Trial & Error]

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