Text Comparison in Query Help

[Sandcreek]

Hi all -

I'm trying to understand an issue I'm having with a particular query and was hoping someone could explain. When comparing fields between two tables, on text field comparison returns results if the two text fields are specified to be equal. However, if I set them to not equal each other "<>", the query returns no results which I don't expect and have validated a control set of data to ensure there should be results.

Here are the two queries.

Matching - returns 2 rows as expected.

SELECT tbl_PAL_Current.[Short Description (30)], tbl_PAL_Current.[Manufacturer's Item ID], PMPROD_PM_ITEM_MFG.ITEM_ID AS PM_MFG_ITEM_ID, PMPROD_PM_ITEM_MFG.MANUFACTURER_ITEM_ID, PMPROD_PM_ITEM_MFG.ITEM_ID
FROM tbl_PAL_Current INNER JOIN PMPROD_PM_ITEM_MFG ON tbl_PAL_Current.[Manufacturer's Item ID] = PMPROD_PM_ITEM_MFG.MANUFACTURER_ITEM_ID
WHERE (((PMPROD_PM_ITEM_MFG.ITEM_ID)=[tbl_PAL_Current].[Item ID]));


Not matching - returns 0 rows, but expect 11.

SELECT tbl_PAL_Current.[Short Description (30)], tbl_PAL_Current.[Manufacturer's Item ID], PMPROD_PM_ITEM_MFG.ITEM_ID AS PM_MFG_ITEM_ID, PMPROD_PM_ITEM_MFG.MANUFACTURER_ITEM_ID, PMPROD_PM_ITEM_MFG.ITEM_ID
FROM tbl_PAL_Current INNER JOIN PMPROD_PM_ITEM_MFG ON tbl_PAL_Current.[Manufacturer's Item ID] = PMPROD_PM_ITEM_MFG.MANUFACTURER_ITEM_ID
WHERE (((PMPROD_PM_ITEM_MFG.ITEM_ID)<>[tbl_PAL_Current].[Item ID]));

Both Item_ID and Item ID are text fields, although field size is different (which I don't think should impact the results).

Any thoughts?!

Thanks,
Sandcreek

 

[Eupher]

Hi, Sandcreek,

Have you tried the "Not" keyword instead of the "<>" operator? Some operators work in VBA expressions but not Jet SQL.

Ken S.

 

[Eupher]

i.e.

WHERE [red][b]Not[/b][/red](((PMPROD_PM_ITEM_MFG.ITEM_ID)=[tbl_PAL_Current].[Item ID]))

HTH,

Ken S.

 

[Eupher]

Ack! Stupid query builder goes crazy with parentheses... Try this:

WHERE [red][b]Not[/b][/red](PMPROD_PM_ITEM_MFG.ITEM_ID)=[tbl_PAL_Current].[Item ID]

(Either should work)

Ken S.

 

[Sandcreek]

Eupher -

Thanks for the suggestion, I tried it and still returns the zero result set. I'll take any alternative suggestions to arrive at th same results, just need to get down to the non-matching rows from the two tables.

Thanks,
Sandcreek

 

[Eupher]

Seems I posted in haste. "<>" is a valid SQL operator.

Still searching...

Ken S.

 

[Eupher]

Sandcreek,

Can you post your table structure(s) and some sample data?

BTW, I presume you tried the "Find unmatched" query wizard?

Ken S.

 

[Sandcreek]

Eupher -

I have not tried the "Find unmatched" wizard, wasn't even aware of it. Just tried and no luck, I'll play around with it some more.

tbl_PAL_Current:
ITEM ID Manufacturer's Item ID Description
1000064204 970403-1 FEE 600i ENGINEERING
1000064205 970403-2 FEE 600i INSTALLATION
970403-3 FEE 600i INSTALL MATERIALS

PMPROD_PM_ITEM_MFG:
ITEM_ID Manufacturer's Item ID
1000064204 970403-1
1000064205 970403-2
1000064209 970403-3

Each table has several other rows, but I really only care about the few described above. What I'd like back in the result set is the 970403-3 value only (with the PMPROD_PM_ITEM_MFG.ITEM_ID value) in the above example and not the two values with both matching "Item ID" and "Manufacturer's Item ID" from the table tbl_PAL_Current.

Thanks,
Sandcreek

 

[Sandcreek]

Eupher -

Thanks to your direction I came up with a solution that used a query similar to that of the "find unmatched" wizard to facilitate a join with the IS NULL parameter. In order for it to work I had to create a separate table with both Item ID and ITEM_ID in the same table structure and then join to the original tbl_PAL_Current.

Appreciate you time and efforts.

- Sandcreek

 

[PHV]

Why a new table ?
SELECT C.[Short Description (30)], C.[Manufacturer's Item ID], I.ITEM_ID AS PM_MFG_ITEM_ID, I.MANUFACTURER_ITEM_ID, I.ITEM_ID
FROM tbl_PAL_Current AS C INNER JOIN PMPROD_PM_ITEM_MFG AS I ON C.[Manufacturer's Item ID] = I.MANUFACTURER_ITEM_ID
WHERE Nz(I.ITEM_ID,'')<>Nz(C.[Item ID],'');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Sandcreek]

PH -

Slick query (returned the results I was looking for). Can you help me understand the syntax in the WHERE statement, I'm not familiar with the Nz and the peculiar placement of the '' (indicating a non-blank I assume) in the statement.

Thanks,
Sandcreek

 

[Eupher]

Sandcreek,

Nz is the null zero function. It replaces a null value in an expression with a value of your choosing, often zero, hence the name of the function. In PHV's example, nulls are being replaced by a pair of single quotes to denote a zero-length string.

HTH,

Ken S.