Unusual Merge Two Tables into one Query

[coolhand729]

Okay, this is not your normal two table query (at least it doesn't seem that way to me).

I'm using Access 2000 on Windows XP.

I have two tables in access. One is a table with addresses of club members and the other is a table with addresses of club houses. I want to merge the Addresses and Names in one long list.

For Example:
Table 1:
MemberName.....MemberAddress.....MemberClubhouse
Joe....................12345 Nowhere.......Clubhouse One
Mike..................23456 Nowhere.......Clubhouse Two
Steve.................34567 Nowhere.......Clubhouse Three
Diana.................45678 Nowhere.......Clubhouse One

Table 2:
ClubhouseName.....ClubhouseAddress
ClubhouseOne.......9876 Somewhere
ClubhouseTwo.......8765 Somewhere
ClubhouseThree.....7654 Somewhere

Now.... What I want is a query that returns this:
Name...................Address
Joe .......................12345 Nowhere
Mike......................23456 Nowhere
Steve.....................34567 Nowhere
Diana.....................45678 Nowhere
ClubhouseOne........9876 Somewhere
ClubhouseTwo........8765 Somewhere
ClubhouseThree......7654 Somewhere


This is for a mailing list where I would like to both mail the newsletter to the members and to the regional clubhouses where they are members at. I have the members and clubhouses in two separate tables for obvious reasons.

I honestly have no idea where to start with this problem. I could always have two separate queries and then copy and paste them together in Excel, but I'm trying to automate the process as much as possible (plus I would like it to be a one step process for when I hand the database to another user without computer skills).

I realize that this may be fairly complex, but if someone can point me in the right direction or to a starting point, I can probably figure it out myself.

Hopefully this is much easier than I think it is.

Thanks for your help in advance

- Marshall

[JKing]

You can use a Union query,

SELECT memberName, memberAddress
FROM Table1
UNION
SELECT clubhouseName, clubhouseAddress
FROM Table2
 

[mlcampeau]

What if you were to append the two tables to a third table?

[FishVal]

Okay, this is not your normal two table query (at least it doesn't seem that way to me).

I'm using Access 2000 on Windows XP.

I have two tables in access. One is a table with addresses of club members and the other is a table with addresses of club houses. I want to merge the Addresses and Names in one long list.

For Example:
Table 1:
MemberName.....MemberAddress.....MemberClubhouse
Joe....................12345 Nowhere.......Clubhouse One
Mike..................23456 Nowhere.......Clubhouse Two
Steve.................34567 Nowhere.......Clubhouse Three
Diana.................45678 Nowhere.......Clubhouse One

Table 2:
ClubhouseName.....ClubhouseAddress
ClubhouseOne.......9876 Somewhere
ClubhouseTwo.......8765 Somewhere
ClubhouseThree.....7654 Somewhere

Now.... What I want is a query that returns this:
Name...................Address
Joe .......................12345 Nowhere
Mike......................23456 Nowhere
Steve.....................34567 Nowhere
Diana.....................45678 Nowhere
ClubhouseOne........9876 Somewhere
ClubhouseTwo........8765 Somewhere
ClubhouseThree......7654 Somewhere


This is for a mailing list where I would like to both mail the newsletter to the members and to the regional clubhouses where they are members at. I have the members and clubhouses in two separate tables for obvious reasons.

I honestly have no idea where to start with this problem. I could always have two separate queries and then copy and paste them together in Excel, but I'm trying to automate the process as much as possible (plus I would like it to be a one step process for when I hand the database to another user without computer skills).

I realize that this may be fairly complex, but if someone can point me in the right direction or to a starting point, I can probably figure it out myself.

Hopefully this is much easier than I think it is.

Thanks for your help in advance

- Marshall

Hi, Marshall.

The answer is very simple. SQL has UNION statement to merge records from multiple tables.

SELECT * FROM Table1 UNION SELECT * FROM Table2;
 

Good luck.

[coolhand729]

Thank you! Thank you! Thank you!

I knew there was an easy way to do it.

All praise the UNION Query!!!!

Thanks again! Problem solved!