Filter records by comparing a field to a query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Filter records by comparing a field to a query

Author

Topic

jonhath
Starting Member

13 Posts

Posted - 2007-12-28 : 13:59:25

I have a query that returns fields (Name, phone number, etc) and I want it -NOT- to return the record if an entry is in a column of a query.

Example:

Here is the result of my query. It counts a field called "DemographicID" that is not unique. It helps me identify age, education etc. Let's call this Query 1:

SELECT tblDemographicIDs.DemographicID, qryCountCandidatesYesDemoID.CountOfsubmissionkey, tblDemographicIDs.Target_Number
FROM tblDemographicIDs INNER JOIN qryCountCandidatesYesDemoID ON tblDemographicIDs.DemographicID = qryCountCandidatesYesDemoID.DemographicID
WHERE (((qryCountCandidatesYesDemoID.CountOfsubmissionkey)>=[tblDemographicIDs].[Target_Number]));

This returns

DemographicID	CountOfsubmissionkey	Target_Number
Iowa1	              1	                      1
Iowa12              3	                      3

When the CountOfsubmissionkey (counting the primary key) equals the target number, the DemographicID appears in this table.

Here is the second query. It is fed into a Microsoft Access form. Let's call this Query2:

 
SELECT tblTraining.First_Name, tblTraining.Last_Name, tblTraining.Contact, tblTraining.Contact_Time, tblTraining.Email_Address, 
tblTraining.City, tblTraining.DemographicID, tblTraining.Confirmed, tblTraining.Location_Date_Time, tblTraining.Last_Called, tblTraining.Notes
FROM tblTraining
WHERE (((tblTraining.Confirmed) Is Null Or (tblTraining.Confirmed)="") AND ((tblTraining.Location_Date_Time) Is Null Or (tblTraining.Location_Date_Time)=""))
ORDER BY Rnd(IsNull(tblTraining.submissionkey)*0+1);

Here's an excerpt of what it returns:


First_Name	     Last_Name	     Contact	     Contact_Time	     Email_Address	     City	   DemographicID	Confirmed	Location_Date_Time	Last_Called	Notes
Test Firstname18	Test Lastname18	319-335-0018	ContactTime18	email@domain.edu0018	City0018	Iowa18				
Test Firstname10	Test Lastname10	319-335-0010	ContactTime10	email@domain.edu0010	City0010	Iowa12				
Test Firstname13	Test Lastname13	319-335-0013	ContactTime13	email@domain.edu0013	City0013	Iowa13				
Test Firstname24	Test Lastname24	319-335-0024	ContactTime24	email@domain.edu0024	City0024	Iowa24

What I want is for my second query to filter the results so that if the DemographicID matches a DemographicID in the first query, it won't be displayed. For example, TestFirstName10 has a DemographicID of Iowa12 but Iowa12 is in Query1. I want to prevent that.
It should be noted that DemographicID is NOT unique and cannot be joined with another table.

Thanks.

donpolix
Yak Posting Veteran

97 Posts

Posted - 2007-12-28 : 14:54:33

add this to your query 2

...where tblTraining.DemographicID not in (
select DemographicID from (<...your query 1..>) t1

Donn Policarpio

jonhath
Starting Member

13 Posts

Posted - 2007-12-28 : 16:19:50

That did it, thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources