Query to combine "Users" and "Groups" into a single table (1 Viewer)

Rufus0065

New member
Local time
Today, 11:02
Joined
Apr 19, 2021
Messages
5
I've got a table called People. Each person is unique. I've got a table called Groups. Each group is unique.

A person can be a member of multiple groups. Not every person is in a group.

Groups can have multiple members. A group can also be a member of another group (e.g. "children" and "parents" are both groups within "family").

I've got the structure shown in this picture. People and groups are connected via a table called Associations.

What query do I need to write to create an output (or another table) that has the complete list of "Full Name" from People and "Group Name" from Groups in one entity called "CombinedResults"?

Thanks. Sorry if this is a trivial question, but I'm confused.
Grab.png
 

Jon

Access World Site Owner
Staff member
Local time
Today, 11:02
Joined
Sep 28, 1999
Messages
5,052
Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:02
Joined
Oct 29, 2018
Messages
15,592
Hi. Welcome to AWF!

What I think you want is a FULL OUTER JOIN, which is not supported in Access. Instead, you can use a UNION query to get the same result.

PS. I am moving your thread out of the Introduction Forum.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:02
Joined
May 21, 2018
Messages
5,121

Rufus0065

New member
Local time
Today, 11:02
Joined
Apr 19, 2021
Messages
5
Hello both of you and think you for your kind replies.

Let's say I have 3 People, Alice, Ben and Chris and I have 2 groups Parents and Children. I just want one list:
Alice
Ben
Children
Chris
Parents

I have looked at Union queries, but unlike the other queries they seem to need manual execution, and I want this to work automatically whenever a new person or group is created.

I hope that clarifies things.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:02
Joined
May 21, 2018
Messages
5,121
I was wrong and @theDBguy guessed correctly.
I have looked at Union queries, but unlike the other queries they seem to need manual execution, and I want this to work automatically whenever a new person or group is created
That is not true. If you write it once then it works whenever an update is made. However, you cannot build one in the query designer. You have to pick SQL view and type it.
 

bastanu

AWF VIP
Local time
Today, 03:02
Joined
Apr 13, 2010
Messages
826
This should work:

SELECT [First Name] & " " & [Last Name] As EntityName, "People" As SourceTable FROM People
UNION
Select [Group Name] As EntityName,"Groups" As SourceTable FROM Groups;

Cheers,
 

Rufus0065

New member
Local time
Today, 11:02
Joined
Apr 19, 2021
Messages
5
That's really helpful, thank you very much. I'm going to see how it performs when updates are made to either the Groups table or the People table. Fingers crossed.

Thank you very much again.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:02
Joined
Sep 12, 2006
Messages
14,492
The important thing is - why do you want this in the way you described?
Why do you even need these groups named as such?

I can't actually see so much benefit of even having a union query to pull them together in the manner shown by @bastanu

I am also not sure at all of the groups within groups. A person will be both a child and a parent by nature of the other people to whom he is related, and not by some arbitrary group membership.

Is this a genealogy database you are trying to construct?

if so the best way would be to have your people table store a gender for each person, and then have each person store a link to another person as "father" and "mother" - then you don't need a "groups" at all. The data becomes self referencing, and you can construct an entire family tree based on this one idea.

That way a given person is simply a child of two parents, and his full siblings are other persons sharing the same parents. A grandparent is any parent of a child's parents. A person's first cousins are people for whom either one of his parents are siblings of either one of the parents of another child.

Even this becomes somewhat difficult when you start to bring in relationships between individuals which are not necessarily the same as the parentage, ie Marriages/Partnerships, as opposed to genetic parentage.

It's easy with horses - less so with people.
 
Last edited:

Rufus0065

New member
Local time
Today, 11:02
Joined
Apr 19, 2021
Messages
5
Hi,

The reason is actually really simple. Let's say I want to create an event (e.g. "Plan summer holiday"). That may be owned by me (e.g. "Dad" a person not a group) but I may need to ask opinions (e.g. "Family" a group).

So I create a form from in which I create the event, assign it, and involve others.

Now say I have another event (e.g. "Conduct an interview") which is owned by a group (e.g. "Interview Panel") and involves an individual (e.g. "Interviewee"). It's the same form that gets used.

So, in the combo boxes for "owner" and "audience" I need to have a single list of all the people and groups in my database. It's as simple as that. Few, but useful, forms. Behind that are sensible and meaningful tables. Versatile reports and a simple to maintain database and application.

Hope this was a helpful explanation. I only used the "parent" and "child" analogy as it is the simplest to articulate, but the groups and people are much more diverse in the real instance.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:02
Joined
Sep 12, 2006
Messages
14,492
The reason is actually really simple. Let's say I want to create an event (e.g. "Plan summer holiday"). That may be owned by me (e.g. "Dad" a person not a group) but I may need to ask opinions (e.g. "Family" a group).

Yes - but can a person only be in one group? what if Dad wants to include both his children and his parents (and mum's parents, and the kids girlfriends and boyfriends) when considering the holiday. How do you decide which connected groups to include? What if dad is in a work group, a golfing group, and multiple family groups. It might get very hard to manage.

Maybe have two separate list boxes (or combo boxes) - one for the people, and one for the groups - then when you select a person, you can show the groups of which the person is a member, and then as you select a group you can get a separate list of the members of that group (or even a combined list of members of all the selected groups . I just think a person, and a group are different entities, and it might be easier to keep them separate rather than join them together.
 

Rufus0065

New member
Local time
Today, 11:02
Joined
Apr 19, 2021
Messages
5
Hi, sorry for the delayed reply.

A person can be in more than one group (e.g. a person can be "Parent" and "Home Owner" or "Child" and "Home Owner" or just "Home Owner"). The management is only hard if you think of the groups from the bottom up rather than the top down. It's better to think "who should be in this group" (top down) rather than "which group should this person be in" (bottom up). I've created some simple forms and queries in Access that apply consistent rules about the workflow for these decisions.

I don't need separate combo boxes.

So I can then have a drop down on a Form (e.g. "Mail All Home Owners") which will present just all the unique members of the group, or a drop down on a Form (e.g. "Mail Unassigned Home Owners") which will just present unique members that are not also members of groups.

Works a treat!

Thank you for your thoughts.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:02
Joined
Feb 19, 2002
Messages
32,285
The union query should answer your original question but it will create other problems. All of us are having trouble with how you are going to use this. "people" are invited to functions not groups. Therefore, you need to end up with a list of people. And that means that what you are doing won't be useful.

If you wand to create a consolidated list of people from groups and individual selections, this is NOT how you would do it. There would be a table to hold the invitees. There would be an autonumber PK and a compound unique index of both FunctionID and PersonID. Then you would use an unbound combo to select groups and your code would run an append query to select individuals from a group and append them to the invitees table. The unique index will prevent the same person from being added multiple times. Then you would use the subform with the invitees and a combo bound to the people table to select individuals who were not selected as part of a group.
 

Users who are viewing this thread

Top Bottom