Combining multiple records into one

[RobinR]

I have read questions regarding similar issues on this forum, but haven't found the solution to my problem... Hope somebody can help!

I have Table1 with layout:

UserID EventID DateTime

For each user id, there will be multiple records for each date, so e.g.

10---47---2-1-2010 10:47:03
10---48---2-1-2010 12:36:38
10---52---2-1-2010 13:06:21

(Dashed are there to show you the separate fields Copy and paste works when composing but not when posting!)

I have created Table2 with the following layout:

WorkDate---UserID---Event01---DateTime01--- Event02---DateTime02---Event03---DateTime03 etc...

Event and DateTime fields run to number 20, which is a number that will never be reached.

I want to combine the records in Table1 per date per user, and put the first record's data in Event01 and DateTime01, the second record's data in Event02 and DateTime02, etcetera, so the previous three records will be in Table2 as follows:

2-1-2010---10---47---2-1-2010 10:47:03---48---2-1-2010 12:36:38---52---2-1-2010 13:06:21

Since I am a VBA novice, I haven't been able to find a way to do this, and hope you guys can help me out here.

Thanks in advance!

 

[HGMonaro]

someones going to ask you why you want to do this...

however, I've had to do it also... here's some code that will get it done (expand the case statement to handle the number of field you need to deal with)

Function ProcessTable1()

Dim db As Database
Dim Table1, Table2 As Recordset
Dim Current_UserId, fieldctr As Variant
 
' open tables
Set db = CurrentDb
Set Table1 = db.OpenRecordset("Table1")
Set Table2 = db.OpenRecordset("Table2")
 
' set fields and add a record
Current_UserId = Table1!UserId
fieldctr = 1 ' use to keep track of what set of fiels to put the data in
Table2.AddNew

' loop around Table1 for all records
Do Until Table1.EOF
   
    ' if the UserId changes start a new Table2 record
    If Table1!UserId <> Current_UserId Then
        Table2.Update
        Table2.AddNew
        Current_UserId = Table1!UserId
        fieldctr = 1
    End If
   
   Table2!WorkDate = Table1!DateTime
   Table2!UserId = Table1!UserId
   
   Select Case fieldctr ' select which fields to put the data in baased on the fieldctr field
   Case 1
        Table2!Event01 = Table1!EventId
        Table2!Datetime01 = Table1!DateTime
   Case 2
        Table2!Event02 = Table1!EventId
        Table2!Datetime02 = Table1!DateTime
   Case 3
        Table2!Event03 = Table1!EventId
        Table2!Datetime03 = Table1!DateTime
   End Select
   
   fieldctr = fieldctr + 1
   Table1.MoveNext
Loop
 
Table2.Update

' clean up by closing tables
Table1.Close
Table2.Close
Set Table1 = Nothing
Set Table2 = Nothing

End Function

 

[RobinR]

Thanks HGMonaro! Will try it.

The reason I want to do this is that this makes it easier to calculate differences between the dates from different records. Having them all in one record makes making my selections and if statements that much easier (I hope...).

Thanks again!

 

[RobinR]

Okay, I adapted the function, and it does not work as it should...

The records in Table2 need to be unique for the combination WerkDatum (workdate) and Werknemer (UserId), so I changed the code in red to reflect the combination, plus all the other code to get the date field in there, of course.

Now I get only one line per userid for only one date, each field filled!

What am I doing wrong?

Function ProcessTable1()
Dim db As Database
Dim Table1, Table2 As Recordset
Dim Current_UserId, Current_WerkDatum, fieldctr As Variant
 
' open tables
Set db = CurrentDb
Set Table1 = db.OpenRecordset("Urenberekening - Temp")
Set Table2 = db.OpenRecordset("Urenberekening")
 
' set fields and add a record
Current_WerkDatum = Table1!WerkDatum
Current_UserId = Table1!nUserId
fieldctr = 1 ' use to keep track of what set of fields to put the data in
Table2.AddNew
' loop around Table1 for all records
Do Until Table1.EOF
 
    ' if the UserId changes start a new Table2 record
[COLOR=red]  If Table1!WerkDatum <> Current_WerkDatum And Table1!nUserId <> Current_UserId Then[/COLOR]
        Table2.Update
        Table2.AddNew
        Current_UserId = Table1!nUserId
        fieldctr = 1
    End If
 
   Table2!WerkDatum = Table1!WerkDatum
   Table2!Werknemer = Table1!nUserId
 
   Select Case fieldctr ' select which fields to put the data in based on the fieldctr field
   Case 1
        Table2!TAEvent01 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime01 = Table1!DateTime
   Case 2
        Table2!TAEvent02 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime02 = Table1!DateTime
   Case 3
        Table2!TAEvent03 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime03 = Table1!DateTime
   Case 4
        Table2!TAEvent04 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime04 = Table1!DateTime
   Case 5
        Table2!TAEvent05 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime05 = Table1!DateTime
   Case 6
        Table2!TAEvent06 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime06 = Table1!DateTime
   Case 7
        Table2!TAEvent07 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime07 = Table1!DateTime
   Case 8
        Table2!TAEvent08 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime08 = Table1!DateTime
   Case 9
        Table2!TAEvent09 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime09 = Table1!DateTime
   Case 10
        Table2!TAEvent10 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime10 = Table1!DateTime
   Case 11
        Table2!TAEvent11 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime11 = Table1!DateTime
   Case 12
        Table2!TAEvent12 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime12 = Table1!DateTime
   Case 13
        Table2!TAEvent13 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime13 = Table1!DateTime
   Case 14
        Table2!TAEvent14 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime14 = Table1!DateTime
   Case 15
        Table2!TAEvent15 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime15 = Table1!DateTime
   Case 16
        Table2!TAEvent16 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime16 = Table1!DateTime
   Case 17
        Table2!TAEvent17 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime17 = Table1!DateTime
   Case 18
        Table2!TAEvent18 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime18 = Table1!DateTime
   Case 19
        Table2!TAEvent19 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime19 = Table1!DateTime
   Case 20
        Table2!TAEvent20 = Table1!nDeviceEventKeyIdn
        Table2!TADatetime20 = Table1!DateTime
    End Select
 
   fieldctr = fieldctr + 1
   Table1.MoveNext
Loop
 
Table2.Update
' clean up by closing tables
Table1.Close
Table2.Close
Set Table1 = Nothing
Set Table2 = Nothing
End Function

 

[HGMonaro]

I think your AND needs to be an OR. This is because if the date changes, you want a new record (even if the user hasn't) or if the user changes (Date could be the same I gather). Also, add your current date assignment to make sure you've got the right date to test.

If Table1!WerkDatum <> Current_WerkDatum [COLOR=Red]Or[/COLOR] Table1!nUserId <>    Current_UserId Then
               Table2.Update
               Table2.AddNew
               Current_UserId = Table1!nUserId
               [COLOR=Red]Current_WerkDatum = Table1!WerkDatum
[/COLOR]               fieldctr = 1
    End If

 

[RobinR]

That was the solution! Thanks!!!