I have 3 Access 2003 tables:

TableMain

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

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



I can get this output if I query for a MID number such as 3:

Firstname Lastname Firstname2 Lastname2 Info
Joe Smith Bill Carson OIUOI

Here is the query:
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


Now if I have a third table (called NotesTable) how would I get that
info?
For example if I query for MID number 3, I should get this:
Firstname Lastname Firstname2 Lastname2 Info Notes
Joe Smith Bill Carson OIUOI
other data

Here is NotesTable
NID MID Notes
1 miscl notes here
2 3 other data
3 fan info


Please advise

On Nov 27, 1:44 am, "tes... (AT) hotmail (DOT) com" <tes... (AT) hotmail (DOT) com> wrote:
First point are your joins One to Many or Many to Many? -
If they are One to many (MAIN is the parent of PERSON) then MAIN table
should not hold data that belongs to PERSON it should be the other way
round!
PERSON would then hold a MID as a foreign key field from the MAIN to
make the one to many join.
If your join is many to many from MAIN to PERSON or vice versa then to
resolve that correctly you need a link table to do it consisting of
MID and PID which maps All occurences of MAIN to PERSON. The same is
then true for NOTES - If a NOTE can belong to more than one PERSON OR
MAIN? then it is a many to many join and a linker table should be
employed to make the join using MID or is it PID and NID as the
fields.

Most designers would agree that to hold a candidate foreign key field
in a table (NOTES.MID) which is nullable (as you show) breaks far too
many relational rules to be correct. (does MID belong to NOTES - no
because MID is absent from 2 rows are those 2 rows 'complete' No
because they hold nulls in the MID field. For some better explanation
of relational joins have a look at http://support.microsoft.com/kb/289533.

Good luck

I may well be missing your point here but there isn't really enough
detail in your question to comment completely accurately.