Solved

convert list data set to a table record set

Posted on 2015-02-12
6
221 Views
Last Modified: 2016-02-12
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
Comment
Question by:Myrocco
  • 2
6 Comments
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
ID: 40608824
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
 

Accepted Solution

by:
Myrocco earned 0 total points
ID: 40625670
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
 

Author Closing Comment

by:Myrocco
ID: 40641557
I created the code that got the job done.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now