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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Why not simply filter on the e-mail address:

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

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.