Dynamic Queries based on multiple group membership

[sistemalan]

Hi and thanks for looking,

I've posted this in the VBA section as I suspect it will need some code. It might be that some experienced soul can point out a different way of achieving what I desire involving structuring my data in a different way. I'm all ears. Here's what I want to be able to do:

I help run a children's music club and use a database to keep track of several hundred children. Children who attend could be members of any number of groups, and new groupings are added all the time. I want to be able to easily see which children are members of which groups and will want for instance to bring up lists of children based on parameters such as "member of Group A and Group B but not a member of Group C"

The way I've been recording information has been to have a Children Table, a Groups Table and a GroupMembers Table to make links between the 2.

I've create and attached a sample database. The attached database has a Groups Table and People Table and a GroupMembers Table. Some people are members of more than one group. I'd like to be able, for instance to see everyone who is a member of The Button Family who is also in the group Men and the group Violinists. I know I could achieve this with multiple queries, but the way I would go about this would only work if these groups will be set and static, whereas I want to be able to find this information based on membership (or not) of any number of ever changing groups.

I'm guessing that I need some sort of dynamically produced form which creates combo boxes based on the Groups Table, and the users selections of that form would dynamically create a query which would return the desired information.

I hope that makes some amount of sense and that you are able to help.

Many Thanks,

Alan

 

[bob fitz]

Perhaps you need something like this:

 

[sistemalan]

Hi Bob,

Thanks for taking the time to create an example. It doesn't do what I had hoped. I'll try and clarify.

First things first: How, in the example we are playing with, would you bring up a list of all of the people in the group "women" who are also in the group "violinists"?

Alan

 

[JHB]

Have a look at the crosstab query.

Sql for the above:

TRANSFORM Count(T_People.PersonID) AS CountOfPersonID
SELECT T_People.FirstName, T_People.Surname
FROM (T_GroupMembers INNER JOIN T_Groups ON T_GroupMembers.GroupID = T_Groups.GroupID) INNER JOIN T_People ON T_GroupMembers.PersonID = T_People.PersonID
GROUP BY T_People.FirstName, T_People.Surname
PIVOT T_Groups.GroupName;

You can manipulate the query's SQL, (use the QueryDef) from code, if you want to narrow down the result.

 

[sistemalan]

Hi JHB. That looks promising.

I do indeed need to be able to narrow down the results. How for instance would I use VBA or SQL to just show a list of people who were in the Groups "Men" and "Violinists"?

Very grateful for any further help.

Alan

 

[JHB]

Here is a sample, be aware of some members are in the same group twice, like "Iain Button" is twice member of the "Tuba Players"!

 

[sistemalan]

Thanks very much for that. It's exactly what I need.

Best Regards,

Alan

 

[JHB]

You're welcome, good luck.