max of aboslute value in query

[odun]

Hello all,

Your urgent assistance is required with the following:

I have 2 tables

Table1 has a few fields that Table2 does not.

The common field between the 2 tables is Docno.

an example of fields in table2 is:

Center Amount DocNo
158 -8500 1
159 -6522 1
156 8900 2
157 100 2

one of the fields in Table1 for example:

DocNo
1
2

I would like to join the 2 tables such that all the fields in table1 will be included as well as the Center field in table2, but here is what I am currently getting:

Table 1 Query
Docno Center
1 158
1 159
2 156
2 157

But I want only 1 center per docNo but the way to choose the center is to get the largest absolute value of the Amount field.

So the Table1 Query should look like this:

Docno Center
1 158
2 156

Because for docno "1" for example 8500 is greater than 6522 (in absolute value) terms. and so the profit center corresponding to the 8500 is 158.

I am sure it is possible to do this, just don't know what to design the query. Please help!

 

[mresann]

Try doing the query again, but click the SUM button at the top of the query menu. It looks like an "Σ". Next, pick the fields Docno and Center. On Center, on Total change Group By to Max. Run the query.

It should show you the data you are looking for.

 

[odun]

Hi Mresann

Thanks much for replying. I think I didn't explain clearly enough.
I want to pick the "center" relating to a particular "docno" when the relating "amount" is the largest absolute dollar, e.g.

Center/Amount/DocNo
158/-8500/1
159/-6522/1
156/8900/2
157/100/2

So for Docno/Center
1/158 because 8500 is greater than 6522 in absolute $
2/156 because 8900 is greater than 100 and also in absolute $

Please help. Thanks.