I am trying to write in VBA for Access. I have a table with several members along with the vendors they belong to. I want to write something that will create a new table at each change in vendor as to group all members with the same vendor into seperate tables but am new to VBA and not sure how to go about doing so. for example:

Column 1  | Column 2
---------   ---------
Member 1  | Vendor 1;
Member 2  | Vendor 1;
Member 3  | Vendor 2;
Member 4  | Vendor 3;

I would like the following:

Table 1:

Member 1
Member 2

Table 2:

Member 3

Table 3:

Member 4

Any help that can be provided is very greatly appreciated. I have tried searching Google for an answer but can't seem to find anything. I know it would have to be some type of loop just not sure how to acheive this since I am not very familiar with VBA.

Thank you all in advance!

I must admit I'm not sure exactly why you would want to do this... that said, you never know. Here is an example of a VBA function that would accomplish this:

Sub CreateTables()

  Dim rs As Recordset
  Dim sql As String
  Dim vendor As String

  sql = "select distinct [Vendor] from [Vendor Members]"

  Set rs = CurrentDb.OpenRecordset(sql)

  Do While Not rs.EOF
    vendor = rs!vendor

    sql = "select [Member] into [" & vendor & _
        "] from [Vendor Members] where [Vendor] = """ & vendor & """;"
    CurrentDb.Execute sql
    rs.MoveNext
  Loop

End Sub

This presupposes your table name is Vendor Members, but such things are easy enough to change on the fly.