Top 2 Items for 2 unqiue fields

[AccessUser207]

I want to take the top 2 items in a list. I want to get the top 2 for both field 1 = a and filed 1 = b.

For example say my two field look like this
field 1 field 2
a 8
b 7
b 6
a 2
b 4

My result would look like
a 8
a 2
b 7
b 6

How do I do this?

 

[lameid]

I think you want to set the top values to 2 (get the query properties in the QBE and look for Top Values) and run it for each criteria you want. Once for a and once for b.

 

[GingerR]

I've done it, and put the results into a table. You have to loop thru the records to find the top two of each item.

My final table is called TopTwoFinal.
2 fields:
Field1: (text, your a's and b's)
Field2: integer

Make a new code module, and put this into it:

Function GetTopTwo()
    'Empty out the final table (TopTwoFinal)
    CurrentDb.Execute ("Delete * from TopTwoFinal")
    
    'Table6 is the table that has data in it
    'Field1 is the one with a's and b's in it (text field type)
    'Field2 is the one with integers in it
    
    'Make sure you go to TOOLS+REFERENCES and pick Microsoft DAO (the highest version
    'available, probably 3.6)
    
    Dim rs, rsTopTwo, rsFinal As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Select Distinct Table6.Field1 from Table6")
    Set rsFinal = CurrentDb.OpenRecordset("Select * from TopTwoFinal")
    rs.MoveFirst
    While Not rs.EOF
        Set rsTopTwo = CurrentDb.OpenRecordset("SELECT TOP 2 Table6.* FROM Table6 " & _
            "WHERE Table6.Field1 = '" & rs!Field1 & "' " & _
            "ORDER BY Table6.Field2 DESC")
        rsTopTwo.MoveFirst
        'For the two TopTwo records returned, add them to table TopTwoFinal
        While Not rsTopTwo.EOF
            rsFinal.AddNew
            rsFinal!Field1 = rs!Field1
            rsFinal!Field2 = rsTopTwo!Field2
            rsFinal.Update
            rsTopTwo.MoveNext
        Wend
        Set rsTopTwo = Nothing
        rs.MoveNext
    Wend
    
    rs.Close
    Set rs = Nothing
    Set rsTopTwo = Nothing
    rsFinal.Close
    Set rsFinal = Nothing
End Function

Substitute your table name for "Table6"
Substitute your field names for "Field1" and "Field2"

Then say you hit a button that creates the report; first call this function, then open the report. Make the report's RecordSource be the table TopTwoFinal. OR--if you need information from your original table, just add TopTwoFinal to your Report's Recordsource and join the fields. Let us know how it goes.


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[AccessUser207]

GingerR
The code works, however, I need to pull various text in field 3 into the new table and I tried inserting the following line and it gave me an eror.

rsFinal!Field3 = rs!Field3

What other lines do I need to add.

 

[lameid]

What was the error?

Does Field 3 exist in both the table and the sql statement you are using to open the recordset?

Also note worthy is that GingerR's code gives you the 2 BIGGEST values for each value and not the first 2 in the list. It all depends on what you are trying to accomplish.

 

[GingerR]

Did you try what I suggested:

OR--if you need information from your original table, just add TopTwoFinal to your Report's Recordsource and join the fields.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[AccessUser207]

I ran the code and it works beautifully. I just was simplifying it more than I should it really has three columns.
field 1 field 2 field 3
a 8 apples
b 7 bannanas
b 6 mangos
a 2 berries
b 4 rubarb

My result needs to look like this.
a 8 apples
a 2 berries
b 7 bannanas
b 6 mangos

So I was just going to add one line to the code above

rsFinal!Field1 = rs!Field1
rsFinal!Field2 = rsTopTwo!Field2
--> rsFinal!Field3 = rs!Field3
rsFinal.Update

to pull field 3 into the new table, however it gives me an error.

 

[lameid]

Shouldn't

rsFinal!Field3 = rs!Field3

be

rsFinal!Field3 = rsTopTwo!Field3

?

 

[AccessUser207]

YES!!! Thank you both.
Thank you GingerR for all the work. My project works perfectly
paul