Using Jet SQL to Create a Numbered Hierarchy in an Access Table

[dgriffis1231]

I am trying to create a named hierarchy based off a table from an Oil & Gas Program ARIES. The code below is as far as I could get:

SELECT dbo_AC_ECONOMIC.PROPNUM 
        , dbo_AC_ECONOMIC.SEQUENCE 
        , dbo_AC_ECONOMIC.SECTION 
        , dbo_AC_ECONOMIC.QUALIFIER 
        , dbo_AC_ECONOMIC.KEYWORD 
        , dbo_AC_ECONOMIC.EXPRESSION  
        , (SELECT COUNT(*) 
                 FROM dbo_AC_ECONOMIC AS dbo_AC_ECONOMIC2 
             WHERE dbo_AC_ECONOMIC2.PROPNUM = dbo_AC_ECONOMIC.PROPNUM 
                AND dbo_AC_ECONOMIC2.KEYWORD = dbo_AC_ECONOMIC.KEYWORD 
                AND dbo_AC_ECONOMIC2.SECTION = dbo_AC_ECONOMIC.SECTION 
                AND dbo_AC_ECONOMIC2.SEQUENCE < dbo_AC_ECONOMIC.SEQUENCE)+1 AS TEST 
FROM dbo_AC_ECONOMIC 
ORDER 
        BY dbo_AC_ECONOMIC.PROPNUM 
          , dbo_AC_ECONOMIC.SEQUENCE;

The results are as such:

PROPNUM...SECTION...SEQUENCE...QUALIFIER...KEYWORD...EXPRESSION...TEST
TEST1.......4................1.................TAG.............GTC/GAS.......03.................1
TEST1.......4................2.................TAG............."..................22..................1
TEST1.......4................3.................TAG............."..................22..................2
TEST1.......4................4.................TAG.............SHRINK.........1...................1
TEST1.......5................1.................TAG.............ATX...............5...................1
TEST1.......5................2.................TAG.............ATX...............5...................2
TEST2.......4................1.................TAG.............GTC/GAS.......03.................1
TEST2.......4................2.................TAG............."..................22..................1

The desired Results:

PROPNUM...SECTION...SEQUENCE...QUALIFIER...KEYWORD...EXPRESSION...TEST...KEYWORD2
TEST1.......4................1.................TAG.............GTC/GAS.......03.................1........GTC/GAS_1
TEST1.......4................2.................TAG............."..................22..................2........GTC/GAS_2
TEST1.......4................3.................TAG............."..................22..................3........GTC/GAS_3
TEST1.......4................4.................TAG.............SHRINK........1...................1.........SHRINK_1
TEST1.......5................1.................TAG.............ATX..............5...................1........ATX_1
TEST1.......5................2.................TAG.............ATX..............5...................1........ATX_1
TEST2.......4................1.................TAG.............GTC/GAS.......03.................1........GTC/GAS_1
TEST2.......4................2.................TAG............."..................22..................2........GTC/GAS_2

First I will give background on the table and fields. Then I will explain the overall goal for creating of the TEST and KEYWORD 2 fields.

BACKGROUND
PROPNUM: UNIQUE ID
SECTION: Set of data responsible for a certain function EX: section 4 - expenses, section 5 - interest, etc.
SEQUENCE: The order of the sytanx in that propnum's section
QUALIFIER: Qualifies multiples set of syntax per section to differentiate other work (NOT REALLY IMPORTANT for the query)
KEYWORD: A specific word that the program recognizes and treates the expression according to the key word * the quotes keyword is a continuation line and represent the keyword above it*
EXPRESSION: are the variables that are treated by program according to the keyword

OVERALL GOAL:
The main goal is to have a spreadsheet of variables used by the program to calculate it's end result. Which means KEYWORD & EXPRESSION by PROPNUM. THE PROBLEM is that the only way to tell that a quote keyword belongs is by having the sequence and section lined up. So my solution is to rename the quote keyword with the primary keyword and a number.

Any help would be appreciated!

Thank-you,

DG

 

[spikepl]

If your goal is to produce from what is shown in the first table to what is shown in the second, then remove all other information (=columns etc) that is not pertinent, and describe the algorithm for each result in each column.

If your goal is to produce the output in the second table directly in teh query withot any intermediate steps, then I have no clue.

Your link, whose function is not clear, does not work for non-members.

 

[JHB]

Do you have some "raw"data we could play with?