Multiple query criteria VBA

[Neilbees]

Hi all

Again, I've searched for ages but I think there's a particular element of what I'm trying to do that I can't find the answer to. The code below basically loops through the table (tblMaster) and creates a new table for each Agency. All works fine.

What I've now been asked to do is add a second element - so that the code will create a separate table for each Agency for each Customer. I just can't work out how to adapt this code to add in this requirement. Customer is another field in the tblMaster. Any help with this would be greatly appreciated.

Dim rs As Recordset

  Set rs = CurrentDb.OpenRecordset("select distinct Agency from qryAgency")
  While Not rs.EOF
    On Error Resume Next
    CurrentDb.Execute "DROP TABLE [" & rs("Agency") & "]"
    CurrentDb.Execute "SELECT tblMaster.* INTO [" & rs("Agency") & "] FROM tblMaster WHERE Agency=""" & rs("Agency") & """;"
    On Error GoTo 0
    rs.MoveNext
  Wend

  rs.Close
  Set rs = Nothing

 

[pbaldy]

If it fits your needs, start with this as the recordset:

SELECT Agency, Customer
FROM tblMaster
GROUP BY Agency, Customer

 

[Neilbees]

Hi Paul

Thanks for your very quick response. I amended the code on your suggestion as follows:

Set rs = CurrentDb.OpenRecordset("SELECT Agency, Customer  FROM tblMaster GROUP BY Agency, Customer")

However - I just got the same results as before - a new table for each Agency.

Just in case I didn't explain myself very well (not for the first time!) I need a new table for each customer for each Agency. So if there were 20 Agencies and 5 Customers there would be 100 new tables.

Thanks in advance for any help you can provide.

 

[pbaldy]

That wouldn't have been the only change required, which is why I said to "start with" that. Also, that assumed that the desired combinations would exist in that table. If you have agency and customer tables, you could start with a query including both tables with no join.

 

[WayneRyan]

Neil,

Try referencing --> "Drop Table [" & rs("Agency") & "_" & rs("Customer") & "]"

You are overwriting the Agency table for each customer.

Use the above for the "Into" and "From" clauses also.

I don't know why you need to create the tables, a query will do.

Wayne