Renaming Query

[dgriffis1231]

I need a field that will rename the " to reflect its keyword. So any word above the " needs to be renamed to the word plus a descending numbering system. For example:

KEYWORD
OPC/T
"
"
"
"
"
"
"
OPC/GAS

Desired Result

KEYWORD
OPC/T
OPC/T_1
OPC/T_2
OPC/T_3
OPC/T_4
OPC/T_5
OPC/T_6
OPC/T_7
OPC/GAS

Any suggestions would be appreciated

 

[dgriffis1231]

So it will be in order from top to bottom. To help you better understand the quote marks note a continuation line in a economics module of a program called ARIES. So if I type the First keyword OPC/T but I would like to continue to another line I put ".

For Example:

PROPNUMSECTIONSEQUENCEQUALIFIERKEYWORDEXPRESSION2176PRE00003620ACTIVEWLS/OIL19 X FRAC 01/2014 AD 2176PRE00003622ACTIVE"X 2 FRAC X AD EXP 42176PRE00003624ACTIVE"2 2 FRAC TO LIFE PC 0

These sets of lines are in a table and it is hard to identify what quote marks line up with what Keyword and expression. The way the machine does it is by sequencing the number in descending order and read from left to right/ up to down.

The desired effect would be as follows:

PROPNUMSECTIONSEQUENCEQUALIFIERKEYWORDEXPRESSION2176PRE00003620ACTIVEWLS/OIL19 X FRAC 01/2014 AD 2176PRE00003622ACTIVEWLS/OIL_1 X 2 FRAC X AD EXP 42176PRE00003624ACTIVEWLS/OIL_2 2 2 FRAC TO LIFE PC 01071172217634ACTIVESTX/NGL7.5 X % TO LIFE PC 0

*The Key is the logic would know only to alter the " lines and nothing else.*

Please let me know if you need further clarification. FYI all the other fields are noise for the most part. The renaming of the Keyword field is the goal.

 

[dgriffis1231]

This is my stab at the logic. I am just not sure how to put it into Access.

A
B
B
B
D

Look for B ...>

(1)if current value does not equal B but the next value does then stays the same.
(2)if current value does equals B and the one above does not then the result should be the value above concatenate "_1" =A_1
(3)if the value above contains an "_" then 1+ end value results = A_2
(4)if current value does not equal B but the previous does then it should be the same value.

Some of that logic is redundant and not needed but below is the desired result.

A
A_1
A_2
A_3
D

 

[Galaxiom]

This process is best done with a loop through a recordset, applying the logic you have explained.

Be sure to order the recordset or you could get random results.

 

[Cronk]

You did not say whether your data is in an Access table. What defines the "order"? Do you have a primary key or are you leaving it to Access to determine the order?

The supplied the sample data
2176PRE00003620ACTIVEWLS/OIL19 X FRAC 01/2014 AD 2176PRE00003622ACTIVE"X 2 FRAC X AD EXP 42176PRE00003624ACTIVE"2 2 FRAC TO LIFE PC 0

Is that all in one field or is it a concatenation of all the fields?

 

[dgriffis1231]

Sorry Cronk that was a bad transfer of data. I tried to copy past strait form Access.

 

[dgriffis1231]

So I have a simplified my need. I need a query that creates a numbered list of a certain variable. For Example:


Field_A....RESULT
A
B............1
B............2
B............3
B............4
C
B............1
B............2
D

 

[Cronk]

Firstly, why do the B1 and B2 follow the C? Should they not be C1 and C2?

I don't think you can achieve what you want with a query. I know I'd use VBA to open and loop through a recordset, storing the change in the prefix and incrementing a suffix while the successive values were the same.

You might want to post in the Modules forum.