How to normalize monthly data in an ordered numerical sequence???

[carojasa]

I am desperate!!!


I need an expert to help me solve my problem. I am a geologist using an access database with very little programming knowledge. I will be extremely grateful if you can help me through this obstacle.

1. The setup of my table is shown in the picture

I have a number of well locations listed as "CAP Pretty Well ID's". These IDS occur many times in this particular table because they contain data recorded in a historical month -to-month basis in which the total number of hours of operation per month were logged. (I am basically explaning the content of my table from left to right so you can understand)

The field containing the dates, listed in yyyy-mm is of no use to me. Example: For ID 1171 i have dates from 2014-06, 2014-07......., 2014-09

I would like to have a column populated with numbers from 1 to n.... indicating to me that 2014-06 is month 1, 2014-07 is month 2 etc etc etc....
I have spend two days trying to do this myself and I am frustrated.

Can you help me please? Step by step instructions would be appreciated.

[June7]

This is ranking records. For some techniques, review http://allenbrowne.com/ranking.html

Should the records be sorted by yyyy-mm for the numbering? Should the numbering be within each ID group?

[orange]

Well, let's start by having you define/describe the problem in plain English. Changing that column to 1...n is not the issue. We want to know WHAT you are trying to accomplish in business terms, plain English, no jargon. You have told us HOW you think you will solve something. It's the something we're trying to understand. What does the table represent?

[carojasa]

The way i got the data was in YYYY-MM . Basically, I have wells from the 1960's which have operated for decades! As shown in the example, Monthly HRS of operation is one of the many parameters recorded.
The numbering should be within each ID group. For example, ID 1171 should have 5 records numbered 1,2......,5 and ID 3045 should have 6 records numbered 1,2.....,6.

[orange]

What if you had 2 tables. Normalized

Wells and WellsProduction. And then joined the tables on WellId and ordered the data by Date ascending.

where Wells

WellID
WellName

And WellsProduction
WellID
Date (reserved word)
PRDMonthlyHours_hrs

[carojasa]

Dear Orange. I apologize.
The table represents monthly hours of operation per well ID arranged from Oldest to Newest.
I need to rank my data from oldest to newest within each ID. Thank you for your help.

[June7]

Did you review the link in post 2?

[carojasa]

Dear all.
I will attempt to clarify my problem and clear up any confusion.

My ID's contain data arranged by date. The dates are ordered from oldest to newest. I have 1000's of IDs
The records contained in the Date Field are in the format YYYY-MM
I need to keep the order in which the data is stored BUT I need to create a new column that can rank the records within each ID from oldest to newest starting with number 1 to number XXX.

I hope this is better explained.

[orange]

Can you post some of your sample data in csv format. (or txt)?
I'd still like to know WHAT your objective is.

[carojasa]

Reading through the link. I don't really understand it much

[June7]

Keep in mind that tables don't really have a 'stored order'. Order is defined by sort criteria.

[carojasa]

Sample.txt


took me awhile to upload the sample file. sorry

[carojasa]

I hope the file clears up the confusion.
Basically.
Thousands of ID's with monthly data.
I want to set the sort criteria from Oldest to Newest and then get access to provide me with a column in which the records are ranked from 1 to XX within each ID number

[June7]

Don't think we are confused. The referenced link provides techniques for dynamic sequential numbering of records within groups. However, if you want to commit that number to table, will probably require VBA code.

[carojasa]

I dont think you are confused June