Merge tables with different columns

[Richard1941]

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]

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]

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]

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]

I'll try that. Thanks.

 

[theDBguy]

I'll try that. Thanks.

Sounds good. Let us know how it goes.

 

[Richard1941]

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]

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]

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.