Results 1 to 6 of 6
  1. #1
    Remphan is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    7

    Macro to create queries based by column value

    Database1.zip

    I have a table in excel. Only 2 columns. First - anything values. 2nd - n values which can be duplicated.
    A simple example. The first column is the surname of the buyer. The second column is the fruit that it acquires (for example, an orange).
    Buyers can be different, but the name of the fruit can be repeated.

    The file is large, 2000K rows. In the Excel it is already difficult to process.
    All I need is export to csv the data for each fruit. Output all buyers of each fruit in a separate file. If there are a lot of kinds of fruit (50-70), then it is difficult.
    How to automate the process? As an option - at least automatically generate requests, by the number of fruits (named as fruit is). To each request brought the buyers of this fruit. Than manually export data from each query to .csv



    Help me please. In the Excel this is easy to solve my problem, but in Access hard for me. Example (not on fruit, but similar) in the attachment.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Perhaps you can show what result you want, particularly since your example data doesn't match your description.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    check my revisions attached....

    fruit Database1.zip

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I cannot download your database, but I came up with a function that will do it.

    First, here are my assumptions.

    Table1 with fields "Buyer" and "Fruit".

    First, create a query that returns all the different fruits.
    I called this "qryFruits", and here is the SQL code for that query:
    Code:
    SELECT Table1.Fruit
    FROM Table1
    GROUP BY Table1.Fruit
    ORDER BY Table1.Fruit;
    Then, I created another query which returns all the buyers of a particular fruit. I named it "qryExport".
    But here is what I used initially:
    Code:
    SELECT Table1.Buyer
    FROM Table1
    WHERE (((Table1.[fruit])="Orange"))
    ORDER BY Table1.Buyer;
    Now, we want to set up our Export Specifications. We can do this by manually going through the Export of our "qryExport".
    So if your right-click on that, go through the Wizard and choose delimited, with comma as your delimiter, and all the other settings you want.
    Before clicking "Finish", click on the Advanced button, and save/name the Export Specification you just set up. I used "CSV_Export_Specs".

    Now, create a new VBA module, and add this VBA code to it:
    Code:
    Public Function ExportFruit()
    
        Dim db As Database
        Dim rst As Recordset
        Dim frtQryName As String
        Dim expQryName As String
        Dim expSpecs As String
        Dim fPath As String
        Dim fName As String
        Dim myFruit As String
        Dim mySQL As String
        
    '   ***INITIAL SETTINGS***
        frtQryName = "qryFruits"    'name of query listing different fruits
        expQryName = "qryExport"    'name of query that has buyers to export
        expSpecs = "CSV_Export_Specs"   '   name of export specification
        fPath = "C:\Temp\"      'name of file path to export to
            
        Set db = CurrentDb()
        Set rst = db.OpenRecordset(frtQryName)
        
    '   Loop through all records in query returning different fruit names
        Do While Not rst.EOF
    '       Get name of fruit from "fruit" field
            myFruit = rst![fruit]
    '       Build SQL string for query to export
            mySQL = " SELECT Buyer FROM Table1 WHERE fruit = " & Chr(34) & myFruit & Chr(34) & " ORDER BY Buyer"
    '       Assign SQL code to query
            CurrentDb.QueryDefs(expQryName).SQL = mySQL
    '       Build file export name
            fName = myFruit & ".csv"
    '       Export
            DoCmd.TransferText acExportDelim, "CSV_Export_Specs", expQryName, fPath & fName, True, ""
    '       Go to next record
            rst.MoveNext
        Loop
        
        rst.Close
        
        Set rst = Nothing
        Set db = Nothing
        
    End Function
    Now, to run it, you can either call the Function from a Form button, or create a Macro (and call it using the "RunCode" action, where Function Name argument is set to: ExportFruit
    That will export one CSV file for each fruit, with all the Buyers listed (and name the file the name of the fruit).

    Note you will need to change the "fPath" value in the VBA code to reflect the path you want to export these files to.

  5. #5
    Remphan is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    7
    Uhm..
    So, I can do simply (runman256+ your code):

    Query qsCata
    Code:
    SELECT DISTINCT DATA.[phonetic letter]
    FROM DATA;
    Query qsData1Cata
    Code:
    SELECT DATA.*
    FROM DATA
    WHERE (((DATA.[phonetic letter])=[Forms]![frmMainMenu]![lstCatas]));
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub btnExport_Click()
    Dim expSpecs As String
    Dim itm
    Dim i As Integer
    Dim fName As String
    Dim fPath As String
    
    expSpecs = "ssss"   '   name of export specification
    
    fPath = "C:\Temp\"
    
    For i = 0 To lstCatas.ListCount - 1
       itm = lstCatas.ItemData(i)       'get next item in listbox
       lstCatas = itm                   'set list to item
       fName = itm & ".csv"
          
      DoCmd.TransferText acExportDelim, "ssss", "qsData1Cata", fPath & fName, True, ""
    
    Next
    
    End Sub
    So if your right-click on that, go through the Wizard and choose delimited, with comma as your delimiter, and all the other settings you want.
    Before clicking "Finish", click on the Advanced button, and save/name the Export Specification you just set up. I used "CSV_Export_Specs".
    I do, saved as "ssss". But Runtime 3625

    Excuse for stupid questions, approximately I understand, but long ago there was no practice any neither with excel nor with access, at everything as for the first time look.

  6. #6
    Remphan is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    7
    Its will so hard, but i found export specification, all ok, thank u!!!

    Just little correct the form code

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub btnExport_Click()
    Dim expQryName As String
    Dim expSpecs As String
    Dim itm
    Dim i As Integer
    Dim fName As String
    Dim fPath As String
    expQryName = "qsData1Cata"    'name of query that has buyers to export
    expSpecs = "sss"   '   name of export specification
    
    fPath = "C:\Temp\"
    
    For i = 0 To lstCatas.ListCount - 1
       itm = lstCatas.ItemData(i)       'get next item in listbox
       lstCatas = itm                   'set list to item
       fName = itm & ".csv"
          
    DoCmd.TransferText acExportDelim, expSpecs, expQryName, fPath & fName, -1
    
    Next
    
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 06-09-2017, 05:07 PM
  2. Replies: 7
    Last Post: 05-17-2017, 07:48 PM
  3. Replies: 43
    Last Post: 03-10-2015, 09:35 PM
  4. Create a column based on a query
    By Russellh in forum Queries
    Replies: 6
    Last Post: 10-23-2014, 08:44 AM
  5. Replies: 8
    Last Post: 06-23-2014, 12:13 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums