Join Tables

[Eric Harrington]

I have two tables "CANDIDATES" and "JOBS" Each shares 3 fields, "Title", Department" and "Area".

I want to create a List or Table view layout that shows ONLY the matches, ie combinations of one record in each table where all 3 fields match.

Is this done with a "join table" and how? I tried just creating a separate "Match" table with the fields realated but I don't know how to populate it.

[comment]

Suppose there are 4 candidates:

Adam

Betty

Cecil

David

and 4 jobs:

Job A

Job B

Job C

Job D

Adam matches Job A and Job B.

Betty matches Job B and Job C.

Cecil matches Job C.

What should the result look like?

[Eric Harrington]

Each match, (ie candidates 3 fields Title, Dept and Area match the 2 fields for the "Job" (Title and Dept) and the field Area from the related parent table "Company") would be listed as a separate record in a list. Ideally I would love it to be a Portal in the "Company" and "Candidate" layouts.

So it would read:

Cand Table|Matched fields|Jobs Table

Cand Name |Title/Dept/Area|Company| Job Descript

ie.

ADAM|DIRECTOR|SURGERY|SW LA| UCLA | "Dir Surgey..

for each match listed out.

Edited August 26, 2008 by Guest

[comment]

I am sorry, that doesn't answer my question. Please use the data provided in the example and show what the ENTIRE report should look like. The real question here is how many rows are there, and what's their order.

If you want to keep this simple, you can produce a report from either one of your tables, with a portal to the other table. Otherwise you'll need to import records into a temp table before you can produce your report.

[Eric Harrington]

In your example the end list would be 5 lines, (one for each match) and the order doesn't really matter but ideqally they would be sorted by the match date.

[Eric Harrington]

Adam A

Adam B

Betty B

Betty C

Cecil C

[Eric Harrington]

BTW I already have the matches shown in each table ie all the jobs that match a single candidate are shown in a portal in the Candidate layout, and all the candidates that match any single job are shown in a portal in the Jobs layout, the problem is I want to show the matches in a different layout, the parent layout of Jobs called "Company"

So I need to show multiple candidates AND multiple jobs matched in one list.

Edited August 27, 2008 by Guest

[comment]

Well, a simple count shows that the Candidates table has 4 records (and only 3 of those are relevant) and the same is true for the Jobs table. Therefore neither table can produce a report with 5 rows. So it comes down to what I said before: either settle for a portal, or produce the required records in a third table.

BTW, a portal can be shown in list view too, and if you set it to slide up it can work reasonably well.

the order doesn't really matter but ideqally they would be sorted by the match date.

What's a match date?

[Eric Harrington]

The date the Candidate and Job had a match. The jobs change continuosly.

Heres the structure in a nut shell. The COMPANY table has all the hospitals I recruit for. The JOBS table is a child (ie the many in a one to many relationship)table of COMPANY where each Hospital has multiple openings each being a new record in JOBS. Also, there is a second child table of COMPANY called STAFF (I called it Candidates in the posts) It has a new record for each staff memebr in the Hospital. Each JOB record and each STAFF record have 3 indentical (ie same drop downs, etc.) fields, 1)Title (exp. "Manager") 2)Department (exp. "Surgery") and 3)Area (the area of the city where the hospital resides, or the STAFF member lives) When the 3 fields opf any STAFF member matches the same three in JOBS, I want to list the match as a record in a table (list view), showing the STAFF members Name, The three matching fields, and the COMPANY (Hospital) Name.

I agree that a portal can't do this. But I want the list to look like a portal or table view layout. I need multiple STAFF records matched to Multiple JOB records all in one portal. Hence my suggestion of a Join or merge or sub table I don't know what to call it, table.. I just don't know exactly how to use this technique. What fields to realate etc. might be too complicated for this forum..

Thanks Much!

Edited August 27, 2008 by Guest

[comment]

it wouldn't work for this because I need multiple STAFF records matched to Multiple JOB records all in one portal.

I am afraid you misunderstood me. If you place a portal to Jobs into the body part of a layout of Staff, then find the Staff records that have (any) related records in Jobs and view them as list, you will see something like:

-------------

Adam

• Job A

• Job B

-------------

Betty

• Job B

• Job C

-------------

Cecil

• Job C

-------------

(The indented lines signify portal rows, the dotted lines are record boundaries.)

If you do the same thing in the opposite direction, you'll get:

-------------

Job A

• Adam

-------------

Job B

• Adam

• Betty

-------------

Job C

• Betty

• Cecil

-------------

If you move the "local" field into the portal, you can get (using the last example)

-------------

Job A | Adam

-------------

Job B | Adam

Job B | Betty

-------------

Job C | Betty

Job C | Cecil

-------------

I still don't get the date concept. A match can be "born" by adding a new Job record, or by adding a new Staff record, or by changing one or more of the three match criteria in any record. This is the crux of the problem - a match is not an event or an entity, it's a coincidence (literally). It's very difficult to record a match when it happens (you would need to run a script after creating a new record and after each modification of one of the match fields).

That's why I think it would be better to create these records ad hoc when a report is required (if you don't want to go with the portal, that is). But then there would be no way of knowing when exactly a match happened. The best you can get here is the maximum of the two creation dates.

[Eric Harrington]

The date isn't important, in fact ordering isn't important, just flagging the match so I can follow up with both sides. I just thought that it could be created as you said when either a new job is entered creating a match somewhere, or a new sfatt memebr is added or one of the match fields is changed. If I was using some kind of a match table, I assume a record would be generated at that moment and a date could be created. But its not important. Just listing the matches.

Your last example is exactly what I want if multiple records of Staff AND jobs that match are shown in the same portal. Is that what you meant? What do you mean about adding the "Local field"

Edited August 27, 2008 by Guest

[comment]

Like this?

MatchJobs.fp7.zip

[Eric Harrington]

No...

See attached excel. This is what the final Output should liook like... Your example has overlap in each successive match fields so they all end up with the same Title, Dept and Area in my example...

FMP_Example.zip

Edited August 27, 2008 by Guest

[bruceR]

I have two tables "CANDIDATES" and "JOBS" Each shares 3 fields, "Title", Department" and "Area".

I want to create a List or Table view layout that shows ONLY the matches, ie combinations of one record in each table where all 3 fields match.

Is this done with a "join table" and how?

Join table? Yes. See attached.

matchJobs.zip

[comment]

Your example has overlap in each successive match fields so they all end up with the same Title, Dept and Area

I don't see where it happens. Note that my example may be a little misleading: in your described setup, you could never have a situation where Job B fits both Adam and Betty, but Job A fits Adam only. I had to give them double criteria to make this happen.

See the modified file, where I have added the criteria fields from both tables and stretched the portal to show them all.

MatchJobs.fp7.zip

[Eric Harrington]

I REALLY appreciate all your help with this but were not getting anywhere and I don't want to waste your time...

Again, my need is to show MULTIPLE Staff records and Multiple matching Jobs records in ONE list view. The only way I can see is to merge them into a separate match table with one record per unique combination of Staff member and job that shares the 3 fields.

Thank you very much Again!

Eric

[comment]

We aren't getting anywhere because you don't say what's wrong with the proposed portal solution.

In any case, the alternatives have already been mentioned: you will either need to import the records into a temp table before producing your report, or generate/delete join records on every modification of one of the two main tables.