Subquery help

  • Thread starter Thread starter AnExpertNovice
  • Start date Start date
A

AnExpertNovice

1. What is my best solution?
2. How would I do a nested query even if that is the worst solution. (If
worst solution, then I will, hopefully, understand nested queries.)


In a Combo box on a form the goal is to display a list of options that have
not already been selected. That is, if the person can have x, y, or z and
we are adding another option but X has been previously chosen, I only want
to give them the option to add Y or Z.

This requires me (not you! :) to use two queries. However, I would like to
explore using one query and change the combo box row source rather than have
one query call another query which calls 4 functions to determine other
values needed in the selection process.

If I were building two queries here is what they would look like.

Query 1: The query that would be opened.

SELECT tblIncludedStoreLevel.Level, Query2.IncludedStoreLevel
FROM tblIncludedStoreLevel
LEFT JOIN Query2 ON tblIncludedStoreLevel.Level = Query2.IncludedStoreLevel
WHERE Query2.IncludedStoreLevel Is Null;


Query 2: (for testing the values in the where clause are hardcoded as I
want them to be in the row source. In an actual query the values would be
referenced by function calls.

SELECT tblDistribution.EmailAddress, tblDistribution.JobGroupID,
tblDistribution.Location, tblDistribution.ReportID,
tblDistribution.IncludedStoreLevel
FROM tblDistribution
WHERE (tblDistribution.EmailAddress="emailaddr") AND
(tblDistribution.JobGroupID=8) AND (tblDistribution.Location=1111) AND
(tblDistribution.ReportID="CsrCpDvStD");


[row counts]
There are such queries to build. The first table has 26 rows. A count that
will get larger but not by much. tblDistribution has 1200 rows but could
grow to 3000+ rows. Thus, in the tables are small.

Thanks
 
The answer turned out to be simple.

SELECT IncludedStoreLevel
FROM tblIncludedStoreLevel
WHERE IncludedStoreLevel NOT IN (SELECT tblDistribution.IncludedStoreLevel
FROM tblDistribution WHERE (tblDistribution.EmailAddress='emailaddr') AND
(tblDistribution.JobGroupID=8) AND (tblDistribution.Location=2583) AND
(tblDistribution.ReportID='CsrCpDvStD'));
 
Back
Top