macbookpro
New Member
- Joined
- Jan 12, 2015
- Messages
- 5
Hello everybody!
I have a Table of club members linked to an Address Table in a 1 to Many relationship. So each Address row is linked to one or more club members ( in practice max 2 members at one address). I want to produce an address query where joint members are addressed as Mr & Mrs J Smith.
Sample data:
Member Table
---------------------
ID Title Forename Surname Addr Id
1 Mr John Smith 100
2 Mrs Mary Smith 100
3 Mr Fred Bloggs 101
Address Table
----------------
Addr Id Address Details
100 Address1
101 Address2
Required result
----------------
Mr John Smith Mrs Mary Smith Address1
Mr Fred Bloggs Address2
I could then do a calculation to get Mr & Mrs J Smith.
I tried a Self Join query, but got 4 rows for the Smiths!
Thank you!
I have a Table of club members linked to an Address Table in a 1 to Many relationship. So each Address row is linked to one or more club members ( in practice max 2 members at one address). I want to produce an address query where joint members are addressed as Mr & Mrs J Smith.
Sample data:
Member Table
---------------------
ID Title Forename Surname Addr Id
1 Mr John Smith 100
2 Mrs Mary Smith 100
3 Mr Fred Bloggs 101
Address Table
----------------
Addr Id Address Details
100 Address1
101 Address2
Required result
----------------
Mr John Smith Mrs Mary Smith Address1
Mr Fred Bloggs Address2
I could then do a calculation to get Mr & Mrs J Smith.
I tried a Self Join query, but got 4 rows for the Smiths!
Thank you!