Pulling data from one table and putting into another...

[bradsr0138]

Hi all,

I want to thank anyone who might be able to help me with this and appreciate you taking the time to read my problem.

Ok, I have a table in my DB that is populated from a word form that I have created. In the form I have 7 "Date" fields that are filled by a user and 7 "Hours" fields that are also filled by the user. I have the same 14 columns in my table that correspond to the fields in the form. Some other fields on the form/columns in the table are "Inspector", "Project Number", "Report Number", and so on.

I would like to generate a table or report, whichever is easiest, that will list the data by date instead of by report. When listed by report, I have to show all 7 days and hours for those days. Any help will be greatly appreciated. I can send the DB to whomever would like to help with it.

Thank you, Brad

 

[ezfriend]

Post your db here so everyone can see it.

 

[bradsr0138]

Sorry ab out that... See Attached. I haven't created the other table yet, but basically I want to break each record in one table into 7 records in the new table, where Date1 corresponds to Hours1 and so on.

thanks again for any help.

 

[ezfriend]

Can you just sort the data by Date1?

 

[bradsr0138]

What I need is to turn (1) one record into (7) records. For example, the (1) record contains 7 "Date" fields (Date1, Date2, Date3...) and also contains 7 "Hours" fields (Hours1, Hours2, Hours3...). I would like to split that 1 record into seven records that look something like the following:

Record 1: Date1 : Hours1
Record 2: Date2 : Hours2
Record 3: Date3 : Hours3
.
.
.
and so on.

 

[bradsr0138]

Can no one figure this out, or at least point me in the right direction to figure it out for myself?

 

[Scooterbug]

First impressions is to write some code to loop through each record and insert the data into the new table. The only problem I can see is what to use as a foreign key in the new table so that you can relate the data together.

UPDATE:

Here is code that will go through each record and take the date and time and put them into another table. Note that the code does add anything else but the stripped out time and date. You will need to add in a field for whatever you are using to link the tables together.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim dtmDate As Date
Dim sinHours As Single
Dim i As Integer
Dim DateField As Variant
Dim HourField As String

Set db = CurrentDb
Set rs = db.OpenRecordset("Query1")
Set rs2 = db.OpenRecordset("tblDateHours")
With rs
.MoveFirst
i = 1
Do
    Do Until .EOF
      If i < 8 Then
        Select Case i
         Case 1
            dtmDate = ![Date1]
            sinHours = ![Hours1]
            rs2.AddNew
            rs2.Fields("RecordedDate") = dtmDate
            rs2.Fields("RecordedHours") = sinHours
            rs2.Update
            i = i + 1
         Case 2
            dtmDate = ![Date2]
            sinHours = ![Hours2]
            rs2.AddNew
            rs2.Fields("RecordedDate") = dtmDate
            rs2.Fields("RecordedHours") = sinHours
            rs2.Update
            i = i + 1
        Case 3
            dtmDate = ![Date3]
            sinHours = ![Hours3]
            rs2.AddNew
            rs2.Fields("RecordedDate") = dtmDate
            rs2.Fields("RecordedHours") = sinHours
            rs2.Update
            i = i + 1
         Case 4
            dtmDate = ![Date4]
            sinHours = ![Hours4]
            rs2.AddNew
            rs2.Fields("RecordedDate") = dtmDate
            rs2.Fields("RecordedHours") = sinHours
            rs2.Update
            i = i + 1
         Case 5
            dtmDate = ![Date5]
            sinHours = ![Hours5]
            rs2.AddNew
            rs2.Fields("RecordedDate") = dtmDate
            rs2.Fields("RecordedHours") = sinHours
            rs2.Update
            i = i + 1
         Case 6
            dtmDate = ![Date6]
            sinHours = ![Hours6]
            rs2.AddNew
            rs2.Fields("RecordedDate") = dtmDate
            rs2.Fields("RecordedHours") = sinHours
            rs2.Update
            i = i + 1
         Case 7
            dtmDate = ![Date7]
            sinHours = ![Hours7]
            rs2.AddNew
            rs2.Fields("RecordedDate") = dtmDate
            rs2.Fields("RecordedHours") = sinHours
            rs2.Update
            i = i + 1
         End Select
       Else
        .MoveNext
        i = 1
       End If
    Loop
Loop Until .EOF
End With

Set db = Nothing
Set rs = Nothing
Set rs2 = Nothing