Duplicates that delete positive vs. negatives

  • Thread starter Thread starter Sleepless In the Natti
  • Start date Start date
S

Sleepless In the Natti

I have an Accounts Receivable program that I am trying to write a find dups
query for.

I am keying from the Ref # which can have multiple invoices under each. I
am also pulling the amount of each invoice.

Fields - Ref# Invoice# and Invoice Amount

I want to be able to match a negative invoice amount to it's positive when
they appear under the same Ref#. Once dups are found it would need to update
a third field to say it was paid.

Any help would be appreciated.
 
UPDATE SomeTable AS S
SET S.FieldPaid = TRUE
WHERE Exists
(SELECT * FROM SomeTable as S2
WHERE S2.[Ref#] = S.[Ref#]
AND S2.[Invoice Amount] = S1.[Invoice Amount] * -1)

If you need to have Invoice# match you can add it to the exists clause
AND S2.[Invoice#] = S1.[Invoice#]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
So I put this into the SQL view of a query. I changed the fields and tables
into the actual fields and tables and it keeps asking me for input. I would
imagine somewhere I messed up your code. Please see below.

UPDATE tblArReport AS S
SET S.Paid = True
WHERE Exists
(SELECT Total FROM tblArReport as S2
WHERE S2.[EIRef] = S1.[EIRef]
AND S2.[Total] = S1.[Total] * -1);

Table - tblArReport
Fields - EIRef - EX-Factory Invoice - Text
Paid - Is Invoice Paid? - Yes/No
Total - Total of the invoice - Number-Double


John Spencer said:
UPDATE SomeTable AS S
SET S.FieldPaid = TRUE
WHERE Exists
(SELECT * FROM SomeTable as S2
WHERE S2.[Ref#] = S.[Ref#]
AND S2.[Invoice Amount] = S1.[Invoice Amount] * -1)

If you need to have Invoice# match you can add it to the exists clause
AND S2.[Invoice#] = S1.[Invoice#]

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

Sleepless In the Natti said:
I have an Accounts Receivable program that I am trying to write a find dups
query for.

I am keying from the Ref # which can have multiple invoices under each. I
am also pulling the amount of each invoice.

Fields - Ref# Invoice# and Invoice Amount

I want to be able to match a negative invoice amount to it's positive when
they appear under the same Ref#. Once dups are found it would need to
update
a third field to say it was paid.

Any help would be appreciated.
Click to expand...
Click to expand...
 
I just realized that the code I had in the program took your * out after
select and replaced it with total. I did this trying to figure out why it
was asking for input. I have now changed it back and it is still looking for
input. I have included the text from the message window below.

Header/ Enter Parameter Value
S1.EIRef
Field for text entry

Sleepless In the Natti said:
So I put this into the SQL view of a query. I changed the fields and tables
into the actual fields and tables and it keeps asking me for input. I would
imagine somewhere I messed up your code. Please see below.

UPDATE tblArReport AS S
SET S.Paid = True
WHERE Exists
(SELECT Total FROM tblArReport as S2
WHERE S2.[EIRef] = S1.[EIRef]
AND S2.[Total] = S1.[Total] * -1);

Table - tblArReport
Fields - EIRef - EX-Factory Invoice - Text
Paid - Is Invoice Paid? - Yes/No
Total - Total of the invoice - Number-Double


John Spencer said:
UPDATE SomeTable AS S
SET S.FieldPaid = TRUE
WHERE Exists
(SELECT * FROM SomeTable as S2
WHERE S2.[Ref#] = S.[Ref#]
AND S2.[Invoice Amount] = S1.[Invoice Amount] * -1)

If you need to have Invoice# match you can add it to the exists clause
AND S2.[Invoice#] = S1.[Invoice#]

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

Sleepless In the Natti said:
I have an Accounts Receivable program that I am trying to write a find dups
query for.

I am keying from the Ref # which can have multiple invoices under each. I
am also pulling the amount of each invoice.

Fields - Ref# Invoice# and Invoice Amount

I want to be able to match a negative invoice amount to it's positive when
they appear under the same Ref#. Once dups are found it would need to
update
a third field to say it was paid.

Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
UPDATE tblArReport AS S
SET S.Paid = True
WHERE Exists
(SELECT Total FROM tblArReport as S2
WHERE S2.[EIRef] = S1.[EIRef]
AND S2.[Total] = S1.[Total] * -1);
Click to expand...

You refer to S1 in the subquery. You should either change that to S or
change the alias in the first line to S1. If this still fails, then I will
propose an alternate solution.

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

Sleepless In the Natti said:
I just realized that the code I had in the program took your * out after
select and replaced it with total. I did this trying to figure out why it
was asking for input. I have now changed it back and it is still looking
for
input. I have included the text from the message window below.

Header/ Enter Parameter Value
S1.EIRef
Field for text entry

Sleepless In the Natti said:
So I put this into the SQL view of a query. I changed the fields and
tables
into the actual fields and tables and it keeps asking me for input. I
would
imagine somewhere I messed up your code. Please see below.

UPDATE tblArReport AS S
SET S.Paid = True
WHERE Exists
(SELECT Total FROM tblArReport as S2
WHERE S2.[EIRef] = S1.[EIRef]
AND S2.[Total] = S1.[Total] * -1);

Table - tblArReport
Fields - EIRef - EX-Factory Invoice - Text
Paid - Is Invoice Paid? - Yes/No
Total - Total of the invoice - Number-Double


John Spencer said:
UPDATE SomeTable AS S
SET S.FieldPaid = TRUE
WHERE Exists
(SELECT * FROM SomeTable as S2
WHERE S2.[Ref#] = S.[Ref#]
AND S2.[Invoice Amount] = S1.[Invoice Amount] * -1)

If you need to have Invoice# match you can add it to the exists clause
AND S2.[Invoice#] = S1.[Invoice#]

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

"Sleepless In the Natti" <Sleepless In the
(e-mail address removed)>
wrote in message
I have an Accounts Receivable program that I am trying to write a find
dups
query for.

I am keying from the Ref # which can have multiple invoices under
each. I
am also pulling the amount of each invoice.

Fields - Ref# Invoice# and Invoice Amount

I want to be able to match a negative invoice amount to it's positive
when
they appear under the same Ref#. Once dups are found it would need
to
update
a third field to say it was paid.

Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Well Mr. John Spencer you are the man!!! Everything works well now. Thank
You for your help. I was actually messing about in this and started to
understand the code and now understand when that was. Thank You again.

John Spencer said:
UPDATE tblArReport AS S
SET S.Paid = True
WHERE Exists
(SELECT Total FROM tblArReport as S2
WHERE S2.[EIRef] = S1.[EIRef]
AND S2.[Total] = S1.[Total] * -1);
Click to expand...

You refer to S1 in the subquery. You should either change that to S or
change the alias in the first line to S1. If this still fails, then I will
propose an alternate solution.

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

Sleepless In the Natti said:
I just realized that the code I had in the program took your * out after
select and replaced it with total. I did this trying to figure out why it
was asking for input. I have now changed it back and it is still looking
for
input. I have included the text from the message window below.

Header/ Enter Parameter Value
S1.EIRef
Field for text entry

Sleepless In the Natti said:
So I put this into the SQL view of a query. I changed the fields and
tables
into the actual fields and tables and it keeps asking me for input. I
would
imagine somewhere I messed up your code. Please see below.

UPDATE tblArReport AS S
SET S.Paid = True
WHERE Exists
(SELECT Total FROM tblArReport as S2
WHERE S2.[EIRef] = S1.[EIRef]
AND S2.[Total] = S1.[Total] * -1);

Table - tblArReport
Fields - EIRef - EX-Factory Invoice - Text
Paid - Is Invoice Paid? - Yes/No
Total - Total of the invoice - Number-Double


:

UPDATE SomeTable AS S
SET S.FieldPaid = TRUE
WHERE Exists
(SELECT * FROM SomeTable as S2
WHERE S2.[Ref#] = S.[Ref#]
AND S2.[Invoice Amount] = S1.[Invoice Amount] * -1)

If you need to have Invoice# match you can add it to the exists clause
AND S2.[Invoice#] = S1.[Invoice#]

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

"Sleepless In the Natti" <Sleepless In the
(e-mail address removed)>
wrote in message
I have an Accounts Receivable program that I am trying to write a find
dups
query for.

I am keying from the Ref # which can have multiple invoices under
each. I
am also pulling the amount of each invoice.

Fields - Ref# Invoice# and Invoice Amount

I want to be able to match a negative invoice amount to it's positive
when
they appear under the same Ref#. Once dups are found it would need
to
update
a third field to say it was paid.

Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top