Matching Inv & Credit Note

[Guest]

Hi

I have a table with the following fields Code, Date, Amount, URN
it is an extract from our accounting system. Code is the supplier code and
URN is the Unique Reference Number.

I would like to be able to run a query which would identify invoices which
have paid but then have been reversed off again. For example there could be 2
payments for a certain supplier one week but one of them could have been
reversed for some reason. Basically the Code would be the same for both
invoice, URN would be different and amount would be the same but a negative
number. Can someone suggest a way of accomplishing this?

Thanks

 

[John Spencer (MVP)]

The following may do what you ask. Obviously Replace "Table" with the actual
table name


SELECT Table.URN, Table.Code, Table.[Date], Table.Amount
FROM Table
WHERE URN IN (
SELECT URN
FROM Table as C1 INNER JOIN Table as C2
ON C1.Code = C2.Code
WHERE CCur(C1.Amount) + CCur(C2.Amount) = 0)

This may have errors if the Amount field is not always filled in

 

[Guest]

Thanks John....worked perfectly.

The following may do what you ask. Obviously Replace "Table" with the actual
table name


SELECT Table.URN, Table.Code, Table.[Date], Table.Amount
FROM Table
WHERE URN IN (
SELECT URN
FROM Table as C1 INNER JOIN Table as C2
ON C1.Code = C2.Code
WHERE CCur(C1.Amount) + CCur(C2.Amount) = 0)

This may have errors if the Amount field is not always filled in

Hi

I have a table with the following fields Code, Date, Amount, URN
it is an extract from our accounting system. Code is the supplier code and
URN is the Unique Reference Number.

I would like to be able to run a query which would identify invoices which
have paid but then have been reversed off again. For example there could be 2
payments for a certain supplier one week but one of them could have been
reversed for some reason. Basically the Code would be the same for both
invoice, URN would be different and amount would be the same but a negative
number. Can someone suggest a way of accomplishing this?

Thanks