creating a multi part query to be able to export complete data

Discussion in 'Microsoft Access Queries' started by Guest, Jun 6, 2005.

  1. Guest

    Guest Guest

    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
    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:

    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],

    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

    Edward Letendre.
    Guest, Jun 6, 2005
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Multi-Table Query/Data Access Page--can't edit data

    Guest, Jan 26, 2004, in forum: Microsoft Access Queries
    Jan 26, 2004
  2. Guest
    Lynn Trapp
    Oct 21, 2005
  3. RebekahK20_pontiac via

    Multi Part Query = No records

    RebekahK20_pontiac via, Aug 14, 2007, in forum: Microsoft Access Queries
    Aug 14, 2007
  4. Krazy Darcy

    part of multi search query non-functional

    Krazy Darcy, Aug 27, 2008, in forum: Microsoft Access Queries
    Krazy Darcy
    Aug 28, 2008
  5. chynewalker

    creating a query that lists drawings of a part #

    chynewalker, Jan 20, 2010, in forum: Microsoft Access Queries
    Jan 20, 2010

Share This Page