Merge tables with different columns (1 Viewer)

Richard1941

Registered User.
Local time
Today, 03:45
Joined
Oct 29, 2012
Messages
34
I support an organization that has both members and volunteers. Member data includes start and end dates. Volunteer data includes interest areas. A query that draws data from both tables only gives me those people who are both members and volunteers. How can I create a query that produces all the people regardless of which category they are in and shows all the data appropriate to their category?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:45
Joined
Oct 29, 2018
Messages
21,357
Hi. You should be able to use a UNION query like using a FULL OUTER JOIN in SQL. However, you might have less problems if you simply combine the two tables into one, so you won't have to maintain the same data in multiple tables.
 

Richard1941

Registered User.
Local time
Today, 03:45
Joined
Oct 29, 2012
Messages
34
It's not quite that simple because I'm really working with several tables and many queries. All of the demographic data does come from a common table of people. The key to almost everything is the ID field from that table. There are also tables of membership history that us ID as a foreign key, and the same for various bits of volunteer information. And of course, queries that, for everything up until now, pull the right data from the right tables to tell us information about members, volunteers, donors and other things. Some of the columns of new data that I needed for my new query make us of ConcatRelated to create strings from multiple records. In fact, there is one of those associated with members and one with volunteers.

So what I need can be described as follows:
Start with all the Member data.
Add those columns associated with Volunteer data.
Where the rows have the same ID, merge the data (some members are also volunteers).
Where there are rows with new IDs, add more rows.

Any ideas?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:45
Joined
Oct 29, 2018
Messages
21,357
Any ideas?
I still think the UNION query idea will work, but I can't say for sure without seeing what you're dealing with. To use a UNION query though, you'll need to have the same columns on the queries or tables you're trying to combine.
 

Richard1941

Registered User.
Local time
Today, 03:45
Joined
Oct 29, 2012
Messages
34
OK, I figured out how to get two tables to have the same columns and how to do a UNION query. It merged the two tables, but not the rows. Where a person is both a member and a volunteer, I want only one row in the table with all the data.

It's probably easy, but my brain is fried at the moment from working on this for several hours.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:45
Joined
Feb 19, 2002
Messages
42,970
It's really hard for us to guess what your schema looks like. I would never have separate tables for members and volunteers. Having one table rather than two might solve the problem.

The reason you are ending up with two rows for people in both tables is because you are are pulling different data from each table and so the two rows are unique and the UNION is not summarizing them away.

One possiblility is to start with a Union query that selects only COMMON columns. That gives you a list of unique individuals. Then you create a second query that uses the Union query to left join to the member table and left join to the volunteer table and you would pull the unique columns from each table and they would end up on the same row.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:45
Joined
May 21, 2018
Messages
8,463
1)query 1: Do an inner join returning all persons in members and volunteers returning all fields you need
2) query 2: Do a left join from members to volunteers returning only those records that are in the members table with all fields you need from both tables (where volunteer id is null)
3) query 3: do a right join from members to volunteers returning those records that are only in volunteer table. (member id is null) with all fields from both tables.
4) union 1,2,3. You have one ID and all fields from both tables.
 

Attachments

  • MergeTables.accdb
    1.1 MB · Views: 118
Last edited:

Users who are viewing this thread

Top Bottom