select and insert statement

[larkeyshark]

Help!!!I am working with a master table containing a large number of records (names and cities of residence sorted by city). I need to insert 500 records from the master table to 5 secondary tables. Each secondary tables will receive 100 records from each of the 5 cities. I tried with a do while...loop statement and did not get very far. Thanks for the help.

 

[WayneRyan]

Lark,

Aside from, "Why are you doing this?", you do not need a
loop. Use an Append query and look up the TOP predicate
in SQL. Just select the Top 100 and insert them into
the other table.

Wayne

 

[larkeyshark]

HI Wayne
I could not make a query with TOP work. More detail: There are 5 tables that need to be populated from the master table. Each table will receive 100 records from each city, not the same records of course; so if I use TOP I need a way to identify or delete from the master table the records already assigned. Any ideas?

 

[WayneRyan]

Lark,

Haven't tried this, but it should be close. Just change the names of
the cities and tables.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim SQL As String

Dim intCity As Long
Dim intRecord As Long

Dim MyCities(5) As String
Dim MyTables(5) As String

MyCities(1) = "New York"
MyCities(2) = "Boston"
MyCities(3) = "Chicago"
MyCities(4) = "San Diego"
MyCities(5) = "Los Angeles"

MyTables(1) = "New York"
MyTables(2) = "Boston"
MyTables(3) = "Chicago"
MyTables(4) = "San Diego"
MyTables(5) = "Los Angeles"

Set dbs = CurrentDb

For intCity = 1 to 5
   ' Get a recordset of a city
   SQL = "Select * " & _
         "From " & MyTables(intCity) & _
         "Where City = '" & MyCities(intCity) & ";"
   Set rst1 = dbs.OpenRecordSet(SQL)
   ' Get count
   rst1.MoveFirst
   rst1.MoveLast
   If rst1.RecCount < 100 Then
      MsgBox(MyCities(intCity) & &quot; has only &quot; & rst1.RecCount & &quot; Records.&quot;)
   End If
   rst1.MoveFirst
   ' Move 100 records
   intRecord = 0
   SQL = &quot;Select * From &quot; & MyTables(intCity) & &quot;;&quot;
   Set rst2 = dbs.OpenRecordSet(SQL)
   While (intRecord < 100 And Not rst2.EOF and Not rst2.EOF
      rst2.AddNew
      rst2!SomeField = rst1!SomeField
      ' repeat for all ...
      rst2.Update
      rst1.MoveNext
      intRecord = intRecord + 1
      Wend
   Next intCity
MsgBox(&quot;Completed.&quot;)

Wayne

 

[larkeyshark]

HI Wayne
I appreciate your help but your code seem to do more than I need, my fault for not being clear enough. Here is what I am trying to do: I have a master table (say tblMaster) containing a large number of records, mainly name and city. I need to insert records from the master table to secondary tables (tblsecondary,1,2...5). At the end of the process each secondary table should have the same number of records total (say 500) and the same number of records selected from each city. So tblSecondary1 will have 100 records from NY, 100 records from LA, etc..., tblsecondary2 will have 100 from NY (not the same as tblsecondary1), 100 for LA and so on. Does that make more sense?
I tried also using insert queries with TOP and excluding already selected records using NOT IN (select statement) but it becomes very long and clumsy.
Thanks for helping.

 

[WayneRyan]

lark,

The code will do what you described in your last post. But
just how many records are you talking about here? With
the proper indexes, they really should live in the same
table. Between your forms, reports and search software
you are going to be swimming in Union queries.

If you changed the city names to your cities, and changed
the table names to your tables, and changed the 100 to
whatever number, it would work.

good luck,

Wayne