Creating Access Junction Table

[STRATMAN1]

Cannot find correct process for creating a junction table in Access. I have attempted via help screens, instructions dont seem to work. Always seems to be an outer (type 2) join automatically created between the two tables I want to relate through the junction table. I have created one junction table with 1 field from each of the other two tables and set each of those two fields with a primary key in the new junction table. Then create a query with all three tables pulled in, but no data comes into the query. Any help appreciated. Thanks, STRATMAN1

 

[fneily]

Your junction table contains the PRIMARY KEYS of the other two tables and any common fields between the tables. Your junction table may also have it's OWN PRIMARY KEY. If there aren't any common fields between the two tables, then the PRIMARY KEY of the junction table is the multi-field combination of the two PRIMARY KEYS of the tables. The joins between the tables depends on what you want. They can all just be regular Inner joins or you could do the outer joins as you stated or a mixture. It depends on what you want.

 

[JeremyNYC]

SELECT tblPerson.FirstName, tblAddress.ZipCode
FROM (tblAddress INNER JOIN tblPersonAddress ON tblAddress.AddressID = tblPersonAddress.AddressID) INNER JOIN tblPerson ON tblPersonAddress.PersonID = tblPerson.PersonID;

will do the trick. Just change the table and field names so they match yours, or create three simple tables to see how it works.

If you're still not having any luck, post back with the SQL from your query and we can take a look at that.

Jeremy

---
Jeremy Wallace
Up & Running Project Coordinator
Fund for the City of New York