Solved

convert list data set to a table record set

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

776 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