asked on

microsoft access vba coding to pick select records from a table based on grouping and criteria

I have a table called BeltDataFromOhmMakeTable. I need expert help to develop a vba module where the recordset is opened from this table and the coding picks out the entire record where each grouped [Additional Data 2] field has the last [ID] associated with it (or the max ID associated with it). These "selected records" need to go into a new table called BeltDataFromOhmMakeTableLastRecord.

I could use queries etc, but I need coding to do this as I could use this elsewhere without having to create multiple in between queries. I have attached a sample db. The output should look something like this

Additional Data 2 ID
59000148 29
59000151 59
59000154 30
59000157 34
59000160 33
59000163 53
59000166 198

test.mdb

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sanjay

ASKER

so no opening recordsets and comparing values and rs moving next type of a thing...........

Sanjay

ASKER

how do you compare the value of a field when you move using rs.movenext?

Sanjay

ASKER

first time I have seen this. so where are these objects b1, b2, z

Patrick Matthews

Executing a query is going to be much faster than looping through a recordset in code. Now, in your question your wrote:

and the coding picks out the entire record where each grouped [Additional Data 2] field has the last [ID] associated with it (or the max ID associated with it).

And yet, your sample output shows two columns. Which is correct?

Anyway, to turn my query above into a "make table":

SELECT b1.*
INTO BeltDataFromOhmMakeTableLastRecord
FROM BeltDataFromOhmMakeTable b1 INNER JOIN
    (SELECT b2.[Additional Data 2], Max(b2.[ID]) AS MaxID
    FROM BeltDataFromOhmMakeTable b2
    GROUP BY b2.[Additional Data 2]) AS z ON b1.[Additional Data 2] = z.[Additional Data 2] AND b1.ID = z.MaxID
ORDER BY z.[Additional Data 2]

Open in new window

If all you really want is the two-column output it is much simpler:

SELECT [Additional Data 2], Max([ID]) AS MaxID
INTO BeltDataFromOhmMakeTableLastRecord
FROM BeltDataFromOhmMakeTable
GROUP BY [Additional Data 2]

Open in new window

You can have those queries be executed from VBA if you wish.

Patrick Matthews

The b1, b2, and z are simply table aliases. It is a common technique.

Sanjay

ASKER

thanks Matt. It would be appreciated if you could also show me on the side for my benefit and understanding on how to loop through a recordset.