Stuck setting up a tricky relationship

[Rich S]

Greets, all:

Please refer to the attached files. I'm trying to set up a report based on the data from two different tables, Job and Resume; the report is in the layout, Printout (also seen in the attached screenshot.) I just can't get the relationship right for it to work. :S The current solution doesn't have the join table, Job-Resume--I just added that in to see if that's the path I should take or not to obtain a one-to-many relationship between the tables...if that's the ticket. The best I could manage so far has been either using Resume as the "parent" table but all I get is one company name, not all, that used a specific ResumeName. Or, I'd get results if I based it on the Job table but I didn't have any control over which ResumeNames I want to include in the report.

The problem is when trying to create this report I want it based on the found set in Resume since it's from there I'll decide which ResumeName(s) I want in the report.

As you can see from the screenshot, I want a list of résumé names, created and last modified data--all entered in the Resume table--with company names--all entered in the Job table--summarized underneath. In Job, many of the records don't have a résumé name associated with them or the résumé name is of a Resume record that no longer exists--which is fine--so relating by that field won't work. Maybe relating by ResumeName, will? I don't know.

Criteria for the report:

I want to print a list where for a specific résumé name it lists those CompanyName(s) that used that ResumeName, (e.g., Main.)

I want to see all the company names (in Job) that used the first letter of ResumeName (e.g., M.) So, if the ResumeName is Main, Machinists, Marketing, etc., their "related" companies would be sub-summarized accordingly.

I want to be able to list all the Resumes Names with their associated company names.

All this would be sorted, of course, ascending order. (Sorting's the easy part!)

If I'm unclear I'll be happy to elaborate--I'm running on only two hours of sleep last night so I'm foggy this morning. Thanks!

Company Resume.fp7.zip

[comment]

Can you explain what this is about in real life?

[Rich S]

At the school where I work, the Career Counselor wants students to be able to create different résumés so they can tailor each to a specific employer when applying for a job...as everyone should when they job hunt; they're doing that in MS-Word but will record their names in the FMP solution I'm trying to build for her.

She also wants to be able to track which employer received "x" résumé, hence the reason for the list.

I uploaded a bare-bones file--there's quite a number of Jobs and Resume-related fields so it's important that they have their own tables.

[comment]

the Career Counselor wants students to be able to create different résumés so they can tailor each to a specific employer when applying for a job.

So it would seem that each résumé belongs to one student and targets one specific job?

She also wants to be able to track which employer received "x" résumé, hence the reason for the list.

That's not clear: what does "x" stand for?

[Rich S]

>>So it would seem that each résumé belongs to one student and targets one specific job?<<

More correctly, one résumé (name) -> many company names.

>>That's not clear: what does "x" stand for?<<

"X" equals résumé (name)--sorry about the missing part.

[comment]

Sorry, I keep losing you. If a student "tailors each résumé to a specific employer when applying for a job", why is the same résumé related to many companies?

[Rich S]

No apology needed--it is I who should apologize to you for taking up your valuable time by not being clear. Sorry. I really appreciate you hanging in there.

The problem is that a specific résumé may be sent to one company for one position--which is encouraged. However, the résumé can be sent to many companies if it isn't edited/changed by the student because she or he feels the position's requirements are so close to another company's that it makes sense to reuse an existing résumé. Not only that, a student may apply for a position at a company with differing résumés for different positions that are available.

So, now you can see my conundrum: you can have one résumé to many companies and many résumés to one company. Alternatively, you can have many companies to one résumé and one company can receive many résumés; all this per student.

[comment]

So how about:

Students -< Résumés -< Submissions >- Positions >- Companies

[Rich S]

I'll give that a shot. Thank you VERY much!