Create separate tables and export into Excel

[KrispyData]

I have a datasheet that contains all the account information for every sales
rep in the nation. I would like to send each rep their respective account
information only. Can I set up a query that will create a table for each
rep? Also, is there a macro i can use to have these tables exported into
Excel?

 

[John W. Vinson]

I have a datasheet that contains all the account information for every sales
rep in the nation. I would like to send each rep their respective account
information only. Can I set up a query that will create a table for each
rep?

You could - a MakeTable query. However it is neither necessary nor the
appropriate approach! You can export a Query into Excel just as easily as you
can export a Table.

Also, is there a macro i can use to have these tables exported into
Excel?

Yes: it won't be trivial but it won't be very hard either. I'd use a VBA
module rather than a Macro, so you can loop through the sales reps; this
module would use the TransferSpreadsheet method to do the export. See the VBA
help for TransferSpreadsheet, and post back if you need more help.

 

[Ken Snell MVP]

See this article:

Create a Query and Export multiple "filtered" versions of a Query (based on
data in another table) to separate EXCEL files via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#FilterExportSepFiles