Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Fetch info from two tables 3

Status
Not open for further replies.

dreampolice

Technical User
Dec 20, 2006
85
US
I have two Access 2003 tables:

TableMain
Code:
MID  PO   TE   INFO
1    2    3    ABC
2    2    1    EREER
3    1    3    OIUOI
4    3    3    PERE
5    2    2    DFE

TablePerson
Code:
PID   FIRSTNAME   LASTNAME
1     Joe         Smith
2     Cal         Jones
3     Bill        Carson

I would like to get this output if I query for a MID number such as 3:
Code:
Firstname  Lastname   Firstname2 Lastname2  Info
Joe        Smith      Bill       Carson     OIUOI

My attempt below only fetches one name and ignores the TE value.
Code:
select * from TablePerson INNER JOIN TableMain on
TablePerson.PID = TableMain.PO
where TableMain.MID = 3


Another example if I query for MID number 5 I should get this:
Code:
Firstname  Lastname   Firstname2 Lastname2  Info
Cal        Jones      Cal        Jones      DFE


Please advise
 
Do you mean:

Code:
SELECT TableMain.MID, TablePerson.PID, TablePerson.FIRSTNAME, TablePerson.LASTNAME, TablePerson_1.FIRSTNAME, TablePerson_1.LASTNAME, TableMain.INFO
FROM TablePerson INNER JOIN (TableMain INNER JOIN TablePerson AS TablePerson_1 ON TableMain.TE = TablePerson_1.PID) ON TablePerson.PID = TableMain.PO
WHERE TableMain.MID=3
 
Thanks it works great!

I was wondering if I have a third (and final) table called NotesTable how would I include that in the query??

For example if I query for MID number 3, I should get this:
Code:
Firstname Lastname Firstname2 Lastname2 Info   Notes      
Joe       Smith    Bill       Carson    OIUOI  other data

Here is NotesTable where Notes field is sometimes null and MID is a Foreign key:
Code:
NotesID  MID   Notes 
1        1    
2        3     other data 
3        2    
4        5     miscl notes here
 
You could use:

Code:
SELECT TableMain.MID, TablePerson.PID, TablePerson.FIRSTNAME, TablePerson.LASTNAME, TablePerson_1.FIRSTNAME, TablePerson_1.LASTNAME, TableMain.INFO, TableNotes.Notes
FROM (TablePerson INNER JOIN (TableMain INNER JOIN TablePerson AS TablePerson_1 ON TableMain.TE = TablePerson_1.PID) ON TablePerson.PID = TableMain.PO) INNER JOIN TableNotes ON TableMain.MID = TableNotes.MID
WHERE TableMain.MID=3

Which I built using the Query Design Window. It would be a good idea to read because you are likely to run into problems if one of the tables is missing a related record.
 
Thanks it works perfect. I added left join for TableNotes to handle a missing related record.

Is Query Design Window the same as Access 2003 Query wizard?
 
Is Query Design Window the same as Access 2003 Query wizard?"

More or less. Just choose design view, rather than selecting a wizard.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top