Create Table Query

[cantona]

I have a table that contains information for a number of departments. What I would like to do is split this table down by department so that I can produce a seperate table for each department. These will then be used to send out to departments to allow them to check the information contained within them. What ive tried so far is this… Ive created a make table query which pulls in all fields from the source table and creates a new table for the department code I enter as a parameter. This works fine although I would need a separate query for each department. Rather than having to run the query say 40 times and naming the table the name of the department, id like to automate the process so that with one click of a button a table is produced for each individual department within the source table, and each table is called the name of the department. Is this possible? How would I go about doing it? The field in question is department_code. Can anyone help?

 

[Remou]

How are you sending these tables? Why not email a query using a department code recordset and SendObject?

 

[cantona]

Thats exactly what i would do, but apparently the departments want it as a table in a seperate mdb. I can do it manually, was just hoping for a quicker way!

 

[Remou]

You can use the same idea with a make table query and a recordset of departments. You can even create the mdb and email it using Outlook automation.

Some notes:

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("tblDepts")
    Do While Not rs.EOF
    
        Filename = "C:\Docs\" & rs!Dept & ".mdb"
    
        Set db = CreateDatabase(Filename, dbLangGeneral)
        db.Close
        Set db = Nothing
        
        strSQL = "SELECT id, etc " _
        & " INTO " & rs!Dept & " IN '" & Filename _
        & "' FROM tblTable " _
        & "WHERE id = '" & rs!Dept & "'"
    
        CurrentDb.Execute strSQL
    Loop

Or there abouts.