Problem with Dupl Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SITCFanTN - 16 Apr 2008 15:47 GMT

I have a table that has two columns, one is for "downpay" amount the other is
for "totaldue" amount. I'm trying to identify duplicate values to see who
has paid a downpayment amount equal to the total due amount. I created the
duplicate query the problem is it isn't working properly. The two columns
contain currency values. The query is matching values such as

Downpayment Total Due

$ 0.00 $100.00
$25.00 $1225.00

How can I get the query to match only exact matches, not partial. I looked
at other posts and didn't find this scenario. As always, I appreciate your
help, thank you

Oh here is the code the query builder created.

In (SELECT [Downpay] FROM [tblAllRecords] As Tmp GROUP BY
[Downpay],[TotalDue] HAVING Count(*)>1 And [TotalDue] =
[tblAllRecords].[TotalDue])

Reply to this Message

Jerry Whittle - 16 Apr 2008 16:22 GMT

For a simple downpay = totaldue in one record, the following should work:

SELECT *
FROM [tblAllRecords]
WHERE [Downpay]=[TotalDue] ;

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I have a table that has two columns, one is for "downpay" amount the other is
> for "totaldue" amount. I'm trying to identify duplicate values to see who
[quoted text clipped - 16 lines]
> [Downpay],[TotalDue] HAVING Count(*)>1 And [TotalDue] =
> [tblAllRecords].[TotalDue])

Reply to this Message

SITCFanTN - 16 Apr 2008 17:12 GMT

Thanks for your help Jerry but I'm really not sure what you are instructing
me to do. I don't know what you mean by "totaldue in one record". My table
has 300K records. Can you eleborate a bit more for me. Thanks

> For a simple downpay = totaldue in one record, the following should work:
>
[quoted text clipped - 22 lines]
> > [Downpay],[TotalDue] HAVING Count(*)>1 And [TotalDue] =
> > [tblAllRecords].[TotalDue])

Reply to this Message

Jerry Whittle - 16 Apr 2008 17:14 GMT

Try running the SQL statement I sent below. Is is close to doing what you want?

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Thanks for your help Jerry but I'm really not sure what you are instructing
> me to do. I don't know what you mean by "totaldue in one record". My table
[quoted text clipped - 26 lines]
> > > [Downpay],[TotalDue] HAVING Count(*)>1 And [TotalDue] =
> > > [tblAllRecords].[TotalDue])

Reply to this Message

SITCFanTN - 16 Apr 2008 17:51 GMT

Thanks so much, it worked great!

> Try running the SQL statement I sent below. Is is close to doing what you want?
>
[quoted text clipped - 28 lines]
> > > > [Downpay],[TotalDue] HAVING Count(*)>1 And [TotalDue] =
> > > > [tblAllRecords].[TotalDue])

Reply to this Message