Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 05-07-2010, 04:27 AM   #1
RobinR
Robin Roelofsen
 
Join Date: Apr 2010
Location: Netherlands
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
RobinR is on a distinguished road
Cool Combining multiple records into one

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!

RobinR is offline   Reply With Quote
Old 05-07-2010, 05:29 AM   #2
HGMonaro
Newly Registered User
 
Join Date: Apr 2010
Posts: 61
Thanks: 2
Thanked 0 Times in 0 Posts
HGMonaro is on a distinguished road
Re: Combining multiple records into one

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)

Code:
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
HGMonaro is offline   Reply With Quote
Old 05-07-2010, 06:06 AM   #3
RobinR
Robin Roelofsen
 
Join Date: Apr 2010
Location: Netherlands
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
RobinR is on a distinguished road
Re: Combining multiple records into one

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 is offline   Reply With Quote
Old 05-07-2010, 07:06 AM   #4
RobinR
Robin Roelofsen
 
Join Date: Apr 2010
Location: Netherlands
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
RobinR is on a distinguished road
Unhappy Re: Combining multiple records into one

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?

Code:
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
  If Table1!WerkDatum <> Current_WerkDatum And Table1!nUserId <> Current_UserId Then
        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
RobinR is offline   Reply With Quote
Old 05-07-2010, 10:51 PM   #5
HGMonaro
Newly Registered User
 
Join Date: Apr 2010
Posts: 61
Thanks: 2
Thanked 0 Times in 0 Posts
HGMonaro is on a distinguished road
Re: Combining multiple records into one

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.

Code:
If Table1!WerkDatum <> Current_WerkDatum Or Table1!nUserId <>    Current_UserId Then
               Table2.Update
               Table2.AddNew
               Current_UserId = Table1!nUserId
               Current_WerkDatum = Table1!WerkDatum
               fieldctr = 1
    End If

Last edited by HGMonaro; 05-07-2010 at 11:03 PM.
HGMonaro is offline   Reply With Quote
Old 05-09-2010, 10:54 PM   #6
RobinR
Robin Roelofsen
 
Join Date: Apr 2010
Location: Netherlands
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
RobinR is on a distinguished road
Re: Combining multiple records into one

That was the solution! Thanks!!!

RobinR is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Combining Multiple Records Dumas.DED Queries 4 05-06-2010 11:35 AM
List box: combining columns from multiple tables where records not always cohesive... killyridols Forms 1 08-26-2009 06:01 PM
Transfer multiple records between subforms arm1 Forms 2 05-21-2009 07:50 AM
need a form design for multiple records diversoln Forms 0 11-02-2003 02:02 PM
[SOLVED] selecting multiple records in a subform Zigster Forms 2 12-11-2001 12:25 PM




All times are GMT -8. The time now is 04:15 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World