Duplicates that delete positive vs. negatives

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sleepless In the Natti - 19 Dec 2007 18:46 GMT

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.

Reply to this Message

John Spencer - 19 Dec 2007 19:48 GMT

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#]

Signature

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

>I have an Accounts Receivable program that I am trying to write a find dups
> query for.
[quoted text clipped - 10 lines]
>
> Any help would be appreciated.

Reply to this Message

Sleepless In the Natti - 20 Dec 2007 13:33 GMT

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
[quoted text clipped - 20 lines]
> >
> > Any help would be appreciated.

Reply to this Message

Sleepless In the Natti - 20 Dec 2007 13:41 GMT

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

> 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
[quoted text clipped - 36 lines]
> > >
> > > Any help would be appreciated.

Reply to this Message

John Spencer - 20 Dec 2007 13:57 GMT

> 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);

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.

Signature

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

>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
[quoted text clipped - 52 lines]
>> > >
>> > > Any help would be appreciated.

Reply to this Message

Sleepless In the Natti - 20 Dec 2007 14:06 GMT

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.

> > UPDATE tblArReport AS S
> > SET S.Paid = True
[quoted text clipped - 63 lines]
> >> > >
> >> > > Any help would be appreciated.

Reply to this Message