Unmatched record query help

G

Guest

I'm trying to create a query that lists records in the AllNewParts tbl where
RefPart/RefPart NHL don't have a matching Part#/AS Part# in the
ExcelARefParts table. The following fields need to match:

AllNewParts tbl ExcelARefParts tbl
Model Model
RefPart Part #
RefPart NHL AS Part #

I've tried the following query, but it's listing several null RefPart fields
in the AllNewParts tbl. Thanks for you help.

SELECT AllNewParts.[Model#], AllNewParts.RefPart, ExcelARefParts.[Part#]
FROM AllNewParts LEFT JOIN ExcelARefParts ON (AllNewParts.RefPartNHL =
ExcelARefParts.[AS Part#]) AND (AllNewParts.RefPart = ExcelARefParts.[Part#])
AND (AllNewParts.[Model#] = ExcelARefParts.[Model#])
WHERE (((AllNewParts.RefPart) Is Not Null) AND ((ExcelARefParts.[Part#]) Is
Null));
 
G

Guest

One way would be to use the NOT EXISTS predicate and a correlated subquery :

SELECT *
FROM AllNewParts
WHERE RefPart IS NOT NULL
AND NOT EXISTS
(SELECT *
FROM ExcelARefParts
WHERE ExcelARefParts.Model = AllNewParts.Model
AND ExcelARefParts.[Part #] = AllNewParts.RefPart
AND ExcelARefParts.[AS Part #] = AllNewParts.[RefPartNHL];

BTW you've shown the RefPartNHL column both with and without a space. I've
assumed the latter.

Ken Sheridan
Stafford, England
 
G

Guest

Are you sure they are NULL and not zero length strings? The IS NOT NULL
criterion should eliminate any rows with NULL at the RefPart column position.
Try executing the following:

UPDATE AllNewParts
SET RefPart = NULL
WHERE LEN(TRIM(RefPart)) = 0;

Back up the table first of course.

If it does the trick amend the table definition to disallow zero-length
strings.

Ken Sheridan
Stafford, England

Alex said:
I got this to run without parameters, but it still gives me all the records
where AllNewParts.Refpart <> ExcelARefPart.Part# and I don't want to see the
records where AllNewParts.Refpart is null. I used that query to create a sub
query where RefPart is not null, but it still gives me records where RefPart
is null. Any ideas?

Ken Sheridan said:
One way would be to use the NOT EXISTS predicate and a correlated subquery :

SELECT *
FROM AllNewParts
WHERE RefPart IS NOT NULL
AND NOT EXISTS
(SELECT *
FROM ExcelARefParts
WHERE ExcelARefParts.Model = AllNewParts.Model
AND ExcelARefParts.[Part #] = AllNewParts.RefPart
AND ExcelARefParts.[AS Part #] = AllNewParts.[RefPartNHL];

BTW you've shown the RefPartNHL column both with and without a space. I've
assumed the latter.

Ken Sheridan
Stafford, England

Alex said:
I'm trying to create a query that lists records in the AllNewParts tbl where
RefPart/RefPart NHL don't have a matching Part#/AS Part# in the
ExcelARefParts table. The following fields need to match:

AllNewParts tbl ExcelARefParts tbl
Model Model
RefPart Part #
RefPart NHL AS Part #

I've tried the following query, but it's listing several null RefPart fields
in the AllNewParts tbl. Thanks for you help.

SELECT AllNewParts.[Model#], AllNewParts.RefPart, ExcelARefParts.[Part#]
FROM AllNewParts LEFT JOIN ExcelARefParts ON (AllNewParts.RefPartNHL =
ExcelARefParts.[AS Part#]) AND (AllNewParts.RefPart = ExcelARefParts.[Part#])
AND (AllNewParts.[Model#] = ExcelARefParts.[Model#])
WHERE (((AllNewParts.RefPart) Is Not Null) AND ((ExcelARefParts.[Part#]) Is
Null));
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Never mind Ken. I have other issues. For some reason, what appear to be
null RefPart fields are not really null. I'll figure it out and then I'm
sure your suggestion will work. Thanks much.

Ken Sheridan said:
One way would be to use the NOT EXISTS predicate and a correlated subquery :

SELECT *
FROM AllNewParts
WHERE RefPart IS NOT NULL
AND NOT EXISTS
(SELECT *
FROM ExcelARefParts
WHERE ExcelARefParts.Model = AllNewParts.Model
AND ExcelARefParts.[Part #] = AllNewParts.RefPart
AND ExcelARefParts.[AS Part #] = AllNewParts.[RefPartNHL];

BTW you've shown the RefPartNHL column both with and without a space. I've
assumed the latter.

Ken Sheridan
Stafford, England

Alex said:
I'm trying to create a query that lists records in the AllNewParts tbl where
RefPart/RefPart NHL don't have a matching Part#/AS Part# in the
ExcelARefParts table. The following fields need to match:

AllNewParts tbl ExcelARefParts tbl
Model Model
RefPart Part #
RefPart NHL AS Part #

I've tried the following query, but it's listing several null RefPart fields
in the AllNewParts tbl. Thanks for you help.

SELECT AllNewParts.[Model#], AllNewParts.RefPart, ExcelARefParts.[Part#]
FROM AllNewParts LEFT JOIN ExcelARefParts ON (AllNewParts.RefPartNHL =
ExcelARefParts.[AS Part#]) AND (AllNewParts.RefPart = ExcelARefParts.[Part#])
AND (AllNewParts.[Model#] = ExcelARefParts.[Model#])
WHERE (((AllNewParts.RefPart) Is Not Null) AND ((ExcelARefParts.[Part#]) Is
Null));
Click to expand...
Click to expand...
 
G

Guest

This looks like what I need, but I pasted it into a query SQL and it keeps
giving me a parameter for all the fields when I run it. What am I doing
wrong? Thanks.

Ken Sheridan said:
One way would be to use the NOT EXISTS predicate and a correlated subquery :

SELECT *
FROM AllNewParts
WHERE RefPart IS NOT NULL
AND NOT EXISTS
(SELECT *
FROM ExcelARefParts
WHERE ExcelARefParts.Model = AllNewParts.Model
AND ExcelARefParts.[Part #] = AllNewParts.RefPart
AND ExcelARefParts.[AS Part #] = AllNewParts.[RefPartNHL];

BTW you've shown the RefPartNHL column both with and without a space. I've
assumed the latter.

Ken Sheridan
Stafford, England

Alex said:
I'm trying to create a query that lists records in the AllNewParts tbl where
RefPart/RefPart NHL don't have a matching Part#/AS Part# in the
ExcelARefParts table. The following fields need to match:

AllNewParts tbl ExcelARefParts tbl
Model Model
RefPart Part #
RefPart NHL AS Part #

I've tried the following query, but it's listing several null RefPart fields
in the AllNewParts tbl. Thanks for you help.

SELECT AllNewParts.[Model#], AllNewParts.RefPart, ExcelARefParts.[Part#]
FROM AllNewParts LEFT JOIN ExcelARefParts ON (AllNewParts.RefPartNHL =
ExcelARefParts.[AS Part#]) AND (AllNewParts.RefPart = ExcelARefParts.[Part#])
AND (AllNewParts.[Model#] = ExcelARefParts.[Model#])
WHERE (((AllNewParts.RefPart) Is Not Null) AND ((ExcelARefParts.[Part#]) Is
Null));
Click to expand...
Click to expand...
 
G

Guest

I got this to run without parameters, but it still gives me all the records
where AllNewParts.Refpart <> ExcelARefPart.Part# and I don't want to see the
records where AllNewParts.Refpart is null. I used that query to create a sub
query where RefPart is not null, but it still gives me records where RefPart
is null. Any ideas?

Ken Sheridan said:
One way would be to use the NOT EXISTS predicate and a correlated subquery :

SELECT *
FROM AllNewParts
WHERE RefPart IS NOT NULL
AND NOT EXISTS
(SELECT *
FROM ExcelARefParts
WHERE ExcelARefParts.Model = AllNewParts.Model
AND ExcelARefParts.[Part #] = AllNewParts.RefPart
AND ExcelARefParts.[AS Part #] = AllNewParts.[RefPartNHL];

BTW you've shown the RefPartNHL column both with and without a space. I've
assumed the latter.

Ken Sheridan
Stafford, England

Alex said:
I'm trying to create a query that lists records in the AllNewParts tbl where
RefPart/RefPart NHL don't have a matching Part#/AS Part# in the
ExcelARefParts table. The following fields need to match:

AllNewParts tbl ExcelARefParts tbl
Model Model
RefPart Part #
RefPart NHL AS Part #

I've tried the following query, but it's listing several null RefPart fields
in the AllNewParts tbl. Thanks for you help.

SELECT AllNewParts.[Model#], AllNewParts.RefPart, ExcelARefParts.[Part#]
FROM AllNewParts LEFT JOIN ExcelARefParts ON (AllNewParts.RefPartNHL =
ExcelARefParts.[AS Part#]) AND (AllNewParts.RefPart = ExcelARefParts.[Part#])
AND (AllNewParts.[Model#] = ExcelARefParts.[Model#])
WHERE (((AllNewParts.RefPart) Is Not Null) AND ((ExcelARefParts.[Part#]) Is
Null));
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Query Help 1
Update value to null 1
Help with Update query SQL 2
Multiple Criteria in Combo Box Dlookup 1
Setvalue expression too long 2
sql delete syntax 1
Do Until Loop help 1
Help setting up table structure 4

Top