Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2014
    Posts
    4

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

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First, write a query to correct the problems with the history table. Create a union to select the columns in a normalized form (well identifier, production type (oil, gas, water, etc.), date (use the first day of the month), and quantity. This isn't a new table, just a query to fix the problem with how the data is stored.

    That ought to fix most if not all of your problems.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2014
    Posts
    4
    Ok, I figured out the union query.

    I now have a table with:
    WELL, YEAR, MONTH, PROD in it.

    I then filter the year down to get just the year I want.

    However if I filter the PROD to get rid of non zero values, this breaks my sums.

    It is possible that a month after the initial month could have a zero in it. So when I go and sum three months of prod, it needs to take zero's into account.

    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.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    For this purpose you need to filter the zero values, they will cause you nothing but grief.

    To figure out which rows are of interest, use date arithmetic. To determine how many rows you have (to see if the sums are valid for this query), use a Count().

    You are making good progress. This is frustrating, but it is part of learning how to cope with poor schema choices (which you need to do). If I thought that this was a "one time shot", I could have just given you the answer but this is something that you'll almost certainly face over and over again when dealing with a schema like this so it is better to learn to handle it up front than to constantly have to come back for more help.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Apr 2014
    Posts
    4
    Quote Originally Posted by Pat Phelan View Post
    For this purpose you need to filter the zero values.

    To figure out which rows are of interest, use date arithmetic. To determine how many rows you have (to see if the sums are valid for this query), use a Count().
    But aren't the zero values that come after the initial production month important to me?

    I'll need a little more to go on at this point. Let me see if I can figure out where you ate going with this.

    For the 6 month sum, you are suggesting an iif statement where it runs count and if the sum is equal to or greater than 6, then the true equation is to sum the first values and the false is to print a code that helps the user this could not be calculated, right? For the 3 month sum this changes to equal to or greater than three...

    However I still have to figure out how to:
    Identify the first non zero value without dumping all zero values or u figuring out how to reintroduce zero values during summing.

    And

    How to only sum a certain number of values out of the set. I have no idea how to attack this.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are correct in that I envisioned removing all of the zero values. Count the remaining values in an N month period... If you have N values, then show the sum. If you don't have N values, show that there isn't a useful answer.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Apr 2014
    Posts
    4
    but that can be misleading. As I have stated - a zero value is important IF it proceeds a non zero value.

    Even if my numbers are:
    0
    0
    0
    100
    0
    0
    0
    0
    0
    0
    0
    0

    Then my sums are:
    30 day: 100
    60 day 100
    90 day 100

    The only time the 60 or 90 would not be 100 is if there were not 3 or 6 months of data to check in the first place.

    So something like
    iif(current.month.year-start.month.year>=6,[90Day]=sum(x,x+1,x+2,x+3,x+4,x+5),[90day]="N/A")

    and if my if statement is nonsensical, then basically: if 6 months or more have passed since the first non zero month [x], then sum up x and the next 5 months (x+1, etc...) but if not, print N/A.

    Same thing for the 30 day, just a smaller time check.

    Getting rid of zero values prevents this from working.

    However this exercise with you did make me realize that I can't just filter by year and be done with it.

    If I were to filter only to 2013, then no well with initial production in August or beyond would ever be able to calculate a 6 month sum. it would have to be able to consider the 2014 data to check this.

    So I have to not only find a way to make it view the months sequentially (which I thought I could easily achieve by using the month numeric field), but I have to make it look at month and year sequentially. I believe this can be done with date functions though. I should be able to do something like:
    Iif((DateDiff ("m", #08/01/2013#, #01/01/2014#))=>6,....,....)

    The key is filling in those dates. Which, as long as I knew what the first non zero value was, I could derive the month and year from the same record and build that date.... and then maybe from there I use DateAdd to calculate what the month 6 months from now is and check that value for a non null value. If it's not null (even a zero is not null), that tells me that *something* has been entered, which means I have enough info to calculate the sum.

    So really, it all boils down to me finding a method to identify the first non zero value in the PROD field. Once I have that, I can start doing a lot of other stuff with it.

    Is this something you think you can assist me with, or weer you counting on me eliminating all the zero's in order to easily just use a min function to grab the first month that was left?

    if so.. maybe I could create a secondary query that eliminates zeros from the view... and somehow reference that query to determine the min value of the month?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by exo View Post
    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.
    Please elaborate on this a bit... It appears to contradict your last post.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •