If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Fallowing rules of normalization you should put your Accounts Payable and Accounts Receivable in separate tables.

As for the Dynamic part, what do you mean? Do you want to ask a question before the query is run? If so you will need some kind of ODBC client to form the question. Is this going to be a Web App or an application programmed in something like VB, C++, or some .NET app?

For now I will just give you a Static Query assuming:

Field1
a1
Field2
AR
Field2
b1
Field4
USD
Field5
10

field names as above and that they are VarChar fields. You really should provide your schema for us to be more of a help. Here is the SQL...

My logic here is to eliminate both transactions for AR and AP accounts for 2 companies.

From my example; if there is an AR transaction between a1 and b1, then show the record which is 10 USD. Apart from that, if there is an AP transaction between b1 and a1, then show the record which is 15 USD. Then I will calculate the elimination which is -5 USD. IF one of the accounts are display, then the elimination value will be either 10 USD or 15 USD.

But there are lots of organizations and interco jumbled up from the source table which is why I need to sort the record before calculating the elimination.

I don't fully understand your problem. What do you mean by "interco"? "Indicators of Territorial Cohesion", "International Code of Signals", ...?

This code will give you the result that you gave in your first post.

I summarised all amounts for the same organization, account, interco and currency.
It puts all corresponding AR-AP's and with the same currency on one line and calculates the "elimination".

I have no idea what the rules for "IF one of the accounts are display, then the elimination value will be either 10 USD or 15 USD." are. Do you mean those records (organization, currency, 'AR') that cannot be linked with another record (interco, currency, 'AP')?

With kind regards . . . . . SQL Server 2000/2005/2012
Wim
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages