In Access, how do I join two tables into one report?

[Linda]

I am trying to complete an Access project and have created a database of two
tables - one of family and one of friends that I have to put into a one page
report. Their are only 3 records in each table. I have worked hours trying
to figure this out and have enlisted the help of some computer savvy friends.
None of them could figure out how to do it either. I think we are very
close, but just can't seem to get that last little bit of what we need to do.
Can someone please give me some suggestions? Thanks.

 

[DML]

Linda,

Let's assume your family table has a unique key of familyID. Your second
table should have at least two columns, familyID, friendsID to define the
friendship which combined make a primary key.

Create a query that contains two copies of familyID and one copy of
friendsID. Join the first copy of friends IDto the faimlyID in the friends
table, and the second copy of familyID to the friendsID in the friends table.

For example

SELECT tblFamilies.familyID AS Family, tblFamilies_1.familyID AS FreindsWith
FROM (tblFamilies INNER JOIN tblFriends ON tblFamilies.familyID =
tblFriends.familyID) INNER JOIN tblFamilies AS tblFamilies_1 ON
tblFriends.friendsID = tblFamilies_1.familyID;

Base your report on this query.

Hope this helps.

 

[Larry Linson]

We don't generally _do_ homework, but we do suggest approaches to help you
over stumbling-blocks. Two ways to show data from different tables in a
single report are:

1. Create a UNION or UNION ALL SQL statement including both the tables and
use that as the RecordSource of the Report. Check Help on this -- you can
create it only in SQL View. This will allow you to, for example, order by
Last Name, regardless of whether the record is for a family member or a
friend.

2. Create a Report for each of the Tables, and imbed the two in Subreport
Controls in a main Report. In this case, you'd have a sort order for Family
(in the Report in one Subreport Control) and a separate sort order for
Friends (in the Report in the other Subreport Control).

Quite possibly, there are other approaches, but I suspect one of these will
satisfy the requirements of your assignment.

Larry Linson
Microsoft Access MVP