Help for Selecting From Within Groups

[susieqtotallynew]

Hi, there,
I am new to VBA and hope someone may be able to help.
I have a database table that includes information like the following:
ID Amount Sequence
1 $5 1
1 $10 2
1 $15 3
2 $8 1
2 $12 2
For each ID, I need to return the ID number, along with the largest amount.
Any suggestions as to how to begin would be most appreciated.
Thank you.

 

[SkipVought]

Hi,

No need for VBA. Just a simple PivotTable.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MakeItSo]

To me this seems more like an SQL issue rather than VBA.
In this case, a simple

SELECT ID, Max(Amount) as Maximum 
FROM [name of your table] 
GROUP BY ID, Amount;

Example from VBA with an Access DB:

Dim con as ADODB.Connection, rs as ADODB.Recordset strSQL as String
Set con=New ADODB.Connection
Set rs=New ADODB.Recordset
Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[path to mdb];Persist Security Info=False

strSQL="SELECT ID, Max(Amount) as Maximum FROM [name of your table] GROUP BY ID, Amount;"

Set rs=con.Execute(strSQL)

Do while not rs.eof
  Msgbox "Maximum for ID " & rs!("ID") & " is " & rs!("Maximum")
Loop

Something like that?
;-)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[susieqtotallynew]

Thanks for your responses...I realized that I hadn't phrased my question correctly...Here is the corrected version of the scenario.
I have a database table that includes information like the following:
ID Amount Sequence
1 $5 1
1 $10 2
1 $15 3
2 $8 1
2 $12 2
2 $6 3
For each ID, I need to return the ID number, along with up to 2 of the largest amounts. So, for ID 1, for example, I would return $10 and $15. For ID 2, I would return $8 and $12.
Any suggestions as to how to begin would be most appreciated.
Thank you (again!).

 

[SkipVought]

Are thre ALWAYS 3 sequence numbers per ID?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[susieqtotallynew]

No...there could be any number. What actually gets returned is up to 10 of the largest entries. So, if there are three entries for an id number, it would return 3, if there were five, it would return five, if there were fifteen, it would return the top 10. Perhaps you see my challenge...Particularly as a newbie!

 

[SkipVought]

For each ID, I need to return the ID number, along with up to 2 of the largest amounts.

THAT statement you made previously does not square with your lates post.

I am TOTALLY confused!!!

Would you please be CLEAR, CONCISE and COMPLETE with your requirements.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[susieqtotallynew]

Apologies, Skip.
I want to return a maximum of 10 entries. Suppose that there are 12 items in the list of entries for ID 1. In this case, I return the top 10 items. The number of entries for ID 2 is 8. In this case, I can only return 8, as that is all there are. Likewise, if there was an ID 3 with 5 entries, I would only be able to return the top 5 entries.
Does that clarify?

 

[SkipVought]

In the adjacent column, assuming that it is column D...
[tt]
D1: Reseq
D2: =IF(A2=A1,D1+1,1)
E1: Keep
E2: =IF(COUNTIF(A:A,A2)-(C2-1)<11,1,0)
[/tt]
copy D2 & E2 down thru all rows of data.

Turn on your AutoFilter and filter on 1 in Keep

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MakeItSo]

Most important information is still missing!

I am new to VBA

So you do have VBA code? In what app? Excel? Access?

I have a database table

That implies you are working with a database.
Are we talking about Access? FoxPro? SQLExpress?

What are you working with?

And: are you querying that data from within that database application or are you trying to access it from some other application?

NEED MORE INFO!

You get a SQL / VBA response from me and Excel responses from Skip and still none of us knows which you need!

Cheers,
MiS

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[SkipVought]

BTW,

There are MANY other forums, including forum705, that are devoted to MS Access. This forum is NOT.

I therefore I have assumed that YOUR questions are NOT related to the MS Access application, rather the Excel application.

PLEASE CLARIFY.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

If your table is called tbl_Base then

SELECT One.ID, One.Amount, Two.Amount
FROM
(SELECT x.ID,x.Amount, Count(*) as Rank
    FROM tbl_Base x INNER JOIN tbl_Base y 
       ON x.ID = y.ID AND x.Amount <= y.Amount 
    GROUP BY x.ID, x.Amount) One
INNER JOIN
(SELECT x.ID,x.Amount, Count(*) as Rank
    FROM tbl_Base x INNER JOIN tbl_Base y 
       ON x.ID = y.ID AND x.Amount <= y.Amount 
    GROUP BY x.ID, x.Amount) Two
ON One.ID = Two.ID
WHERE One.Rank = 1 AND Two.Rank = 2

may do the job for you

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[susieqtotallynew]

Geoff,
Thank you very much--that worked beautifully.
How would you modify this query to include an additional column item if it existed, such as the date the transaction occurred? Knowing this would also help me to understand the workings of the query a bit better.
Thanks so much.

 

[xlbo]

You would reference it in exactly the same way as the amount:

SELECT One.ID, One.Amount, One.TransDate, Two.Amount, Two.TransDate
FROM
(SELECT x.ID,x.Amount, x.TransDate, Count(*) as Rank
    FROM tbl_Base x INNER JOIN tbl_Base y 
       ON x.ID = y.ID AND x.Amount <= y.Amount 
    GROUP BY x.ID, x.Amount, x.TransDate) One
INNER JOIN
(SELECT x.ID,x.Amount, x.TransDate, Count(*) as Rank
    FROM tbl_Base x INNER JOIN tbl_Base y 
       ON x.ID = y.ID AND x.Amount <= y.Amount 
    GROUP BY x.ID, x.Amount, x.TransDate) Two
ON One.ID = Two.ID
WHERE One.Rank = 1 AND Two.Rank = 2

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[susieqtotallynew]

Geoff,
One last inquiry...Sorry! If I wanted to complete the query for three (or more) transactions, how would I proceed? I think if I saw one more group I can accomplish the rest.
Thank you again.

 

[xlbo]

Simply add in another join to the same table again:

SELECT One.ID, One.Amount, One.TransDate, Two.Amount, Two.TransDate, Three.Amount, Three.TransDate
FROM
(SELECT x.ID,x.Amount, x.TransDate, Count(*) as Rank
    FROM tbl_Base x INNER JOIN tbl_Base y 
       ON x.ID = y.ID AND x.Amount <= y.Amount 
    GROUP BY x.ID, x.Amount, x.TransDate) One
INNER JOIN
(SELECT x.ID,x.Amount, x.TransDate, Count(*) as Rank
    FROM tbl_Base x INNER JOIN tbl_Base y 
       ON x.ID = y.ID AND x.Amount <= y.Amount 
    GROUP BY x.ID, x.Amount, x.TransDate) Two
ON One.ID = Two.ID
INNER JOIN
(SELECT x.ID,x.Amount, x.TransDate, Count(*) as Rank
    FROM tbl_Base x INNER JOIN tbl_Base y 
       ON x.ID = y.ID AND x.Amount <= y.Amount 
    GROUP BY x.ID, x.Amount, x.TransDate) Three
ON One.ID = Three.ID

WHERE One.Rank = 1 AND Two.Rank = 2 AND Three.Rank = 3

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question