Can I create a make table query that will make multiple tables ?

[Michael D]

I want to create a different table for each sales person listed in a table. If I have salesperson1 through salespersonN listed in the table, I'd like to end up with a table for salesperson1 with just salesperson1 data through salespersonN with just salespersonN's data.

The resultant tables would be named something like

salesperson1 table1
through
salespersonN table1

Make sense?

[MMcCarthy]

Your question makes sense, it's just not the way that databases work.

So lets say you have a table for salespeople, then a table for leads and a table for sales.

You then would have something like this

tblSalespeople
SalespersonID (Primary key)
FirstName
Surname
Etc

tblLeads
LeadID (Primary key)
SalespersonID (Foreign key to tblSalesperson)
LeadName
PhoneNum
Etc

tblSales
SalesID
SalespersonID
CustomerName
Etc

Then using a query something like ...

SELECT tblSaleperson.*, tblLeads.*, tblSales.*
FROM tblSalesperson LEFT JOIN tblLeads
ON tblSalesperson.SalespersonID=tblLeads.SalespersonID
(LEFT JOIN tblSales
ON tblSalesperson.SalespersonID=tblSales.SalespersonID

You can retrieve the information you need for each sales person. For further information check out this insight.
Database Normalization and Table Structures

[Michael D]

I really owe you a little more explanation. From time to time I send a personalized excel file to each salesperson asking them review and mark up a listing of a subset of their accounts with various information I generally collect yes/no type of data for a specific question about the account.
If my sales people could use Access I'd build a form where they choose their accounds to collect their data right into a table.
Unfortunately, I have to expernalize data and then import.
Does that help explain why I'm doing this silly thing?

[MMcCarthy]

That helps explain it a little better :)

Still don't think its a good idea to create a lot of tables though. So if I understand correctly, you are going to import/link each excel sheet and then try to create a table from the data and then use the subsequent tables to update your data.

If that is what you are trying to do it would be easier to use vba code to automate the process. However, I don't know how familar you are with vba code.

[MIchaelD]

Not familiar with VBA but can learn.

Best scenario is I would write a macro to create a temp table, one salesperson at a time, and export to an excel file of that salesperson. So, in the end I'd have salesperson1.xls --> salespersonN in some directory somewhere.

[MMcCarthy]

OK it sounds like you need to create dynamic queries.

You can put this code behind a command button.

Private Sub cmdButton1_Click()
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim path As String
 
    ' find the path for the current division
    path = "C:\FolderName\" ' Path to the folder you want to export to
 
    With CurrentDb
        Set rs = .OpenRecordset("tblSalesperson") ' replace with name of your salespeople table
 
        rs.MoveFirst
        'For each person in the table
        Do Until rs!EOF
            ' Set up SQL query string, replace with your field names, query name, salesperson unique id etc.
            strSQL = "SELECT Field1, Field2 FROM MyQueryName WHERE SalespersonID=" & rs!SalespersonID
            '  createquerydef command line follows
            Set qdfNew = .CreateQueryDef("My_Query", sqltext)
            DoEvents
            ' save the query to Excel, replace surname and firstname with your appropriate field names
            DoCmd.OutputTo acOutputQuery, "My_Query", acFormatXLS, path & rs!FirstName & "_" & rs!Surname & Format(Now, "ddmmyyyy hhmm") & ".xls", True
            ' delete the temporary query
            DoCmd.DeleteObject acQuery, "My_Query"
            qdfNew.Close
            Set qdfNew = Nothing
            rs.MoveNext
        Loop
 
        rs.Close
        Set rs = Nothing
    End With
 
End Sub

[Michael D]

Thanks for all your help. I'll give it a go this week.