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.