Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-23-2019, 11:22 AM   #1
Richard1941
Newly Registered User
 
Join Date: Oct 2012
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Richard1941 is on a distinguished road
Merge tables with different columns

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?

Richard1941 is offline   Reply With Quote
Old 08-23-2019, 11:26 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,963
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Merge tables with different columns

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 08-23-2019, 11:46 AM   #3
Richard1941
Newly Registered User
 
Join Date: Oct 2012
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Richard1941 is on a distinguished road
Re: Merge tables with different columns

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?

Richard1941 is offline   Reply With Quote
Old 08-23-2019, 11:56 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,963
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Merge tables with different columns

Quote:
Originally Posted by Richard1941 View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 08-23-2019, 11:59 AM   #5
Richard1941
Newly Registered User
 
Join Date: Oct 2012
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Richard1941 is on a distinguished road
Re: Merge tables with different columns

I'll try that. Thanks.
Richard1941 is offline   Reply With Quote
Old 08-23-2019, 12:04 PM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,963
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Merge tables with different columns

Quote:
Originally Posted by Richard1941 View Post
I'll try that. Thanks.
Sounds good. Let us know how it goes.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 08-23-2019, 01:55 PM   #7
Richard1941
Newly Registered User
 
Join Date: Oct 2012
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Richard1941 is on a distinguished road
Re: Merge tables with different columns

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.

Richard1941 is offline   Reply With Quote
Old 08-23-2019, 08:36 PM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,272
Thanks: 15
Thanked 1,595 Times in 1,515 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Merge tables with different columns

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-24-2019, 03:19 AM   #9
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,858
Thanks: 36
Thanked 566 Times in 533 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Merge tables with different columns

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.
Attached Files
File Type: accdb MergeTables.accdb (1.10 MB, 2 views)

Last edited by MajP; 08-24-2019 at 08:34 AM.
MajP is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Merge columns from two tables fboehlandt Queries 1 04-21-2017 02:00 AM
merge Columns xltest Excel 13 10-09-2012 11:41 PM
Create a query that takes 2 columns and merge them into 1 cdoyle Queries 4 05-10-2012 09:50 AM
Merge tables by columns, not rows hunoob Tables 4 04-05-2009 03:36 AM
Merge Columns betheball Queries 46 07-05-2004 12:32 PM




All times are GMT -8. The time now is 01:39 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World