?
Solved

convert list data set to a table record set

Posted on 2015-02-12
6
Medium Priority
?
252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 38

Assisted Solution

by:PatHartman
PatHartman earned 1500 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

752 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