displaying multiple fields without affecting group by

[mystraightroad]

any help with following much appreciated

Two tables, linked on ID

Table1
ID CODE
==========
1 AA
2 BB
3 AA

TABLE-2
ID ITEM DESC AMOUNT
========================
1 1 X05 0
1 2 Y07 5
1 3 V73 1
1 4 K94 4
1 5 VF5 0
2 1 NG1 3
2 2 XD2 2
2 3 C53 7
2 4 V84 1
2 5 V35 2
3 1 K91 4
3 2 A22 2
3 3 V73 4
3 4 O94 1
3 5 S65 2

right, what I'm trying to do is to pull the highest VALUE from the
first three records (based on Table2-ID), unless the first three
records contain a VALUE of 0, or the highest VALUE is duplicated with
the first three.

So, using above data the query should return:

ID CODE ITEM DESC AMOUNT
=================================
2 BB 3 C53 7

for the following reasons
1 - ignored as zero in first three
3 - ignored as highest VALUE (4) is duplicated in first three

issues that are driving me mad - I can pull the ID and AMOUNT as
required by using two queries - tried a sub-query but it was still
running 30 mins later. - but what I can't do is to display the
ITEM/DESC/CODE because as soon as I put it after the SELECT statement
Access demands that it goes after GROUP BY and that results in
displaying records I don't want.

If anyone thinks this is easier done in a report and can give me some
pointers - that would be fine.

ta

 

[Guest]

Use your totals query (Max whatever) to find the correct record. Then join
the table and the first query in a query to display the rest of the fields.

If you will post your SQL that you are using to pick the record I will post
back the second query.

To post SQL open query in design view, click on menu VIEW - SQL View,
highlight, copy, and paste in a post.