Solved

convert list data set to a table record set

Posted on 2015-02-12
6
247 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 35

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

791 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