Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 29th, 2004, 10:35 AM
Authorized User
 
Join Date: Sep 2003
Location: Upper Marlboro, Maryland, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default Append Records From One Table to Another Table

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

  #2 (permalink)  
Old February 29th, 2004, 11:08 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #3 (permalink)  
Old February 29th, 2004, 12:57 PM
Authorized User
 
Join Date: Sep 2003
Location: Upper Marlboro, Maryland, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #4 (permalink)  
Old February 29th, 2004, 02:56 PM
Authorized User
 
Join Date: Nov 2003
Location: Commerce Twp, MI, USA.
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #5 (permalink)  
Old February 29th, 2004, 03:04 PM
Authorized User
 
Join Date: Sep 2003
Location: Upper Marlboro, Maryland, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Darrell,

I'll try it!

David



Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert table records into another table. hewstone999 Access VBA 2 March 5th, 2008 11:01 AM
create table/append data. bpdineen Access VBA 1 January 4th, 2007 11:47 AM
Append Query to Table ! penta Access 3 February 24th, 2005 04:24 PM
copy and append records from table-A to table B bhunter Access 6 March 9th, 2004 02:02 PM
thousands records enter one table to another table mateenmohd SQL Server 2000 3 July 17th, 2003 07:52 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.