Splitting and exporting multiple tables from MS Access

Question

I'm hoping there is a fairly easy answer to this question. Perhaps using VB.

I have a table in access called 'customers'. Each customer has an assigned franchise name in a column called 'franchise' on this table. I need to supply all franchises a csv file containing all the customers assigned in their franchise from the customer table.

The long winded way would be an individual query for each franchise(select * from customer where franchise = 'New York'), but there are over 50 different franchise names. And then exporting each of the queries one by one would be very time consuming.

Is there any vb code for creating multiple queries or tables based on the value in a field?

Thanks

Answer 1

The code below will do it. I created a DB with the structure below. It outputs CSV files with names in the format of "{Franchise_Name} {Customers}.csv" e.g "Franchise A Customers.csv".

Your description seemed to indicate that you were saving franchise names in the customer table itself, whereas it would be better practice to have a separate table that contains the names of the franchises and a foreign key in the Customers table referencing the primary key in the Franchises table. Still, you could modify this code to work with your implied structure, but you'd still have to create a distinct list of Franchise names (i.e. your recordset set line would be Set Franchises = CurrentDb.OpenRecordset("SELECT DISTINCT Franchise FROM Customers") and you'd modify my references to FranchiseID to work on your Franchise field instead.

Customers Table

ID | Customer_Name | FranchiseID
 1 | Customer 1    |           1
 2 | Customer 2    |           2
 3 | Customer 3    |           1
 4 | Customer 4    |           2
 5 | Customer 5    |           3

Franchises Table

ID | Franchise_Name
 1 | Franchise A
 2 | Franchise B
 3 | Franchise C

VBA Code

Option Compare Database
Option Explicit

Sub Export_Franchise_Customers()

  Dim Franchises As Recordset

  Dim FranchiseID As Integer
  Dim Franchise_Name As String
  Dim Base_SQL As String
  Dim QueryDefName As String

  Base_SQL = "SELECT * FROM Customers WHERE FranchiseID = "

  Set Franchises = CurrentDb.OpenRecordset("Franchises")

  Do While Not Franchises.EOF

    FranchiseID = Franchises("ID")
    Franchise_Name = Franchises("Franchise_Name")

    QueryDefName = "get_Franchise" & FranchiseID & "_Customers"
    CurrentDb.CreateQueryDef QueryDefName, Base_SQL & FranchiseID

    DoCmd.TransferText TransferType:=acExportDelim, TableName:=QueryDefName, FileName:=Franchise_Name & " Customers.csv", HasFieldNames:=True

    CurrentDb.QueryDefs.Delete QueryDefName
    Franchises.MoveNext

  Loop

End Sub