Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 3rd, 2005, 02:43 PM
Authorized User
 
Join Date: Feb 2005
Location: Frankston South, Victoria, Australia.
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL query question in Access

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
  #2 (permalink)  
Old April 3rd, 2005, 03:39 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #3 (permalink)  
Old April 3rd, 2005, 09:02 PM
Authorized User
 
Join Date: Feb 2005
Location: Frankston South, Victoria, Australia.
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #4 (permalink)  
Old April 3rd, 2005, 10:42 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
sql query question ldp101068 SQL Server 2000 6 December 3rd, 2007 03:41 PM
SQL Query question darkhalf Classic ASP Databases 4 March 17th, 2004 07:44 PM
Access VBA - SQL question. Raelz Access VBA 4 February 10th, 2004 11:28 PM
Sql query question. pankaj_daga SQL Language 3 November 30th, 2003 03:04 PM
sql access syntax question using "LIKE" katie456 SQL Server 2000 3 October 10th, 2003 05:49 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.