Append Records From One Table to Another Table

twsinc · February 29th, 2004, 10:35 AM

Dear Forum Members

I have a table that identifies all the tasks that my supervisors are responsible for during the course of the year. Some of those tasks are performed Daily (âDâ), Twice Daily (â2Dâ), Weekly (âWâ), Monthly (âMâ), Quarterly (âQâ), Semi-Annually (âSAâ), Annually (âAâ). The table structure looks like the following:

Table1:

Building
Supervisor
Floor
Area
Task1_Frequency
Task1
Task2_Frequency
Task2
Task3_Frequency
Task3
.
.
.
Task12_Frequency
Task12

I created 12 queries, one for each task frequency and associated task, which are further used to print 12 reports. This effort works, but it requires manual collation since each report only prints one task. Once the 12 reports are printed, my secretary has to manually collate each report by supervisor, so the supervisor can have a monthly report of all the work he/she is responsible to complete during the month.

I would like to modify the table so that one query will handle all tasks. It appears that the following strategy will work: create a new table that has the following structure:

Table 2:

Building
Supervisor
Floor
Area
Task_Freqency
Task,

then append each task frequency and associated task along with the Building, Supervisor, Floor and Area from Table1 to Table 2. This will obviously create 12 records in Table 2 for each record in Table 1. How can I use VBA to append the records from Table 1 to Table 2?

Thanks for saving me hours of needless headaches,

David

sal · February 29th, 2004, 11:08 AM

I will save you weeks of headaches.

Change the structure of table 1 to be the same as the structure oftable 2. Then you create another lookup table for your task, I am sure that you have a lis tof buildings, supervisors and areas.

This will give you a table that is more normalized and this will allow you to squery your data and report it easier. If you need a report for only one task, you can use a parameter query to only pull one task, or one supervisor, or one building area, etc. or any combination.

Try reading a bit on data normalization.

Sal

twsinc · February 29th, 2004, 12:57 PM

Thanks Sal,

How can I change the structure of Table 1 to look the same as Table 2 without losing the data that is already in Table 1? If I change the structure of Table 1, Wouldn't I need to add Task_Frequency and Task to Table 1 and delete Task1_Frequency/Task1 through Task12_Frequency/Task12 in Table 1?

David

embreyo · February 29th, 2004, 02:56 PM

Basically, you've created an array of the tasks and task frequency for each supervisor. While I agree with Sal that the best way to resolve your challenge would be to restructure your tables to normalize your data, sometimes that is much easier said than done. I have a situation similar to yours where I receive source files from several operating locations that I must import to a centralized database. Some of the file come nice and neat with one row of data for each person for each day hours were billed. And I have a few that send files with one row per person and columns for each date containing the number of hours billed on that date.

I built a procedure that first determines what file type is being imported, list type or array, by reviewing the column headings. If the procedure cycles through all the columns without finding a date for a column heading, then I know I have a list type table and call a procedure to import the data from the list. Likewise, if the procedure encounters a column header that is a date, I know I have an array type table and call a different procedure to import the data from the array.

The following is the procedure that imports the data from the array to my normalized data table.

I've made the changes to my code to match your scenario. However, this has not been tested!

Code:

Sub TaskFreq_Array()

Dim db As DAO.Database
Dim rsTbl1 As DAO.Recordset, rsTbl2 As DAO.Recordset
Dim strBldg As String, strSupv As String, strFloor As String
Dim strPrevSupv As String, strArea As String
Dim strTaskFreq as String, strTask as String
Dim blnSameSupv As Boolean
Dim intTask As Integer
Dim fld As Field

    DoCmd.Hourglass True

    Set db = CurrentDb()

    Set rsTbl1 = db.OpenRecordset("Table1", dbOpenDynaset)

    Set rsTbl2 = db.OpenRecordset("Table2", dbOpenDynaset)

    rsTbl1.MoveFirst

    Do until rsTbl1.EOF

        For Each fld In rsTbl1.Fields

            Select Case fld.Name

                Case "Building"

                    strBldg = fld.Value

                Case "Supervisor"

                    strSupv = fld.Value

                Case "Floor"

                    strFloor = fld.Value

                Case "Area"

                    strArea = fld.Value

                Case Else

                    intTask = fld.CollectionIndex

                    If (intTask Mod 2) = 0 Then

                        strTaskFreq = rsTbl1.Fields(intTask)

                    Else

                        strTask = rsTbl1.Fields(intTask)

                        With rsTbl2
                            .AddNew
                            !Building = strBldg
                            !Supervisor = strSupv
                            !Floor = strFloor
                            !Area = strArea
                            !Task_Frequency = strTaskFreq
                            !Task = strTask
                            .update
                        End With

                    End If

            End Select

        Next fld

        rsTbl1.MoveNext

    Loop

    rsTbl1.Close
    rsTbl2.Close

    Set rsTbl1 = Nothing
    Set rsTbl2 = Nothing

    Set db = Nothing

    DoCmd.Hourglass False

End Sub

I hope this helps.

Darrell L. Embrey

twsinc · February 29th, 2004, 03:04 PM

Thanks Darrell,

I'll try it!

David