Employee Query Naming Convention

[depawl]

OK, I have a query that pulls data from 2 tables. The first table (let’s call it Table A) has employee information (employee name, job title, ID#, etc.). The second table (Table B) has information on training courses that employees have taken.
So my query pulls the latest employee information from Table A and matches with training records from Table B. If a new employee comes onboard he/she appears in the query with no training records. The issue I’m having is that in the design of Table A (done years ago by others) the “Employee Name” field includes the employee’s ID# (for example: “John Doe – 56789”).
And when an employee gets promoted or moves to another division in the agency, his/her employee ID# changes (agency policy). So when I run my query, if an employee has been promoted, access recognizes him/her as a new employee and the query returns the employee’s name with no training records. What I need is to somehow query only “name” portion of the Employee Name field so that the query will return the correct training records for the employee.
Thank you in advance.

 

[GinaWhipp]

Hmm, might be time to fix that Table. Just add another field to store their ID and problem solved! You can then go thru and concatenate where both name and ID are needed.

 

[plog]

Time to properly set up your databases. You need to add an autonumber primary key (https://support.office.com/en-us/ar...mary-key-07b4a84b-0063-4d56-8b00-65f2975e4379) to Table A. And then you need a numeric field in Table B to store that data instead of employee name.

I recommend other changes as well. Every distinct piece of data should be in its own field. So instead of an employee name field that contains first name, last and employee #, you should have 3 different fields for each of those pieces of information.

 

[depawl]

Thank you for your responses, but those tables have been developed by others in my agency and changing them is out of the question. So in a nutshell I need to work with what I have.

 

[jdraw]

I recommend you get a screen capture(s) of your table designs and your relationships window --zip the files - then attach the zip.
As others have said -it appears you have a structure/design problem, but until readers see what you really have, it is difficult to give a focused response.

 

[CJ_London]

all very well to query on the name part (it's not that difficult) but what guaranties do you have that

a) John Doe is not misspelt the next time e.g. John Do, Jhon Doe?
b) that an employee does not change their name (e.g. on marriage)
c) that you do not have more than one person with the same name

Any of the above will cause major issues

 

[namliam]

Also a name only wont guarantee uniqueness...
While the design isnt optimal because the employee ID is part of your name field, it will still be unique and serve as PK, an iffy PK, but a PK non-the-less.

I would suggest adding a table to your database that will let you hold "translations" where you map on Name + Employeenumber to another... Yes this needs to be done manually to Ensure the match is done properly, unless you have other additional information such as Date of birth.

If you want to "extract" the name only from your field, I suggest you have a look at the functions InstrRev and Left and Right assuming all fields adhere to the naming convention using those 3 (or really just 2) should allow you to extract the name only.