SQL and Question

[Guest]

Hello:

Here's my code:

SELECT TblDL.SecNm, TblDL.CUS, TblXZ.SED, TblDL.Ex, TblDL.Pay, TblDL.Grate,
TblXZ.CUS, TblXZ.Ex, TblXZ.Pay, TblXZ.Grate, TblDL.Ctry, TblDL.Stat
FROM TblDL INNER JOIN TblXZ ON TblDL.CUS = TblXZ.CUS
WHERE (((TblDL.CUS)=[TblXZ].[CUS]) AND ((TblDL.Grate)<>[TblXZ].[Grate]));

Here's my question:
TblDL.Grate and TblXZ.Grate are rates that can be up to six decimal places
long. When these come to within three decimal places I want the query to
recognize it as a match and not report it in the query. Right now a TblDL
rate of 2.3257 and a TblXZ rate of 2.32568 are recognized as mismatches. Any
help with this is greatly appreciated.

Robert

 

[Guest]

Format([TblDL].[Grate], "0.000") <> Format([TblXZ].[Grate], "0.000")

However there is a rounding issue. The data in your examples both round up
to 2.326. Can you live with that?

 

[John Spencer]

I would try the following

SELECT TblDL.SecNm, TblDL.CUS, TblXZ.SED, TblDL.Ex, TblDL.Pay, TblDL.Grate,
TblXZ.CUS, TblXZ.Ex, TblXZ.Pay, TblXZ.Grate, TblDL.Ctry, TblDL.Stat
FROM TblDL INNER JOIN TblXZ ON TblDL.CUS = TblXZ.CUS
WHERE TblDL.CUS=[TblXZ].[CUS]
AND Abs(TblDL.Grate-TblXZ.Grate) >= .001

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Hello Jerry:

Thank you, I used your code. From what I see this will work by formatting
the values on both sides to round them down. This is just what I needed.

Format([TblDL].[Grate], "0.000") <> Format([TblXZ].[Grate], "0.000")

However there is a rounding issue. The data in your examples both round up
to 2.326. Can you live with that?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello:

Here's my code:

SELECT TblDL.SecNm, TblDL.CUS, TblXZ.SED, TblDL.Ex, TblDL.Pay, TblDL.Grate,
TblXZ.CUS, TblXZ.Ex, TblXZ.Pay, TblXZ.Grate, TblDL.Ctry, TblDL.Stat
FROM TblDL INNER JOIN TblXZ ON TblDL.CUS = TblXZ.CUS
WHERE (((TblDL.CUS)=[TblXZ].[CUS]) AND ((TblDL.Grate)<>[TblXZ].[Grate]));

Here's my question:
TblDL.Grate and TblXZ.Grate are rates that can be up to six decimal places
long. When these come to within three decimal places I want the query to
recognize it as a match and not report it in the query. Right now a TblDL
rate of 2.3257 and a TblXZ rate of 2.32568 are recognized as mismatches. Any
help with this is greatly appreciated.

Robert

 

[John W. Vinson]

TblDL.Grate and TblXZ.Grate are rates that can be up to six decimal places
long. When these come to within three decimal places I want the query to
recognize it as a match and not report it in the query. Right now a TblDL
rate of 2.3257 and a TblXZ rate of 2.32568 are recognized as mismatches. Any
help with this is greatly appreciated.

What's the datatype of Grate? Double or Decimal with six places would be
prudent.

To get a "fuzzy" match within +/- .001, use

SELECT TblDL.SecNm, TblDL.CUS, TblXZ.SED, TblDL.Ex, TblDL.Pay, TblDL.Grate,
TblXZ.CUS, TblXZ.Ex, TblXZ.Pay, TblXZ.Grate, TblDL.Ctry, TblDL.Stat
FROM TblDL INNER JOIN TblXZ ON TblDL.CUS = TblXZ.CUS
WHERE TblDL.CUS=[TblXZ].[CUS] AND Abs(TblDL.Grate-[TblXZ].[Grate]) <= .001;


John W. Vinson [MVP]