Multiple criteria - same concept, different names (name of employees - different formats)

[rayted]

Hi guys,

Unsure where I should have posted this.



Can anyone tell me what I need to search for/attempt on MS Access 2010.

I have employee codes of employees who send emails from 3 different systems. I want to merge these into 1 system.

However, there are different employee codes for each system i.e. John Smith, JohnS, John etc. I want to know if I need some sort of IF query or a table? I don't know!

Can someone please tell me how I can merge the data to indicate the employee codes should be grouped?... or regarded as 'Jonny' or whatever...

help would be much appreciated.

Thanks,

Ray

[CJ_London]

nowhere near enough examples to see what might work but looks like you need to do some sort of string comparison

e.g. myfield like 'john*'

will find all of your examples, but will also find JohnT, John Jones etc

[CJ_London]

another thought - do you have their actual email address? if so, perhaps you can match on that?

[orange]

What is the root issue? To identify Employees uniquely or to identify and de-duplicate/reconcile email addresses or something else??

Some additional info and examples would help clarify your requirements.

[rayted]

Hi all,

In blunt terms:

System A (named GUI) - I can query who/the number of emails sent to customers by employees
System B (named IS system) - I can query who/the number of emails sent to customers by employees
System C (named PIS system) - I can query who/the number of emails sent to customers by employees

However, rather than doing the access query 3 times, I want to merge all of the queries together. However (this is where the problem is), the employee codes are different on each of the 3 aforementioned systems. Therefore, I am thinking (I might be completely wrong) that I need to create a table with employee codes/names and then link all 3 of the systems' employee codes and a primary field to a master table?!

Thanks for your responses/attempts to help so far!!!

[orange]

What if John Doe is JDoe@email.net in GUI, and BlackCat@free.net in IS and TonyZ@gmail.com in PIS?

What is the reality of this sort of thing? And what do you want as the answer?

[rayted]

What if John Doe is JDoe@email.net in GUI, and BlackCat@free.net in IS and TonyZ@gmail.com in PIS?

What is the reality of this sort of thing? And what do you want as the answer?

OK.. so in short I currently run 3 access queries for each system, to see how many emails were sent for employees

GUI says: JohnS 120 (sent 120 emails)
IS says: John Smith 15 (sent 15 emails)
PIS says: John 11 (sent 11 emails)

I want to merge these 3 queries into 1. However, the difficulty is the 3 systems (GUI, IS, PIS are all connected to different databases and have different employee codes - like JohnS, John Smith and John - all for the same employee just different versions).

Thanks

[CJ_London]

what is being suggested is that rather than using the employee code, you use the employee email address

[rayted]

Unfortunately this is not part of the databases on the systems.... employee code will be the only unique distinction on the databases for me to identify the employees

[orange]

Please tell us what you mean with

..employee code will be the only unique distinction...

Many organizations in their attempt to reconcile duplicate code(silo systems) recognize that having an authoritative table for each of their key entities --personnel, materiel, projects, products .... is key to reducing waste and duplication. Have you considered an Employee table that spans the various systems?

[CJ_London]

since there is no logic in the names in each system, other that they will be unique within each system (but still not been clarified if they are unique across all system), I think the only way you can do this is to have an employee alias table

assuming you have a table
tblEmployees
EmployeePK autonumber
...
...

then have another table
tblAliases
AliasPK autonumber
EmployeeFK long
Alias text
AliasSystem text


How you populate it is down to you. Your examples are very limited so no idea what to suggest. Perhaps populate the alias table without the employeeFK then by sorting/filtering you can start to match things off - tho' how you would decide that JohnS was the same person as John Smith or John Sallis, I've no idea

[rayted]

Not sure I quite follow you Ajax - what is PK and FK?

orange - the 3 systems are different in terms of employee codes format and this cannot be changed.

Let me elaborate on this:

GUI: Ray Ting - employee code: RTI (made up of initials)
Ajax Orange - employee code: AOR
John Smith - employee code: JSM

On a 2nd system the employee code is different.. i.e.
Ray Ting - employee code: Ray Ting
Ajax Orange - employee code: Ajax Orange
John Smith - employee code: John Smith

I need to run access queries for each system to know who has sent how many systems. However, with 3 systems and 3 different employee codes, I don't know how is best to link these... should I link to a primary table and a primary key?

[CJ_London]

PK=Primary key, FK =Family or Foreign Key