Query similar records in multiple tables 1

[skcrival]

We are consolidating multiple reports from outside vendors to evaluate our positions against industry standards. I'm having trouble matching position titles across the various reports to summarize the data.

Example:
Report A names a position "Business Development Rep"
Report B refers to the same position as "Business Development Representative"
Report C refers to it as "Business Development Specialist"

All 3 reports are in separate tables. Query should match records whose say the first “X” letters are the same. I’ve tried various criteria parameters, wildcards etc, but being in separate tables is where I’m having the difficulty.

[Report A]![Position] Like [Report B]![«Expr» Left («stringexpr», «n») «Expr» [Position]]

Suggestions?

 

[Remou]

Is it possible for you to set up a table that gives the correct title?

tblTitles
ReportTitle eg Business Development Representative
AKA eg Business Development Specialist

It should be possible to set up the table quite quickly and it could then be joined to the relevant report tables to give consistent titles across the three reports.

 

[skcrival]

That's the goal we're trying to get to, yes - whereas the new table consolidates the data from the 3 reports with one title for each position. (The reports have over 5,500 position titles listed.)

Second step is to use that table to compare industry data (salaries, vacation, job level and so-forth)

e.g.

Business Development Specialist (or whatever the decided name is)

Job Level
Report A Response
Report B Response
Report C Response

Salary Range
Report A Response
Report B Response
Report C Response

Paid Holidays
Report A Response
Report B Response
Report C Response

 

[Remou]

I do not think I explained what I mean clearly. Let us say that you have a table ReportA with a field Position then:

SELECT DISTINCT Position, "" As NewPosition
INTO tblPositions FROM ReportA

Will give you a list of unique positions in ReportA and a field NewPosition to be completed. I do not think you have 5,500 unique positions?

This can be done for each report. Finally:

SELECT p.NewPosition, r.Blah FROM tblPosition p
INNER JOIN ( SELECT Blah FROM ReportA
             UNION ALL
             SELECT Blah FROM ReportB
             UNION ALL
             SELECT Blah FROM ReportC ) r
ON p.Position = r.Position

Or there abouts. Is this what you had in mind?

 

[skcrival]

Let's say the below example is from Report A, which shows 5 unique positions in the eyes of the Dept of Labor. Same job really but responsibilities/duties do vary from one level to the next.

Accounting Associate I
Accounting Associate II
Accounting Associate III
Accounting Associate IV
Accounting Associate V

Report B (different table) shows the same position but calls it:

Accounting Clerk I
Accounting Clerk II
Accounting Clerk III
Accounting Clerk IV
Accounting Clerk V

Report C, in yet another table, shows the same position but it’s called:

Accounting Rep I
Accounting Rep II
Accounting Rep III
Accounting Rep IV
Accounting Rep V

A matched query (INNER JOIN) would not return any results as there are no records from all 3 tables that are equal.

The desired outcome is for the query to only return results where each record, across all 3 tables, match say the first 5 characters (or however many the business feels is needed for optimum results).

Does that help, I hope? It is confusing I know. I really do appreciate your assistance Remou!

 

[Remou]

Yes, I think I got that in the first place, however, I do not believe that a match on a certain number of characters is not going to work in this situation. I am not sure that you get what I mean about creating a linking table that will perform this function. in the above example, the linking table would have the following records:

[tt]Position NewPosition
Accounting Associate I Accounting I
Accounting Associate II Accounting II
Accounting Associate III Accounting III
Accounting Associate IV Accounting IV
Accounting Associate V Accounting V
Accounting Clerk I Accounting I
Accounting Clerk II Accounting II
Accounting Clerk III Accounting III
Accounting Clerk IV Accounting IV
Accounting Clerk V Accounting V
Accounting Rep I Accounting I
Accounting Rep II Accounting II
Accounting Rep III Accounting III
Accounting Rep IV Accounting IV
Accounting Rep V Accounting V[/tt]

 

[skcrival]

I tried the first piece of code and it worked perfectly-thank you!

SELECT DISTINCT Position, "" As NewPosition
INTO tblPositions FROM ReportA

The second part confused me. What should go in place of Blah? Also, is the INNER JOIN joining the new tblPositions for each report that we just created with the above code?

SELECT p.NewPosition, r.Blah FROM tblPosition p
INNER JOIN ( SELECT Blah FROM ReportA
             UNION ALL
             SELECT Blah FROM ReportB
             UNION ALL
             SELECT Blah FROM ReportC ) r
ON p.Position = r.Position

 

[Remou]

Blah is the fields that you wish to select. Separate field names with commas. The SQL includes union query, which should add all the report tables to each other to create one large table, but only as a query, not permanently. The Inner Join then selects records from the two tables (the imaginary union table and the position table) where the position matches. It is late, I hope you can get something out of that explanation

 

[skcrival]

How does the SQL add all the report tables to each other to create one large table?
Wouldn't I need to adjust the code to something like:

SELECT p.NewPosition, r.Position FROM tblPosition A B & C p
INNER JOIN ( SELECT Position FROM TableA
             UNION ALL
             SELECT Position FROM TableB
             UNION ALL
             SELECT Position FROM TableC ) r
ON p.Position = r.Position

 

[Remou]

Nearly. The alias letters (p, r) need to come after the table name or in the format <table> As <alias>.

SELECT p.NewPosition, r.Position FROM tblPosition p
INNER JOIN ( SELECT Position FROM TableA
             UNION ALL
             SELECT Position FROM TableB
             UNION ALL
             SELECT Position FROM TableC ) r
ON p.Position = r.Position

 

[skcrival]

I massaged the code and tried this:

SELECT p.NewPosition, r.Position FROM tblPositions, FROM tblPositions2, FROM tblPositions3 p
INNER JOIN ( SELECT Position FROM TableA
             UNION ALL
             SELECT Position FROM TableB
             UNION ALL
             SELECT Position FROM TableC ) r
ON p.Position = r.Position

Code returns a syntax error. I'm just not sure how to fix it

 

[Remou]

What are the names of your three report tables? What are the fields of your three report tables? Did you create a table called tblPositions with the suggested fields?

 

[skcrival]

The original three table names are:
ECI
ECF
ECP

There are many fields in the three reports but the field containing the job title is "Position" across all three reports. I created three separate queries, SQL as follows:

SELECT DISTINCT Position, "" As NewPosition 
INTO tblPositions FROM ECI

SELECT DISTINCT Position, "" As NewPosition 
INTO tblPositions2 FROM ECF

SELECT DISTINCT Position, "" As NewPosition 
INTO tblPositions3 FROM ECP

 

[Remou]

So, that is a little more complicated. You need just one positions table (backup everything first, of course ).

To create one table from the three tables:

INSERT INTO tblPositions ( Position, NewPosition )
SELECT p2.Position, p2.NewPosition 
FROM tblPosition p RIGHT JOIN tblPosition2 p2 
ON p.Position = p2.Position
WHERE p.Position Is Null

INSERT INTO tblPositions ( Position, NewPosition )
SELECT p3.Position, p3.NewPosition 
FROM tblPosition p RIGHT JOIN tblPosition3 p3 
ON p.Position = p3.Position
WHERE p.Position Is Null

To view the three report tables against the single positions table:

SELECT p.NewPosition, r.Position FROM tblPosition p
INNER JOIN ( SELECT Position FROM EC1
             UNION ALL
             SELECT Position FROM ECF
             UNION ALL
             SELECT Position FROM ECP ) r
ON p.Position = r.Position

Is that clearer?

 

[skcrival]

Woo hoo! Thanks to your help I was able to join the three tables together, which gives me a total of 423 unique titles.

I tried to view the three report tables against the single positions table using:

SELECT p.NewPosition, r.Position FROM tblPositions p
INNER JOIN ( SELECT Position FROM ECI
             UNION ALL
             SELECT Position FROM ECF
             UNION ALL
             SELECT Position FROM ECP ) r
ON p.Position = r.Position

I get a listing of 9,113 positions. All in the "Position" column, and "NewPosition" is all blank.

 

[skcrival]

- EDIT -

My apologies, I forgot to change Unique Values to "yes". I changed it which it drop to 423 records.

I'm still don't understand why "NewPosition" is blank, but you have been a tremendous help to me and I want to thank you for all your time.
It is people like you who make a difference-thanks!

 

[Remou]

Did you fill in new titles with the titles as you want them to appear (post dated 28 Nov 08 19:05 )? However, you should find that you are now a step on the way to comparing your data. You should also be able to Group By (sigma on the menu bar).