Access 2010 query for 3 tables


Leslie Coover

These are my table fields and relationships:

Primary Key: COLID AutoNumber
Collection (Text)
ONE TO MANY with Table_Citations

Primary Key: CITD AutoNumber
COLID (Number, Long Integer)
Citation (Text)
Source (Memo, Rich Text)
ONE TO MANY with Table_Notes

Primary Key: NID AutoNumber
CITD (Number, Long Integer)
Note (Memo, Rich Text)

Here is my SQL, but there is something wrong with it. It pulls the data from the first two tables OK, but nothing from the 3rd (Table_Notes). Thank you for your help in advance.

SELECT Table_A_Collections.Collection, Table_A_Collections.COLID AS Table_A_Collections, Table_Citations.COLID AS Table_Citations_COLID, Table_Citations.Citation, Table_Citations.CITD, Table_Citations.Source, Table_Notes.CITDAS Table_Notes_CITD, Table_Notes.NID, Table_Notes.Note
FROM (Table_A_Collections LEFT JOIN Table_Citations ON Table_A_Collections.[COLID] = Table_Citations.[COLID]) LEFT JOIN Table_Notes ON Table_Citations.CITD = Table_Notes.CITD
ORDER BY Table_A_Collections.Collection;


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question