complex (for me) QUERY HELP

[Trope]

In theory, this seemed easy to me. WHen I tried to SELECT it... different story.


I have 4 tables related to this question. I am interested in retrieving all related records from all four tables in a Select.

This is a recruiting site I am doing. The page I am having trouble with is the page that tells the employer who has applied

for each of their particular jobs.

This is the output I am hoping to achieve on my page:

'----- Output desired--------------------------
' Welcome, BigShot EMPLOYER
'
' The below is a summary of your job activity:
'
'
' JOHNNY EMPLOYEE applied for CASHIER
' with cover letter MY MAIN LETTER
' and resume MY First RESUME.
'---------------------------------------------



All we know coming into this query is the PEOPLE_ID ( which is who is logged in, in this case the EMPLOYER )

Notice that in the EMPLOYERAPPS table I refer to Applicant_ID (which really is the applicant's people_ID ).




'----------- ACCESS TABLE STRUCTURE BELOW -------------------

' PRIMARY KEY: people_id
|----------- |
|tbl_people |
|--------------------------------------------------|
| people_id | people_firstName | people_lastName |
|--------------------------------------------------|
| 6 | Johnny | EMPLOYEE |
|--------------------------------------------------|
| 99 | BigShot | EMPLOYER |
|--------------------------------------------------|


' PRIMARY KEY: resume_id
|------------|
|tbl_resumes |
|--------------------------------------------|
|resume_id | people_id | resume_name |
|--------------------------------------------|
| 2 | 6 | My First Resume |
|--------------------------------------------|


' PRIMARY KEY: ID
|-----------------|
|tbl_coverLetters |
|-------------------------------------------------------|
| ID | people_id | coverLetterName | CoverLetterText |
|-------------------------------------------------------|
| 3 | 6 | My Main Letter | Dear Sir... |
|-------------------------------------------------------|


' PRIMARY KEY: ID
|-------------|
|currentJobs |
|-----------------------------------------------|
| ID | people_id | job_title | job_description |
|-----------------------------------------------|
| 5 | 99 | cashier | Great Job! |
|-----------------------------------------------|


' PRIMARY KEY: ID
|-------------|
|employerApps |
|-----------------------------------------|
| ID | people_id | applicant_id | job_id |
|-----------------------------------------|
| 5 | 99 | 6 | 5 |
|-----------------------------------------|


IS this even possible? It seems a bit complex to ME, but maybe one of you can do this???

Thanks!
Giovanni