Create multiple tables from one table in MS Access

Hello Experts,

I am using MS Access 2007 and I'm trying to make multiple tables/files from one table.

The master table is titled "SmrtShtQ1-18". In this table, there are 3 owners: anyone, Patel, and Hector.
I would like to create a file for each owner with their records only. The intent is so I can email each of them their report separately.

I created a module, "Module1" where the query qryOwnerReports, is populated with "".  The module moves the query results to tblTemp.

What I need to do next, and I don't know how to do it, is to have the next name create a file for Patel's records, then move on to Hector, until a file is made for each owner.  To keep it simple, I am keeping the new table in the database instead of exporting the file to folder.

I'm sure there is more than one way to do this (no query, move next, etc.) and I'm not concerned with method is used, just that the separate files are made.  The end result will be 3 tables with the name of the owner as the table name.

I have attached the sample database I have been working with.

Can you please assist me with this?

Option Compare Database

Public Function CreateUserReviewsRpt()

Dim rs As Recordset
Dim rs1 As Recordset
Dim rs2 As Recordset

Dim db As Database

Set db = CurrentDb
Set rs = db.OpenRecordset("SmrtShtQ1-18")
Set rs = db.OpenRecordset("qryOwnerReports", dbOpenSnapshot)

Set rs2 = CurrentDb.OpenRecordset("tblTemp", dbOpenDynaset)

If rs.RecordCount > 0 Then
    Do Until rs.EOF
        If Not rs.NoMatch Then
            If Not IsNull(rs!Owner) Then
                    rs2!Name = rs!Name
                    rs2!Workspace = rs!Workspace
                    rs2!Type = rs!Type
                    rs2!Owner = rs!Owner
                    rs2!SharedTo = rs!SharedTo
                    rs2!SharedToPermission = rs!SharedToPermission
                    rs2!LastViewedDate = rs!LastViewedDate
            End If
        End If
End If

End Function

Thank you,
Who is Participating?
Gustav BrockConnect With a Mentor CIOCommented:
Why not simply filter on the e-mail address:

    [SmrtShtQ1-18].Owner=[Owner E-mail];

Open in new window

Fabrice LambertFabrice LambertCommented:
Not sure about what you're trying to achieve, but in a database, a table should not be named dynamically, neither be named after some specific data.

As Gustav said, it is better to filter queries with a WHERE (or HAVING) clause.
Steph_MAuthor Commented:
I must not  have explained my requirement well enough. The real table has over 100 owners. I want to automate the process so that a table, or file, is made and saved so I can distribute it after the module or query runs.

What I wanted to do was create a loop that would move the next owner into the query, run the query, export the results into a table,  

Any suggestions?
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Gustav BrockCIOCommented:
Then open a recordset with all the e-mail addresses, and loop through this.
For each record, run my query above with the address as parameter value and export the output to a table.
Fabrice LambertFabrice LambertCommented:
Maybe the following:
Dim db as DAO.Database
Set db = Currentdb

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT DISTINCT Owner FROM [SmrtShtQ1-18];", dbOpenSnapshot)
While Not rs.EOF
    db.Execute("DELETE * FROM tblTemp;", dbFailOnError)
    Dim SQL As String
    SQL = VbNullString
    SQL = SQL & "INSERT INTO tblTemp (Owner, Name, Workspace, Type, SharedTo, SharedToPermission, LastViewedDate)" & vbcrlf
    SQL = SQL & "SELECT Owner, Name, Workspace, Type, SharedTo, SharedToPermission, LastViewedDate" & vbcrlf
    SQL = SQL & "FROM [SmrtShtQ1-18]" & vbcrlf
    SQL = SQL & "WHERE Owner = """ & rs.Fields("Owner").Value & """;"
    db.Execute SQL, dbFailOnError

    '// Code doing export here

Set rs = Nothing
Set db = Nothing

Open in new window

Steph_MAuthor Commented:
Ok, I see Fabrice moved Gustav's query into a module.  I'll work on the export piece later tonight and comment back tomorrow after I test it. Right now, it fails on the db.execute ("Delete......     line but I think that is because the lines need deleted first.

Thank you
Steph_MAuthor Commented:
Thank you for providing the assistance in MS Access.
All Courses

From novice to tech pro — start learning today.