find exact match or match those only few cents off

[hoachen]

Table1
item# CustID Dep$
1234A 111 10.15
2554S 222 7.75
1232B 111 8.49

Table2
item# CustID Return$
1234A 111 10.15
1232B 111 8.47


Right now, I use Left join on all these fields (it have more fields but I
shortern it). But if the Cents (Table1 1232B have 8.49 and Table2 have 8.47)
did not match exactly then I would not be able to varify the item has been
return
Is there anywhere I can do like if match exact the same, then display or if
only one to ten cents different it will display also.
The above result will only display:

The result should be display below even though there were two cents
different:

1234A 111 10.15
1232B 111 8.47

I am not know much about IIF function. I believe it can do it, can anyone
show me how to accomplish this?
Thanks

 

[Jeff Boyce]

If you'll define how close is close enough, you can use a query to see if
one table's value is "close" to the other table's value.

Take a look at using Between xxxx And yyyy as a selection criterion.

Or calculate "rounded" values (if 5 cents is close enough) and compare them.

Or simply take the difference and check to see if it is larger than ... {you
decide how "close}".

(but if you have the same itemID and CustID, why do you care if the amounts
are "close". Would it be enough to calculate the difference?)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Daryl S]

Hoachen -

Yes, this can be done, but I would worry about multiple matches occurring...
I also don't like the special characters in the field names...

To do what you want, make the query an inner join, but don't join on the
Dep$ = Return$ at all. Then add a field in your query for ABS([Dep$] -
[Return$]), and set the criteria to <=0.10.

You can test this, and then uncheck the 'Show' box for this new field if you
don't want to see it.

 

[hoachen]

it works, thank you very much

Hoachen -

Yes, this can be done, but I would worry about multiple matches occurring...
I also don't like the special characters in the field names...

To do what you want, make the query an inner join, but don't join on the
Dep$ = Return$ at all. Then add a field in your query for ABS([Dep$] -
[Return$]), and set the criteria to <=0.10.

You can test this, and then uncheck the 'Show' box for this new field if you
don't want to see it.

--
Daryl S

Table1
item# CustID Dep$
1234A 111 10.15
2554S 222 7.75
1232B 111 8.49

Table2
item# CustID Return$
1234A 111 10.15
1232B 111 8.47


Right now, I use Left join on all these fields (it have more fields but I
shortern it). But if the Cents (Table1 1232B have 8.49 and Table2 have 8.47)
did not match exactly then I would not be able to varify the item has been
return
Is there anywhere I can do like if match exact the same, then display or if
only one to ten cents different it will display also.
The above result will only display:

The result should be display below even though there were two cents
different:

1234A 111 10.15
1232B 111 8.47

I am not know much about IIF function. I believe it can do it, can anyone
show me how to accomplish this?
Thanks