asked on

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 "anyone@myCompany.com". 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 Patel@myCompany.com 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.AddNew
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
rs2.Update
End If
End If
rs.MoveNext
Loop
End If

End Function

Thank you,
Steph_M
ExpExchSampleDB.accdb

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fabrice Lambert

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_M

ASKER

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?

Gustav Brock

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 Lambert

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
    '///////////////////////////////////

    rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set db = Nothing

Open in new window

Steph_M

ASKER

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_M

ASKER

Thank you for providing the assistance in MS Access.