ACCESS FEED: Re: [MS_AccessPros] Joining tables

Senin, 23 Maret 2015

Re: [MS_AccessPros] Joining tables

To get all in one fell swoop, first create a query that includes the linking table (Spouses?) and Patrons, but make sure the link from Patrons to the linking table is on WifeID. Put whatever fields you need for wives AND the HusbandID on the query grid and save that as qryWives.

Now start another query on Patrons and add qryWives using Show Tables. Link the ID in Patrons to the HusbandID in the query. Select the linking line, then right click and select Join Properties. Select the option to include all rows from Patrons and any matching rows from Wives. Put the fields you need for the mailing list on the query grid from both Patrons (husbands or unmarried people) and qryWives. Make sure you include the ID from Patrons and add this in the Criteria box under the field:

NOT IN (Select WifeID FROM Spouses)

Use this query for your mailing list. Any people in Patrons who do not have a linking entry will show up with blanks in the fields from qryWives. You will also not get anyone who shows up as a "wife" in the Spouses table - they should already be included in the info from qryWives.

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 5:16 PM, Jan Hertzsch jan.hertzsch@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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