Conditional query with exclusion

[Deutz]

Hi and thanks in advance,

I have a single table in Access 2010 with about 500,000 client records that I am trying to query. Each client ID can appear in the table multiple times with different letter codes from S to Z. I want to return a list of distinct client IDs where the client has code X but has never had code Y. Not sure if I should use something like EXISTS or IN in some kind of subquery.

Thanks
Deutz

 

[Deutz]

I have subsequently tried this SQL but it seems to hang forever ...

SELECT DISTINCT tblClient.fldID

FROM tblClient

WHERE tblClient.fldID NOT IN (SELECT tblClient.fldID FROM tblClient WHERE tblClient.fldCode="Y") AND tblClient.fldCode="X"

 

[sneuberg]

You can make a query that gives you the distinct user ids for code x by using the DISTINCT keyword in the SELECY clause., something like

SELECT DISTINCT CliientID WHERE Code = 'X'

save that query and make one for code y like

SELECT DISTINCT CliientID WHERE Code = 'Y'

Sve that too and then you can make an unmatched query using the query wizard or do it yourself by making the second query a subquery and use it like:

SELECT DISTINCT CliientID WHERE Code = 'X' AND ClientID Not In (SELECT DISTINCT CliientID WHERE Code = 'Y')


I find this site I good source for understand subqueries.

 

[jdraw]

This may help (tested on a small set of data). You'll have to change the names to match your table.

SELECT fldId
	,fldcode
FROM flds
WHERE fldid IN (
		SELECT DISTINCT flds.fldID
		FROM flds
		WHERE flds.fldID NOT IN (
				SELECT fldID
				FROM flds AS HH
				WHERE hh.fldCode = "Y"
				)
		)
	AND fldcode = "X"

OOOps: I see sneuberg has responded.

 

[sneuberg]

I've attached a database with an unmatched query made of queries I outlined before. This uses an outer join between the queries like

SELECT qryDistinctX.fldID, qryDistinctY.fldID
FROM qryDistinctX LEFT JOIN qryDistinctY ON qryDistinctX.fldID = qryDistinctY.fldID
WHERE (((qryDistinctY.fldID) Is Null));

You will probably have to look at the database to make sense of this. I think this might be a lot faster. I believe this is about what the query wizard would create.

 

[Deutz]

Thanks for your suggestion sneuberg. I'll give it try.

Regards
Deutz

 

[Deutz]

Thanks sneuberg, that works very well.

Much appreciated

Regards
Deutz

 

[Deutz]

Also, thanks to JDraw,

I tested your suggestion also but since I have a lousy work PC, the query takes a bit too long to run. I'm sure it would be just fine on a better machine.

Regards
Deutz