Problem with Dupl Query

  • Thread starter Thread starter SITCFanTN
  • Start date Start date
S

SITCFanTN

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])
 
For a simple downpay = totaldue in one record, the following should work:

SELECT *
FROM [tblAllRecords]
WHERE [Downpay]=[TotalDue] ;
 
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

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

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

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


SITCFanTN said:
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])
Click to expand...
Click to expand...
 
Try running the SQL statement I sent below. Is is close to doing what you want?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


SITCFanTN said:
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

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

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

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


SITCFanTN said:
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])
Click to expand...
Click to expand...
Click to expand...
 
Thanks so much, it worked great!

Jerry Whittle said:
Try running the SQL statement I sent below. Is is close to doing what you want?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


SITCFanTN said:
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

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

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

--
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
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])
Click to expand...
Click to expand...
Click to expand...
 
Back
Top