分裂与从MS Access导出多个表 [英] Splitting and exporting multiple tables from MS Access

问题描述

我希望有一个很容易回答这个问题。也许是用VB。

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

我在访问一个表称为客户。每个客户都有一个指定的特许经营权的名称在一个名为该表的特许经营权一栏。我需要提供包含所有从客户表他们的特权分配给客户的所有特许经营的CSV文件。

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.

本长篇大论的办法是为每个加盟店单个查询（SELECT * FROM客户，其中专营='纽约'），但也有超过50种不同的专营权的名字。然后由一个出口的每个查询中的一个将是非常耗时的。

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.

有没有VB code的基础上的一个字段中的值创建多个查询或表？

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

感谢

推荐答案

下面的code将做到这一点。我创建了以下结构的DB。它输出CSV文件名格式为{Franchise_Name} {}客户的.csv，例如专营Customers.csv。

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".

您的描述似乎表明，你在客户表本身节约专营名称，而这将是更好的做法，有一个包含特许经营权的名称和在客户表的外键引用的主键一个单独的表在特许经营表。不过，你可以修改此code与您的隐含结构的工作，但你还必须创建特许经营名字的不同的列表（即您的记录集线将设置特许经营= CurrentDb。 OpenRecordset（SELECT DISTINCT特许经营客户），你会修改我的引用FranchiseID在你的特许经营领域的工作，而不是。

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.

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

特许经营表

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

这篇关于分裂与从MS Access导出多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！