Find First Non Zero Value And Sum Sequential Values, Access 2010

[exo]

Ok, hopefully I can explain this clearly and concisely.

There is a commercial database in my company which I access through an ODBC connection with access. It tracks well production. I have mapped the database and figured out the tables relationships. I have been asked to find production sums for all wells. I can not change the way the data is stored. I tried asking about this problem on a different forum last week, and every answer told me the database was the problem... unfortunately I have no control over that :/

There are 2 tables:
Main
Hist

[MAIN] contains each well and info about the well (location, owner, etc).
[HIST] contains the history of that well (oil, gas, water production, etc)

[HIST] has a row for each year. So my query selects for the current year (2014).

There are twelve fields of interest. prod1$1, prod1$2, prod2$3... prod1$12.

These correspond to jan-dec.

I have to find the first non-zero entry. If production started in march, then prod1$3 would be the first non zero.



The end report requires three things.

Value of the first month of production (first non zero value)
Value of first three months of production summed (sum of first non zero value and the next two sequential months)
Value of first six months of production (sum of first non zero value and next five sequential months).

The only catch is, if there is not enough information to complete a sum (eg: not 6 months worth or values to sum), then it should not sum them at all. Rather it should return a coda like N/A to notify the user this can't be calculated yet.

I don't know if I should be attempting this via query or module. If using a module, the command Recordset.Seek Method (DAO) seemed like it might be useful, but I wasn't able to figure out how to use it. For a query, I had no idea where to start as I have never had to search for data across multiple fields before.

I don't have much experience with creating modules. I have simply found ones that work for other jobs and been able to modify them to fit my needs in the past. I'd say I am medium-level proficient with queries. But I don't know where to start in tackling this issue.

Assistance would be greatly appreciated.

[June7]

Yes, non-normalized data structure is a problem but since you cannot control that, must deal with. A UNION query can manipulate the data into a normalized structure. I don't know all your field names but something like:

SELECT [well ID field], [year field] AS ProdYr, 1 AS ProdMo, [prod1$1] AS ProdCost FROM Hist
UNION SELECT [well ID field], [year field], 2, [prod1$2] FROM Hist
UNION SELECT [well ID field], [year field], 3, [prod1$3] FROM Hist
UNION SELECT [well ID field], [year field], 4, [prod1$4] FROM Hist
UNION SELECT [well ID field], [year field], 5, [prod1$5] FROM Hist
UNION SELECT [well ID field], [year field], 6, [prod1$6] FROM Hist
UNION SELECT [well ID field], [year field], 7, [prod1$7] FROM Hist
UNION SELECT [well ID field], [year field], 8, [prod1$8] FROM Hist
UNION SELECT [well ID field], [year field], 9, [prod1$9] FROM Hist
UNION SELECT [well ID field], [year field], 10, [prod1$10] FROM Hist
UNION SELECT [well ID field], [year field], 11, [prod1$11] FROM Hist
UNION SELECT [well ID field], [year field], 12, [prod1$12] FROM Hist;

There is no wizard or builder for UNION. Must type or copy/paste into SQL View of query designer. There is a limit of 50 SELECT lines. Include other fields if needed.

Now use that query same as if it were a table (except cannot edit data with it) of normalized data. Use it in subsequent queries and in reports to get the desired output.

[rpeare]

what happens if you have, for instance, production that starts in january, has no production in february but has production in march and april and no other production for the year.

Let's say 500 in january, 0 in february, 750 in march, 500 in april.

Would the first 3 months be 1250? (monthly calendar production)
or would the first three months be 1750? (monthly production)

Similarly what if you had production in january, but no production in february through july, then production again for august through december, would the 6 month total just be the january (since no other production happened in the first 6 calendar months) or would it be the first 6 months of actual production numbers?

Here's an example database (I was working with the union query concept too)

Exo.zip

[exo]

Jun7 - thank you so much for the union query. It worked like a charm.

Now I have a columnar version of my data.

So now the question is.... I have this query saved. And then I use a second query to bring it in and select only records of a certain year. I'll use 2013 as an example sicne I have all 12 months of data there.

I need to calculate a sum for each [well ID field] (actual name = LSE_ID) based on the first months production that <> 0.

I suppose I could make that the select criteria? ... which would then only list months with production that have a valid number.... but that brings me to rpeare's question. I actually do need to sum up zero values IF they come after the initial non zero value.

So if we have:
Jan 0
Feb 0
Mar 100
Apr 0
May 100
Jun 100
Jul 0
Aug 100
Sep 0
Oct 100
Nov 100
Dec 100

Then here are my three numbers:
First month prod 100 (March)
First 3 months prod 200 (1 month with zero prod in there)
First 6 months prod 400 (2 months with zero prod in there)

So if I were to select for non zero prod first, this would not allow me to maintain them those zeros when it comes time to sum.

So my dilemma is:
Identify the first non zero value. Report It. Identify the next two values that proceed it. Sum them. Identify the next 5 values that proceed it. Sum them.

[June7]

You want cumulative sum every 3 months starting with first month not zero, meaning the month groups will not be the same for each well?

These are difficult requirements even for normalized data.

[exo]

You want cumulative sum every 3 months starting with first month not zero, meaning the month groups will not be the same for each well?

These are difficult requirements even for normalized data.

Yea... each well has it's own timeline. I don't think this is all that abnormal though. I mean, most sales databases can individually look at each products sales and chart them... how is this any different?

The query or module identifies the first non zero value, and assigns that to X. X is my first sum. Then I just need sum(x, x+1, x+2). And Finally sum (x, x+1, x+2, x+3, x+4, x+5). The two things I am trying to figure out is how to define X, and how to script the sums. I think using the numerical month field (1-12), if I found the first non zero value in the PROD column and then noted both that value and the month value (lets say it was march, so a value of three).... then all I have to do is use the increments on the month column to grab the right value I think.... I just have to figure out how to get it written out. Hopefully someone has some ideas there....

While the union query stuff was extremely helpful, telling me what I'm doing is hard isn't as much...lol.

[rpeare]

Did you look at my example? Mine sums the way you want it, at least in the testing I did, I may have missed something but it seemed to function the way you wanted in your OP

[exo]

Did you look at my example? Mine sums the way you want it, at least in the testing I did, I may have missed something but it seemed to function the way you wanted in your OP

I somehow missed your attachment. Thanks for pointing it out. Unfortunately there are a few problems.

The problem with this method is it does not take into account months AFTER the first month that have zero production.

If Sept was the first month with production, then it need to provide the following values:
Value of Sept
Value of Sept, Oct, Nov (whether they have a zero in them or not)
Value of Sept, Oct, Nov, Dec, Jan (followingyear), Feb (following year)

And that "following year" is the next problem. The data is not confined to a single year. So to do a min would require using date math instead. Something like taking the month and year into account and finding the min of the combination....

I have a sample database of my actual data and my current queries I have put together. I will upload it and hopefully seeing the actual data will make things easier to understand.

I have uploaded it to tiny upload:
http://s000.tinyupload.com/index.php...62414012761210

[June7]

Re the example of sales - conventional requirement would be to produce aggregate data by product for the same time period - the time period would be the same for all products, say quarterly - Jan-Mar, Apr-Jun, Jul-Sep, Oct-Dec. This is easy.

I know just telling you it is difficult was not terribly helpful, I was still mulling over the issue. This may be beyond my abilities and/or take too much time to figure out. Maybe rpeare guidance will get you on right track. Maybe queries alone won't accomplish and VBA will be needed.

[exo]

I know just telling you it is difficult was not terribly helpful, I was still mulling over the issue. This may be beyond my abilities and/or take too much time to figure out. Maybe rpeare guidance will get you on right track. Maybe queries alone won't accomplish and VBA will be needed.

I was thinking it would likely fall into VBA at this point. I have written VBA functions in the past, so I have a basic understanding of how to get them going.

The problem is, I haven't figured out how to identify the first non zero value yet. Until I can figure that out, I can't really begin planning the summing process or the error checks.

[June7]

That shouldn't be too out of the ordinary.

Maybe use TOP N qualifier to return earliest not-zero record for each well. Review http://allenbrowne.com/subquery-01.html#TopN

The issue is making this result available to the next step of your data analysis. This is where my eyes glaze over and I get lost.

[exo]

The TOP function seems to return the last 'n' number of values. That example for example returns the last 3 values. I suppose if the value was 1 and it could be reverse sorted to pull from the bottom, then that might grab the right value.... but only if it can be told to ignore zero values.

[June7]

Would have to use filter criteria to exclude the zero records.

Can use any non-zero positive integer as N.

And order can be ascending or descending. Allen's example use DESC to 'pull from the bottom'. But don't you want to sort ASCEN by date to pull the earliest?

[rpeare]

Attached is an updated example
I have data spanning 2013 through 2014 for well_number 1
I left you a couple of queries that I used in developing the final query (qry_PRODUCTION)

The query relies on user input to for a specific STARTING YEAR, in this case you'd put in 2013 since that's the year you're starting to look forward from.

Exo.zip

[exo]

I'm not sure I understand the part in the qry_FirstMonth that states [Enter the Production Year] in regards to the criteria for the firstprodyear field?

Is this the earliest possible year that any well might have data? or is it supposed to be the first prodyear for each well? Each well is different, so there is no constant firstprod year for the entire record set. A well drilled in 1950 and a well drilled in 2005 will have a large difference.

It also seems to require a production year for the qry_UnionBase...

I hope this isn't critical, as I have a feeling it undermines all the great work you have done.

The goal here would be to get the first month of production from a well no matter what year the well first kicked off.

Would these values you have coded into this thing work if I was to put a very high range, like 1900-2014? Or does it blow up if there isn't data for a record in a specified year?

So far, this seems to be the closest I have been able to get to this... i really hope there is a way to reocncile the year issue.