This is really a question about how to create queries in Access and you may
find a better description of how to do that in an Access group.
However, in an attempt to get you started...
Suppose for example each project has an associated project leader in a
"person" table and an associated department, and you have
Project
ProjectID
ProjectName
PersonID
DepartmentID
Person
PersonID
PersonName
Department
DepartmentID
DepartmentName
Then in Access you can create the necesary query to "join" these tables in a
number of ways. First, although it is not logically essential, you might
find it useful to define the Relationships between these tables using
Tools|Relationships. There should be one-many relationships for
Person-Project and Department-Project. Then, if you create a new query in
design view, you should be prompted for the tables you want to use in the
Query. Select all three tables. If you defined the relationships correctly,
you should see connecting lines between Department.DepartmentID and
Project.DepartmentID, and between Person.PersonID and Project.PersonID. Drag
the * from Project into top cell of the first column in the grid. Then drag
Person.PersonName into the top cell of the second column, and
Department.DepartmentName into the top cel of the third column. If you now
right-click in the title bar or table area in the query designer, and select
SQL view, you should see something lke:
SELECT Project.*, Person.PersonName, Department.DepartmentName
FROM Person INNER JOIN (Department INNER JOIN Project ON
Department.DepartmentID = Project.DepartmentID) ON Person.PersonID =
Project.PersonID
WITH OWNERACCESS OPTION;
If you do not see the INNER JOIN stuff, it is almost certainly because you
have not set up the relationships. For this query, you can do it in the
query designer (i.e.right-click on the SQL box and choose Query Design
again) by dragging Department.DepartmentID to Project.DepartmentID. A line
should appear. Select the line, double-click or ight click on it, make sure
the names in the dialog box are the ones you expect, then select the "Only
include rows where the joined fields from both tables are equal" option. Do
the same for the Person/Project relationship, and re-inspect the SQL.
Then save your query, and use it as the data source for your merge.
Personally, I prefer to enter the SQL directly (just navigate as quickly as
you can to the appropriate box) but tend to forget the precise syntax for
multiple INNER JOINs and find it useful to generate the starting point using
the tables/lines approach described above, then just edit the SQL text
manually. Since multiple INNER JOINs have to be nested it is also sometimes
simpler to revert to old-style SQL syntax - in the above case,
SELECT Project.*, Person.PersonName, Department.DepartmentName
FROM Project, Person, Department
WHERE Project.PersonID = Person.PersonID
AND Project.DepartmentID = Department.DepartmentID
which I think is a bit simpler to understand. Another way of simplifying
things can be to create simple queries involving two of the tables (e.g.
Project and Person), then create a further query that uses the first query
as one of its "tables". (But that is more likely to lead to performance and
maintenance problems.
