Group records and put into different tables in access

Question

I have a database with two tables. (5,034,139 & 3,297,127 rows in each table) In these tables there are report names (~1000 different reports) and log dates. I want to create new tables for each report name and each table should contain log dates of the reports.

Let's assume that there are just two reports "a" and "b". Log dates for report "a" are 01.01.2014, 03.04.2014 and 05.06.2014. Log dates for report "b" are 04.07.2013, 06.08.2014 and 02.03.2014.

I want to create two tables / queries. In one table / query there should be only report a's log dates and in the other table there should be only report b's log dates. Like SELECT * FROM Table1 AND SELECT * FROM Table2 WHERE REPORT = "a" for query1 and SELECT * FROM Table1 AND SELECT * FROM Table2 WHERE REPORT = "b" for query2 But I have to do this for more than a thousand report name.

You will need to provide more information. What is your question? — Josh Miller
You would more likely wish to create a simple select query that will return the many datasets. — Gustav
Yes I want to create a simple select query that will return many datasets. — Ogan
Your description still lacks information to assist you in writing the queries. Please provide the field names from the tables. — Josh Miller
LOG_DATE, USER_NAME and REPORT_NAME are the field names. Table names are 21994_2000 & 21994_2001. — Ogan

Hannover Fist Hannover Fist · Accepted Answer · 2015-04-02T20:08:34

OK - this is actually kinda complicated but what you'll want to do is create a loop that loops through your records by Report Name (to get the names to create the tables) then execute a Select Into to create a new table with the records for each report.

You should end up with something like:

Dim rs As DAO.Recordset
Dim strSQL as string
Dim ReportName as string

Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT ReportName FROM TABLE1 UNION SELECT DISTINCT ReportName FROM TABLE2 ")

docmd.SetWarnings false

If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until rs.EOF = True

        ReportName = rs("ReportName") 'GET THE REPORTNAME

        strSQL="SELECT LogDates INTO " & ReportName & _
        "FROM (SELECT LogDates FROM Table1 " & _
        "WHERE ReportName = '" & ReportName & "' " & 
        "UNION "
        "SELECT LogDates FROM Table1 " & _
        "WHERE ReportName = '" & ReportName & "') " & 

        DoCmd.RunSql strSQL
        Docmd.setwarnings True

        'Move to the next record. Don't ever forget to do this.
        rs.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If

MsgBox "Finished looping through records."

rs.Close 'Close the recordset
Set rs = Nothing 'Clean up

You'll have to use your table and field names but this should give you the basics of what you need to do.

Group records and put into different tables in access

1 Answers