UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query Design, Access 2010    
 
   
LonesomeDove
post Sep 10 2019, 03:59 PM
Post#1



Posts: 345
Joined: 14-October 10
From: Southeastern Pennsylvania


The answer is probably quite simple, but it eludes me.

I am building a database to manage volunteer job assignments for a thrift store.

There are presently 200 volunteers and 45 jobs. A volunteer may have from zero to 8 jobs assigned to him/her.

The Assignments form has 3 list boxes. The Volunteers list box shows all the volunteers. The Assigned Jobs listbox shows the jobs assigned to the volunteer selected in the Volunteers listbox. The Available Jobs listbox shows the jobs that are not assigned to the selected volunteer.

There are 3 tables supplying data to these listboxes. tblVolunteers (all the volunteers), tblJobs (all the jobs), and tblVolunteerJobs (manages the many to one relationships).

The problem: I need a query to fill the Available Jobs listbox when the user selects a volunteer in the Volunteers listbox.

Thanks in advance.

--------------------
LonesomeDove
"There is great satisfaction in building good tools for other people to use." - Freeman Dyson
Go to the top of the page
 
Doug Steele
post Sep 10 2019, 04:03 PM
Post#2


UtterAccess VIP
Posts: 22,223
Joined: 8-January 07
From: St. Catharines, ON (Canada)


So what do you want shown in the list box: jobs not currently performed by any of the other volunteers, jobs not currently associated with the selected volunteer, or something else?

(To make it easier for us to provide an answer, what are the names of the fields in the three tables?)

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
nvogel
post Sep 10 2019, 04:05 PM
Post#3



Posts: 1,023
Joined: 26-January 14
From: London, UK


Maybe something like the following example will help.

SELECT j.*
FROM tblJobs AS j
LEFT OUTER JOIN tblVolunteerJobs AS v
ON j.jobid = v.jobid
WHERE v.jobid IS NULL;
Go to the top of the page
 
Doug Steele
post Sep 10 2019, 04:13 PM
Post#4


UtterAccess VIP
Posts: 22,223
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Another variation on that same query would be

CODE
SELECT j.*
FROM tblJobs AS j
WHERE j.jobid NOT IN (SELECT jobid FROM tblVolunteerJobs);

Hard to say which query would be more efficient.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
LonesomeDove
post Sep 11 2019, 09:01 AM
Post#5



Posts: 345
Joined: 14-October 10
From: Southeastern Pennsylvania


Works great!

Thanks very much.

--------------------
LonesomeDove
"There is great satisfaction in building good tools for other people to use." - Freeman Dyson
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th November 2019 - 05:44 AM