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

sxxgupta
sxxgupta used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
SELECT b1.*
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]

Author

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

Author

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

Author

Commented:
first time I have seen this.  so where are these objects b1, b2, z
Top Expert 2010

Commented:
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.
Top Expert 2010

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

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial