Okay, lots of ground to cover. This post is the Big Picture about joins. And, it looks like you don't need it anymore, given your 11:00 edit of post 13...
QUERIES, SQL AND JOINS
If a query contains more than one table, then any way you make it happen, in the background somewhere there will be a JOIN written in SQL. (If there's only one table, there probably won't be a JOIN.)
Check out Chapter 6 of Crystal Long's Tutorial for a quick overview of SQL, at
http://www.accessmvp.com/Strive4Peac...Chapter_06.pdf. Look at figures 6-2 and 6-3, which show the design view and SQL view of the same query. Then look at the text immediately following figure 6-3, where Crystal cleans up the SQL and bolds some items to explain what they mean.
INNER JOINS
An INNER JOIN means that only the matching records from each table will be returned from the query. The same INNER JOIN query can be coded multiple ways. The following two queries are equivalent, if I've coded them correctly. The first version is an implicit INNER JOIN, while the second one is an explicit version of the same INNER JOIN.
Code:
SELECT Table1.Key, Table1.Field1, Table1.Field2, Table2.Field3
FROM Table1, Table2
WHERE Table1.Key = Table2.Table1Key
AND Table1.Field1 = "X";
SELECT Table1.Key, Table1.Field1, Table1.Field2, Table2.Field3
FROM Table1
INNER JOIN Table2
ON Table1.Key = Table2.Table1Key
WHERE Table1.Field1 = "X";
OUTER JOINS
A LEFT JOIN, which is an example of an OUTER JOIN, will return all the records from the first (left) table, along with the matching records from the second (right) table. If no matching record exists on the right table, then all fields from the right table will show up as NULL.
There's no technical difference between these two OUTER JOIN queries, if I've coded them correctly.
Code:
SELECT A.*, B.*
FROM TableA AS A
LEFT JOIN TableB AS B
ON A.Key1 = B.Key2;
SELECT A.*, B.*
FROM TableB AS B
RIGHT JOIN TableA AS A
ON A.Key1 = B.Key2;
The two OUTER JOINs above are equivalent, but programmers, by convention, use LEFT JOINS in preference to RIGHT JOINs. That convention allows you to remember that the far left table is the one from which all records are retained.
FULL JOINS, CROSS JOINS, ETC
A FULL OUTER JOIN is like a LEFT and RIGHT join at the same time... all appropriate records in both tables will be returned, with NULL in the other table's fields if there was no match in the other table. I don't use these much.
A CROSS JOIN, also called a Cartesian product, basically joins every record of the left table to every record of the right table. This is extremely useful, but only in a few situations. Here's one write-up of that subject http://weblogs.sqlteam.com/jeffs/arc...9/12/7755.aspx.
PIVOT table joins are cool things for analysis of data, which I've never yet had a need for, so I can't speak to them. Crystal Long writes about them in Chapter 6, the link I gave you above.
Synchronization of database setup and form design
Windows 7 64bit 
Access 2010 32bit
Reply With Quote
Try this:
