Hi everyone,

I have a puzzling question about a result I am looking for in Access.

I have a long list on workshops and the name of all people who attended each workshop. I created a form with all the workshops in a multi-select listbox. My goal is to be able to list the people who attended all the selected workshops. Sounds simple hey? Well...

Even in a simple Access query I have a hard time to find the people who came to the two workshops specified as the criteria.

Say the two workshops are Word and Excel. The criteria cannot be Word Or Excel because it will list those who came to both as well as to either one! I only want those who came to BOTH! Of course using Excel AND Word returns 0 record.

In plain English I have an ensemble or those who attended workshop A and those who attended workshop B and I need to display A intersect B. When I will be able to do this I will need this to work for more than 2 workshops. So I could need to list A intersect B intersect C insersect D ... The more workshops the more restrictive the query.

Is that possible?

Note: I will do this query using a SQL query in VBA. I am very fluent with VBA and SQL in code

Daniel

__________________
Daniel

Daniel,

This is easily doable in VBA and possibly doable just using queries.

Since you're in VBA, the easy way would be to create a recordset that selects people and workshops, sorted by people. This will give you one record for each workshop attended. Then in a loop read one record at a time while tracking a single person. One way or another, and there are many ways, while tracking that person determine whether they attended all of the workshops that were selected in the multi-select listbox.

As far as doing this with just queries, the way to accomplish this would be to have one query for each workshop. The record set of the query will have a set of all of the people that attended that workshop. Then INNER join your person table to each of the workshops queries that need to be included. Group by your person identifier so you only have the person listed once. Of course this could get rather ugly depending on how many workshops you have. You could dynamically build the queries as needed to keep from having to have a query for each workshop. Or you could write the subqueries right into the SQL. e.g.

SELECT tblPeople.personID, tblPeople.PersonName
FROM (tblPeople INNER JOIN (Select personID From tblWorkshops where WorkShopID = listboxselecteditem(1)) AS WS1 ON tblPeople.personID = WS1.personID) [Continue INNER JOINing as necessary] ...
GROUP BY tblPeople.PersonID;

Watch out for balancing those parentheses!!!

Since you say you want to "list all people who attended", I assume you want to list them elsewhere on the form. The query method will allow set the Record/Row Source of whatever object you're listing those people in. But keep in mind that if you're listing those names in a list box or combo box you can set the Row Source Type to "Value List" and build the Row Source yourself in VBA.

There's probably another solution out there somewhere.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Thanks Randall for your precious time.

It is interesting that I thought of many of your ideas before but I was overwhelmed by the final query if the lady asked for those who attended up to 12 workshops!

As I was waiting for an idea I decided to tackle this project using another appreach. Read on if you want:

I create a query that will list all the workshop names (most are given more than once to I will go with their name and not their occurence in time) with everybody that attended all of them using a criteria based of the selection in the list box then I would get all people that attended one or more of the selected workshops right? This query uses the DISTINCT so that if a person took a seminar more than one it will only appear once.

This worked very well. The QueryDef is recreated by code with the appropriate criteria.

Now one more Total query group by the Participants and count the number of workshops they attended. If the count matches the number of selected workshops then they MUST have come to all of them.

For instance if I use 4 workshops the first query returns say 640 unique rows. The second one counts the number of workshops per participants. Some have a 1, 2, 3 and little have 4. I just code the criteria to return those having a count equal to the number of workshops selected.

The two queries are deleted and rebuilt everytime the user click on the Print button of the form and it is very quick and works perfectly.

The last resulting query is linked with the Participants table to print the information required for the report.

If you want to see the database with the form, the queries and the report to examine let me know. With very little modification (done in code) I can list people who came to 1 to an unlimited number of workshops.



Daniel

Outstanding solution! Way to go!

I understand the concepts you used. No need to publish your discovery.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org