Programing Help Needed!

[Ed G]

Help requested!

I am not a programmer but I have the following crude code that I want to run
in an Access 2000 database. Can anyone clean it up and tell me where to place
it in Access 2000.

This program takes data out of the DESCRIPTION field (type memo) in the
SESSIONS Table and creates multiple new records in the DETAIL Table with a
common field PRKEY. The data is in a fixed length text format and contains
multiple records in one DESCRIPTION field, one record. Each set of data is 55
characters long, with multiple sets (total # of sets vary) . I am trying to
append this data into an existing table called DETAIL and have this data put
into multiple records with matching PRKEY.

Ed Grenzig


Start Program
Table Sessions
Table Detail
Dim m as integer, n as integer, F1 as integer, F2 as integer, F3 as Date, F4
as Date

m=1

;step through Sessions table one record at a time, to end of table
Select Sessions.PRKEY, Sessions.Description, Sessions.DetailCk
From Sessions
Where PRKEY=m

Do While Sessions!PRKEY <> null ;test for end of table (?)
If Sessions!DetailCk=True ;DetailCk indicates if this conversion was
already done.
go to Line2
end if

;get data out of Description Field and place in Detail Table many records.
N=0
Do while Mid$(Sessions!Description, n*55+30+1, 2) <> null ; test for end
of data (?)

F1= Sessions!PRKEY
F2=Mid$(Sessions!Description, n*55+30+6, 2)
F3= TimeSerial(Mid$(Sessions!Description, n*55+30+20, 11))
F4= TimeSerial(Mid$(Sessions!Description, n*55+30+43, 11))
Insert Into Detail(PRKEY, Lap, LapTime, SplitTime)
Values(F1, F2, F3, F4)
n=n+1
Loop

Sessions!DetailCk=True
Line2:
M=m+1
Select Sessions.PRKEY, Sessions.Description, Sessions.DetailCk
Where PRKEY=m
Loop
Close all
End Program

 

[jacksonmacd]

Ed - if you are uncomfortable with programming, perhaps you can
accomplish the same task using just queries. Experiment with a copy of
your database:

- make a query that extracts the information from your original table
in the format that you need
- change it into an append query to append it to the second table.

- how to generate the multiple records that you need?
- create a third tabld with any arbitrary fields, containing the
number of records that you want to create
- add the new table into your original query. No need to do any joins;
adding the table to the query is sufficient
- Magically, Access will create multiple records for each of the
original records

Help requested!

I am not a programmer but I have the following crude code that I want to run
in an Access 2000 database. Can anyone clean it up and tell me where to place
it in Access 2000.

This program takes data out of the DESCRIPTION field (type memo) in the
SESSIONS Table and creates multiple new records in the DETAIL Table with a
common field PRKEY. The data is in a fixed length text format and contains
multiple records in one DESCRIPTION field, one record. Each set of data is 55
characters long, with multiple sets (total # of sets vary) . I am trying to
append this data into an existing table called DETAIL and have this data put
into multiple records with matching PRKEY.

Ed Grenzig


Start Program
Table Sessions
Table Detail
Dim m as integer, n as integer, F1 as integer, F2 as integer, F3 as Date, F4
as Date

m=1

;step through Sessions table one record at a time, to end of table
Select Sessions.PRKEY, Sessions.Description, Sessions.DetailCk
From Sessions
Where PRKEY=m

Do While Sessions!PRKEY <> null ;test for end of table (?)
If Sessions!DetailCk=True ;DetailCk indicates if this conversion was
already done.
go to Line2
end if

;get data out of Description Field and place in Detail Table many records.
N=0
Do while Mid$(Sessions!Description, n*55+30+1, 2) <> null ; test for end
of data (?)

F1= Sessions!PRKEY
F2=Mid$(Sessions!Description, n*55+30+6, 2)
F3= TimeSerial(Mid$(Sessions!Description, n*55+30+20, 11))
F4= TimeSerial(Mid$(Sessions!Description, n*55+30+43, 11))
Insert Into Detail(PRKEY, Lap, LapTime, SplitTime)
Values(F1, F2, F3, F4)
n=n+1
Loop

Sessions!DetailCk=True
Line2:
M=m+1
Select Sessions.PRKEY, Sessions.Description, Sessions.DetailCk
Where PRKEY=m
Loop
Close all
End Program

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security