ACCESS FEED: Re: [MS_AccessPros] Joining tables

Senin, 23 Maret 2015

Re: [MS_AccessPros] Joining tables

I will need to have labels for people without spouses.

This is great. It is the beginning steps in learning any new application that drive me nuts. I had all this before me (Patron_1) but did not take it far enough. Now I understand.

Thanks

On Mon, Mar 23, 2015 at 8:56 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Jan-

Open the query that you built with the Wizard in Design View. On the Design tab, click the Show Table button in the Query Setup group. In the Show Table dialog box, click the Tables tab and find and select the Patrons table. Click Add to add it to your query design. It should name the table Patrons_1. If there is a line between Patrons_1 and the HusbandID field, select it and press the Delete key to get rid of the link. Click on ID in the Patrons_1 table and drag and drop it to WifeID in the linking table.

Once you do this, the records from the original Patrons table will be for the Husband, and the records from Patrons_1 will be for the wife.

Note that this will produce records for your report only for couples who have a record in the linking table. It will not produce a record for anyone who doesn't have a spouse. If you need that, reply again, and I'll give you a solution.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Mar 23, 2015, at 3:37 PM, Jan Hertzsch jan.hertzsch@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi John:

Unfortunately. I am not understanding how to do what you mention. Surely I don't need to maintain the same table twice.

When I go though the query wizard, I see only Patrons and Spouses. Could you elaborate?

Thanks
(I dragged out my Access 2007 Inside Out book the other day and guess who the author is. Nice work. )

On Mon, Mar 23, 2015 at 7:39 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Jan-

You need TWO copies of the Patrons table in your query - one linked to HusbandID and the other linked to WifeID.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Mar 23, 2015, at 2:23 PM, jan.hertzsch@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have a table called Patrons with names and addresses. I created a table called Spouses with just the husband's Access ID and the Wife's Access ID (my titles are more socially correct but this describes it better)

I created a link between the Patrons table and the Spouses table of one to many with the ID in the Patrons table linked to both the Husband and the Wife field on the Spouses table.

Now, I want to produce mailing labels with

Husband name
Wife name
Address.

The best I have been able to get is
Husband Name
Wife ID
Address.
My first Access database. Can someone tell me what I am doing wrong?

Thanks