E
Edward Letendre
Okay, I asked this question a few days ago and did not get a repy as of yet.
But I want to approach this problem from another perspective so here goes
again in a different way.
As I had said in my previous question, I need to have two groups of data
placed together in a query to be able to then export the data into an excel
spreadsheet and then as a text file or comma delimited file to be able to
access the information as it was before it was stored in the database in the
first place. I am dealing with an access 97 front end and a sql server 2000
back end. The tables in the sql server database are linked tables in the
access 97 database/front end.
In my case I need to create what you might call a header file or beginning
of each query. In this case, the query needs to be able to retrieve the
following information:
H - a single letter to indicate the header file, then the following columns
to make up the header file in the following order:
Line : followed by the name of line from line header table
client : followed by the name of the client from the client table
prospect : followed by the name of the prospect from the line header table
contractor : followed by the name of the contractor from the line processor
table
projection : followed by the projection information from the line
projection info table
There are a few other columns that make up a header but this is the basic
information. Once a single header is created in the intital query, a second
part of the query needs to be created so that the various lines can be
displayed, thus the relationship is one header to many lines. The line part
of the data will look like the following:
[LINE] [POINT] [LAT] [LONG] [EAST] [NORTH] [ELE]
*[COMMENT]
R01 1501 15634525 1001234510
R01 1502 15645526 1011234520
R01 1503 15847727 1021234710
As you can see from above the line stays the same and the point, lat, long
and other values change from shot point to shot point.
I was thinking that the query could be put together in the following way:
select line info (line_name, lat, long, east, north, comment from line
points table
where uniqueID.line points table = uniqueID from line header table
and uniqueID.line points table = (select header info from header table)
Thus a second select statement retrieves and displays the header information
first, followed by the line information. I also need to be able to add
column headings in
the line information that I mentioned above such as [LINE], [POINT], [LAT],
etc.
I have a background in oracle and this is beginning to make a little sense
for me as I remember building queries and or reports in SQL plus that is
included in oracle 8i and oracle 9i. I realize that sql plus is not the same
as access query and that you cannot do the same things with colunm headings,
btitiles, etc. that you can in sql plus (column headings, btitles, etc. are
all uniquie to SQL plus in oracle). But I hope that access does have some
simular query features that you can create a simular select statement. It is
just that I cannot remember exactly how to begin building this select
statement in SQL within access, but I have an idea and a starting point, so
could someone please get me started.
I guess the other possibility is the use access code and create the first
select statement and have a do while loop to retrieve the multiple lines
until there are no lines left for each header query, but I am not sure how I
would go about coding this as of yet and I still need a place to store the
results which I would hope to be excel so I can then take the data and export
it to a text file and a newer application, as this whole process is so that I
can export the data out of my access/SQL server 7 database and into another
application.
Edward Letendre.
But I want to approach this problem from another perspective so here goes
again in a different way.
As I had said in my previous question, I need to have two groups of data
placed together in a query to be able to then export the data into an excel
spreadsheet and then as a text file or comma delimited file to be able to
access the information as it was before it was stored in the database in the
first place. I am dealing with an access 97 front end and a sql server 2000
back end. The tables in the sql server database are linked tables in the
access 97 database/front end.
In my case I need to create what you might call a header file or beginning
of each query. In this case, the query needs to be able to retrieve the
following information:
H - a single letter to indicate the header file, then the following columns
to make up the header file in the following order:
Line : followed by the name of line from line header table
client : followed by the name of the client from the client table
prospect : followed by the name of the prospect from the line header table
contractor : followed by the name of the contractor from the line processor
table
projection : followed by the projection information from the line
projection info table
There are a few other columns that make up a header but this is the basic
information. Once a single header is created in the intital query, a second
part of the query needs to be created so that the various lines can be
displayed, thus the relationship is one header to many lines. The line part
of the data will look like the following:
[LINE] [POINT] [LAT] [LONG] [EAST] [NORTH] [ELE]
*[COMMENT]
R01 1501 15634525 1001234510
R01 1502 15645526 1011234520
R01 1503 15847727 1021234710
As you can see from above the line stays the same and the point, lat, long
and other values change from shot point to shot point.
I was thinking that the query could be put together in the following way:
select line info (line_name, lat, long, east, north, comment from line
points table
where uniqueID.line points table = uniqueID from line header table
and uniqueID.line points table = (select header info from header table)
Thus a second select statement retrieves and displays the header information
first, followed by the line information. I also need to be able to add
column headings in
the line information that I mentioned above such as [LINE], [POINT], [LAT],
etc.
I have a background in oracle and this is beginning to make a little sense
for me as I remember building queries and or reports in SQL plus that is
included in oracle 8i and oracle 9i. I realize that sql plus is not the same
as access query and that you cannot do the same things with colunm headings,
btitiles, etc. that you can in sql plus (column headings, btitles, etc. are
all uniquie to SQL plus in oracle). But I hope that access does have some
simular query features that you can create a simular select statement. It is
just that I cannot remember exactly how to begin building this select
statement in SQL within access, but I have an idea and a starting point, so
could someone please get me started.
I guess the other possibility is the use access code and create the first
select statement and have a do while loop to retrieve the multiple lines
until there are no lines left for each header query, but I am not sure how I
would go about coding this as of yet and I still need a place to store the
results which I would hope to be excel so I can then take the data and export
it to a text file and a newer application, as this whole process is so that I
can export the data out of my access/SQL server 7 database and into another
application.
Edward Letendre.