E
Edward Letendre
Hello, My name is Edward Letendre. I had been working with this group to
solve my problem with a conversion process for access 97 to access 2000. The
conversion project went very well up to this point, but I have a new problem
I was hoping that the experts here could address:
I have a set of tables in access 97 that contains various seismic data. In
my case, I have a series of tables that when combined make up a header file.
for all of the data in the header file, I have a second table that has many
seismic lines, associated with the single header file mentioned above. Thus
the relationship is one header file to many lines records.
My first goal here is to gather the data that makes up a header file and
send it to an excel file. My second goal is then to add all of the line
records to that header file and have it saved as a single excel file.
The structure of the basic header file is as follows:
line header with the following structure:
UniqueId
LineName
Prospect
ClientCode
and other columns about the header
The clientCode column can be linked to the clients table and give you the
name of the client
The UniqueId and station value from the line points can then be linked back
to the line_header table to create the one to many relationship. And in this
case there are usually many stations (or if you will shot points) associated
with a single header as described above.
The output from such a connection should look like the following (not the H
in the first part identifies this as the Header section):
H
Line : R05
Client : Seis Energry
Prospect : Garfield_01
Contractor : Field Services
[Line ][Point] [LAT] [LONG] [EAST] [NORTH] [ELE] *[COMMENT]
R05 1201 45331529 100753410
R05 1202 45332029 100773459
R05 1203 45769910 100734598
As for programming, I was thinking of creating the following type of code
header_count as Interger
line_count as Integer
header_count = 1
line_count = 1
loop until no headers are found (not sure how to phrase this start of the
loop)
--the start of the loop will go until there are not more header files
available
select line, client_name, prospect, etc..
from line_header, client
where line_header.client_code = clients.client_code
and line_header.UniqueId = head_count
(note the data from above would have to be loaded into a variable and then
the data needs to be written to an excel file)
second loop here to run until there are no line files associated with the
header file:
do until no lines found
select LineName, station, Latitute, Longitude, north_local, east_local,
elevation
from Line_header, line_points
where line_header.uniqueid = line_points.uniqueid
and line_points.uniqueid = line_count
the data from above would then have to be appended to the same excel file
that was created in the loading the header part of the loop)
line_count = line_count + 1
loop
Then before starting the next loading of the header and after the line
points or stations are all loaded and saved into the excel file, the excel
file should be closed and a new excel file should be created again in the
loading of the header file section.
Thus if this works the way I hope it does, I would end of with a series of
excel files with one header record for each of the header records in the
line_header table (each line header table can contain anywhere from 100 to
3500 header records) and each line point table can have multiple line point
entries for a single line header record.
I hope that this makes sense to someone, but the ultimate goal here is to
create a basic seg p1 file from the tables into excel and then into a text
file of some kind to be loaded in another application.
Edward Letendre.
solve my problem with a conversion process for access 97 to access 2000. The
conversion project went very well up to this point, but I have a new problem
I was hoping that the experts here could address:
I have a set of tables in access 97 that contains various seismic data. In
my case, I have a series of tables that when combined make up a header file.
for all of the data in the header file, I have a second table that has many
seismic lines, associated with the single header file mentioned above. Thus
the relationship is one header file to many lines records.
My first goal here is to gather the data that makes up a header file and
send it to an excel file. My second goal is then to add all of the line
records to that header file and have it saved as a single excel file.
The structure of the basic header file is as follows:
line header with the following structure:
UniqueId
LineName
Prospect
ClientCode
and other columns about the header
The clientCode column can be linked to the clients table and give you the
name of the client
The UniqueId and station value from the line points can then be linked back
to the line_header table to create the one to many relationship. And in this
case there are usually many stations (or if you will shot points) associated
with a single header as described above.
The output from such a connection should look like the following (not the H
in the first part identifies this as the Header section):
H
Line : R05
Client : Seis Energry
Prospect : Garfield_01
Contractor : Field Services
[Line ][Point] [LAT] [LONG] [EAST] [NORTH] [ELE] *[COMMENT]
R05 1201 45331529 100753410
R05 1202 45332029 100773459
R05 1203 45769910 100734598
As for programming, I was thinking of creating the following type of code
header_count as Interger
line_count as Integer
header_count = 1
line_count = 1
loop until no headers are found (not sure how to phrase this start of the
loop)
--the start of the loop will go until there are not more header files
available
select line, client_name, prospect, etc..
from line_header, client
where line_header.client_code = clients.client_code
and line_header.UniqueId = head_count
(note the data from above would have to be loaded into a variable and then
the data needs to be written to an excel file)
second loop here to run until there are no line files associated with the
header file:
do until no lines found
select LineName, station, Latitute, Longitude, north_local, east_local,
elevation
from Line_header, line_points
where line_header.uniqueid = line_points.uniqueid
and line_points.uniqueid = line_count
the data from above would then have to be appended to the same excel file
that was created in the loading the header part of the loop)
line_count = line_count + 1
loop
Then before starting the next loading of the header and after the line
points or stations are all loaded and saved into the excel file, the excel
file should be closed and a new excel file should be created again in the
loading of the header file section.
Thus if this works the way I hope it does, I would end of with a series of
excel files with one header record for each of the header records in the
line_header table (each line header table can contain anywhere from 100 to
3500 header records) and each line point table can have multiple line point
entries for a single line header record.
I hope that this makes sense to someone, but the ultimate goal here is to
create a basic seg p1 file from the tables into excel and then into a text
file of some kind to be loaded in another application.
Edward Letendre.