zyus said:
My data (500,000 records) is in Tbl-A and consist of 10 fields. One of the
field is BRNCD (branch code) and overall i hv 13 branch code.
With just ONE query/code/etc I want to export all data to excel file
grouped
by each branch code and access will automatically create 13 excel files .
You can't possibly do it with one *query*, but you can easily do it with one
VBA procedure. Probably the best way is to do this from a form, and have on
that form a text box that will be used to specify the current branch code to
export. Suppose that the form is called "frmExport" and the text box is
called "txtExportBranch". Then you would create a query named
"qryExportTblA", with SQL like this:
SELECT * FROM [Tbl-A] WHERE BRNCD =
[Forms]![frmExport]![txtExportBranch];
Are your 13 branch codes stored in a table? If so, the code to export each
of the branches to a separate file might look something like this:
'----- start of example "air code" -----
Dim rsBranches As DAO.Recordset
Set rsBranches = CurrentDb.OpenRecordset("tblBranches")
With rsBranches
Do Until .EOF
Forms!frmExport!txtExportBranch = rsBranches!BRNCD
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel9, _
"qryExportTblA", _
"Branch_" & !BRNCD & ".xls", _
True
.MoveNext
Loop
.Close
End With
'----- end of example "air code" -----
