to modify a query in access

Question

Hi,

I have a query which gives a result like this

field1  field2  field3  field4  field5
23      gfhf    ghjj       5        49
23      ghf     jkll       6        45
67      bnvn    nmb        7        45
89      gfh     hjj        8        78
89      gfhg    hk         9        23

Here the first 2 and last 2 records are kind of same. I want only one based on field 4 and field 5. i.e whichever has the minimum value in field 4 and maximum in field 5.

I want the following result

field1  field2  field3  field4  field5
23      gfhf    ghjj       5        49
67      bnvn    nmb        7        45
89      gfh     hjj        8        78

Thanks

Answer 1

In order to get your ordering correct you will need to create a new query which takes the input as the your existing query.

To make it easiest adjust your current query so the result are ordered in the way you wish, with field4 Ascending and then field5 Descending

Create a new query selecting all the values from your existing query

In the designer for this new query, right click and add totals Select Group By for field1 and then First for all the other ones

This should do what you want

Answer 2

First establish the minimum value for field 4 and the maximum value for field 5 that you want to associate with field 1.

SELECT field1, Min(field4) AS MinField4, Max(field5) AS MaxField5
FROM Table1
GROUP BY field1;

Save this query as, say Query1 then join it back onto Table1 to get the values for field2 and field 3.

SELECT Query1.field1, Table1.field2, Table1.field3, Query1.MinField4,
       Query1.MaxField5
FROM   Query1 INNER JOIN Table1 ON (Query1.field1 = Table1.field1) AND
       (Query1.MinField4 = Table1.field4) AND
       (Query1.MaxField5 = Table1.field5);