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

  • Follow


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.
0
Reply Utf 12/9/2007 10:51:01 PM

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.

"Linda" wrote:

> 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.
0
Reply Utf 12/10/2007 2:13:00 AM


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


"Linda" <Linda@discussions.microsoft.com> wrote in message 
news:14D70283-4D44-4F30-929F-268B2AABA774@microsoft.com...
>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. 


0
Reply Larry 12/18/2007 3:10:52 AM

2 Replies
1467 Views

(page loaded in 0.052 seconds)

4/15/2014 12:08:08 AM


Reply: