Help with this query!

[Patrick]

HI!!

I need help in figuring why the following query isn't
returning exactly what I need.

SELECT so_code, FY, ID, manufacturer
FROM [t 97/98 Data] 'tbl1
WHERE (so_code &"-" & fy & "-" & id) not in(SELECT
orderNbr from [t warranty]) ' tbl2

If I compare the result of this query for Order number:
'9999-97-003' there in each of the 2 tables. Which is not
good..
What I want his:
To locate the Order # (so_code &"-" & fy & "-" & id) )
from tbl1 that doesn't exist in tbl2.

The problem is that tbl1 order # in in 3 segments, where
as, in tbl2 its one segement.

This query would help me to identify any order # from tbl1
not presently entered in my warranty table.

Does anyone see a problem with this query...
Any help or suggestions will be appriciated!!

PAtrick

 

[John Viescas]

Patrick-

What are the data types of so_code, fy, and id? If, for example, id is
numeric, you'll need to do:

WHERE so_code & "-" & fy & "-" & Format(id, "000") NOT IN ...

This forces the leading zeros you need to get an exact match on orderNbr.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Patrick]

That's what the problem was, thanks a million!!!

-----Original Message-----
Patrick-

What are the data types of so_code, fy, and id? If, for example, id is
numeric, you'll need to do:

WHERE so_code & "-" & fy & "-" & Format(id, "000") NOT IN ...

This forces the leading zeros you need to get an exact match on orderNbr.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

HI!!

I need help in figuring why the following query isn't
returning exactly what I need.

SELECT so_code, FY, ID, manufacturer
FROM [t 97/98 Data] 'tbl1
WHERE (so_code &"-" & fy & "-" & id) not in(SELECT
orderNbr from [t warranty]) ' tbl2

If I compare the result of this query for Order number:
'9999-97-003' there in each of the 2 tables. Which is not
good..
What I want his:
To locate the Order # (so_code &"-" & fy & "-" & id) )
from tbl1 that doesn't exist in tbl2.

The problem is that tbl1 order # in in 3 segments, where
as, in tbl2 its one segement.

This query would help me to identify any order # from tbl1
not presently entered in my warranty table.

Does anyone see a problem with this query...
Any help or suggestions will be appriciated!!

PAtrick

.

 

[John Vinson]

HI!!

I need help in figuring why the following query isn't
returning exactly what I need.

What is it returning that it shouldn't, or vice versa?

SELECT so_code, FY, ID, manufacturer
FROM [t 97/98 Data] 'tbl1
WHERE (so_code &"-" & fy & "-" & id) not in(SELECT
orderNbr from [t warranty]) ' tbl2

If I compare the result of this query for Order number:
'9999-97-003' there in each of the 2 tables. Which is not
good..
What I want his:
To locate the Order # (so_code &"-" & fy & "-" & id) )
from tbl1 that doesn't exist in tbl2.

If ID is a Number field you'll lose the leading zeros: try

[So_code] & "-" & [fy] & "-" & Format([ID], "000")

If any of the other fields are numeric you'll need to do the same
thing: concatenation will NOT honor format properties of fields.