Crystal 2008, matching fields that are not without a link to the tables/fields

I have a report which I need to link multiple tables to fields to pull the data I need.  I am linking general employee data table to a salary table, which holds supervisor names.  The need is to have the next level of data for the supervisor employee data.  I have setup the employee links and additional alias tables for the supervisor general data with left outer join. The thing is the main field  link (employee id) is missing for the supervisor information, so I must link by last and first name fields.  What I am not understanding is some of the data appears and then it skips to the next name.  Not sure why this is happening.  See attached report.
Linking EMGS > EMCS Supervisor Last Name > alias EMGS Last Name and trying to match first name fields.
Supervisor-Sanjo-test.rpt
GrapeladyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
I would try 2 things

1.  Try linking not on the last name but on the SUPERVISOR NUMBER to the EMP NUM

2.  Link on the 3 parts of the name LAST, FIRST and MIDDLE.  You may have records for sveral people with the same last name

mlmcc
0
James0628Commented:
What I am not understanding is some of the data appears and then it skips to the next name.
I'm not sure what you mean by that, but, putting that aside for the moment ...

 I agree with mlmcc's second point.  You said "I must link by last and first name fields", but you're only using the last name in the link, and you have a number of supervisors with the same last name, so your formulas that check for matching values in other fields, like the first name, don't show anything for some records.

 And while you only mentioned using the first and last name fields for the link, you also have a middle name field, which should presumably be included in the link (as mlmcc suggested).

 James
0
GrapeladyAuthor Commented:
Can't link on the employee's supervisor number because the main link (employee level) is missing the supervisor number, so next link moves to the employee's supervisor's last name.  Hence there are some last names that have multiple first name matching, so I need to have a partial match on the employee's supervisor's first name to the supervisor's employee's first name.  Is there a way to do a partial linking to the fields?  By what I mean on skipping names, on the far right side columns, the report is pulling the last name and first name on some of the records, but then does not complete on all the employee records, even though the supervisor last and first name match appears to be working some of the time. baffled...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mlmccCommented:
In the EMCS_CURRENT_SAL_JOB table what is the SUPERVISOR_NUMBER field?
If that is his employee number then you can link to the SUPERVISOR_EMGS_GENERAL... table using it.

Linking on names unless the First, Middle, and Last combination is unique you will have errors in the data on the report.

If Employee 12345 has supervisor John C Johnson and there is another supervisor by that name you will link employee 12345 to 2 supervisors.

The reason it works some of the time but not all is you have a formula for the supervisor's first name that checks if the last names are the same and the first 2 letters of the first name then show the first name otherwise show nothing.

Apparently there are 2 supervisors with the same last name but different first names.

Put the supervisor's first name on the report and you will see there are 2 with the same last name and different first names (report rev1)

Try running the second report.  I linked on all 3 name fields.  See if that makes a difference
This will work until you have 2 supervisors with the same full name

mlmccSupervisor-Sanjo-testrev1.rpt
Supervisor-Sanjo-testrev2-linkonfullname
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James0628Commented:
I think someone is missing something here.  Either we're missing something about the situation, or you're missing something about how to link the tables.

Hence there are some last names that have multiple first name matching, so I need to have a partial match on the employee's supervisor's first name to the supervisor's employee's first name.
Why a "partial match"?  Why not just include the first name in the link?  (And the middle name too)

By what I mean on skipping names, on the far right side columns, the report is pulling the last name and first name on some of the records, but then does not complete on all the employee records, even though the supervisor last and first name match appears to be working some of the time.
Because you're not including the first name in the link, so you're not really doing a "first name match".  You're linking any supervisors with the same last name.  If you have more than one supervisor with the same last name, the first, and middle, names match on some (so your formulas show the first and/or middle name), and they don't match on others (so your formulas show nothing).

 The obvious answer, as we've stated more than once, is to include the first and middle names in the link.  Is there some reason that you think that won't work?

 James

 PS: If any of that came off as "harsh", I really don't mean it that way.  There just seems to be a basic misunderstanding here somewhere, and I'm trying to get past that.
0
GrapeladyAuthor Commented:
I have attached a worksheet and examples on what I am talking about.  Unfortunately the Supervisor number is not an option to match, if so, my life would be sweet.  Not all employee supervisor numbers are populated, but the last and first name fields are populated.  The next option is to match with the Supervisor Last Name.  Unfortunately as I have found that if the last name has more than one employee with different names then I will get  all employees as supervisors with those different names.  If I do a full outer join on Employee Supervisor Last Name to Supervisor Last Name then I receive some with data and then skipping of the data, see attached pdf file, even though the data does not appear to be different on some of the records.  If I join Last name and first name then I only get exact matches and the report excludes non-matches.  I really need to match on last name and partial on first name to eliminate those employees who have same last name but different first names.  I was hoping for a solution...Name-matching.xlsx  Please let me know if this is possible.
Supervisor-FULL-Join-on-Supervisor-Last-
Supervisor-FULL-Join-on-Supervisor-Last-
Supervisor-LEFT-OUTER-JOIN-on-Supervisor
0
mlmccCommented:
Without a change in the database or some control over the data what you are trying to do can't be done.  Apparently the data is entered freehand so anything can be selected.

The best you can do is link on the last name and display the results which will be many wrong records.  Even trying to match the first 2 or 3 letters can result in incorrect records since you could have Janet Jones and Janice Jones.  Even full names could be an issue if you have 2 Janet Jones.

Did you try the reports I uploaded?
If not try the one that links on the 3 name fields.  You can change the joins to LEFT OUTER if you need to.  You could also delete the link on middle names

The problem you have appears to be 2 major issues.
1.  The tables should have primary keys that are used to link them.  The employee able needs a supervisor id (FK) field that is the supervisor's id in the supervisor table.

2.  Names are not entered consistently so one time you get Jim J Jones in 3 fields the next time it is Jim J in one field and Jones in another.

This makes it very difficult to write queries that return the data you desire.

What do you want the report to do?  Perhaps we can figure out a way to get the information you want or something very close.

mlmcc
0
James0628Commented:
OK, if the names aren't entered consistently, then that would be a problem.

 You could go into the record selection formula and add something like

Left({EMCS_CURRENT_SAL_JOB.SUP_FIRST_NAME}, 2) = Left({Supervisor_EMGS_GENERAL_STATUS.FIRST_NAME}, 2)

 Then the report would only include the records where the first 2 letters of the first name matched.  That would eliminate a lot of the extra records, but, as mlmcc said, there's still the potential for false matches (eg. James and Janet).  For that matter, even if the names were entered consistently and you could check first, middle and last names, there's still the chance that two people could have the exact same name.

 FWIW, if the first and middle names are the same, and it's just a question of how they're entered (eg. it's always "Jim" and "J", but they could be entered separately in the First and Middle fields, or together in the First field), then you could combine the First and Middle fields and compare them.  But, even if that worked, there's still the possibility of different people having the same name.

 James
0
mlmccCommented:
The only problem with using the select expert to filter is the filter may not get passed to the database.  That just means more data is coming to the report but in this case it may be minimal and probably can't be avoided in any case.

mlmcc
0
James0628Commented:
The only problem with using the select expert to filter is the filter may not get passed to the database.
You're right, of course, but it would be a lot better (simpler) than trying to use formulas to show/hide the fields in the extra records.  This is assuming, of course, that matching on the first 2 letters of the first name doesn't exclude some records that it shouldn't (like someone that only had their first initial in one table, instead of their first name).

 If the test doesn't get passed to the server and there is a lot of data, then creating a query would be the obvious solution, so that you could make sure that the test is done on the server, but there's still the underlying problem of trying to match inconsistent fields.

 James
0
GrapeladyAuthor Commented:
Hello mlmcc and James0628, I worked out a solution using your suggestions and ideas.  The field formulas  became a combination of possibilities and matchings with combining the EMSH supervisor names with the EMGS supervisor employee's names, even including an exception of one employee who had same last name, first name but a different middle initial.  Thank you for your suggestions, they were helpful and led me down the road to success.
0
James0628Commented:
I'm glad you were able to come up with a solution.  Non-unique and inconsistent fields like that are going to be a pain to deal with.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.