Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

convert list data set to a table record set

Good Afternoon,

I have a list of data that needs to be converted into records and need help with the code construction.

The initial data set as list:
f1	f2
1	1/12/04 
2	11:00 AM 
3	EST 
4	Confirmed 
5	(Download 
6	appointment to 
7	Outlook or Palm) 
8	Location: 
9	Event: Hearing PROVISIONAL ORDER HEARING 
10	Length: 
11	Hearing Type: PR 
12	1/23/04 
13	10:00 AM 
14	EST 
15	Confirmed 
16	Location: 
17	Event: Hearing PROVISIONAL ORDER HEARING 
18	Length: 
19	Hearing Type: PR 
20	6/21/04 
21	9:00 AM 
22	EST 
23	Cancelled 
24	Location: 
25	Event: Hearing PROVISIONAL ORDER HEARING 
26	Length: 
27	Hearing Type: PR 
28	6/29/04 
29	9:00 AM 
30	EST 
31	Cancelled 
32	Location: 
33	Event: Hearing FINAL HEARING ON DIVORCE 
34	Length: 
35	Hearing Type: FH 
36	7/13/04 

Open in new window

The data needs to get transformed into the below:
f1                   f2                f3            f4            f5                                                                                 f6                  f7
1/12/04   11:00 AM 	EST 	Confirmed (Download appointment to 7	Outlook or Palm)    Location: 	Event: Hearing PROVISIONAL ORDER HEARING 
10	Length: 
11	Hearing Type: PR 

Open in new window

and so on, until the code picks up on the next line that contains a date, then creates a new record.

I was thinking of doing something like: 'vba', 'dao'
dim db1 as dao.recordset
dim db2 as dao.recordset
dim tbl1 as string
dim tbl2 as string
dim autonum as string

set db1 = currentdb.openrecordset ("NameOfTable", dbopendynaset)
set db2 = currentdb.openrecordset ("NameOfTable2", dbopendynaset)

db1.movefirst
db2.movefirst

set autonum = db1.[f1]

Do while not db1.EOF

If db1.FieldName <> '##/##/####' then 'seeking the date as the first field of the record

set autonum = db1.[f1]
   with db2
   .addnew
db2("Field1").value = db1![field1]

db1.movenext

Open in new window

I need to figure out the rest of the code logic.

Any help would be most appreciated.

Thank you. !
0
Myrocco
Asked:
Myrocco
  • 2
2 Solutions
 
PatHartmanCommented:
Your outer loop reads the individual records as you are doing.  You also need an inner loop that examines each "record" to see if it is a date.  Use the IsDate() function to determine if a value is a valid date.  When you find a date, you have to write out the previous record, if there is one.  You then have to start accumulating the fields for the next record.
0
 
MyroccoAuthor Commented:
Good Afternoon,

I figured it out.
Dim dbVita As DAO.Recordset
Dim dbUnion As DAO.Recordset
Dim tbl1 As String
Dim tbl2 As String
Dim autonum As String
On Error Resume Next
Set dbVita = CurrentDb.openrecordset("09252009Vita", dbopendynaset)
Set dbUnion = CurrentDb.openrecordset("Union", dbopendynaset)

dbVita.MoveLast
FindRecordCount = dbVita.RecordCount
dbVita.MoveFirst
If Not dbUnion.EOF Then
dbUnion.MoveFirst
End If
I = 1
dbUnion.AddNew
Do While Not dbVita.EOF
If IsDate(dbVita![f1]) = True And I <> 1 And I <> 2 Then
        dbUnion.Update
        dbUnion.AddNew
        I = 1
    End If
    dbUnion.Fields(I).Value = dbVita![f1]
    I = I + 1
    dbVita.MoveNext
Loop

Set dbUnion = Nothing
Set dbVita = Nothing

MsgBox "Completed"
0
 
MyroccoAuthor Commented:
I created the code that got the job done.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now