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

[rpeare]

You didn't state that this was for the history of your data, I just assumed it was for a particular time span. The query concept doesn't change you still have to find a first production year and month within that year. Then get your data to conform to the way I have it and your final query won't really change at all.



Example Enclosed.
Exo.zip

[exo]

Wow, this is great stuff. It is a very clever way to do this via query.

In using it with my master data, I have only come across 1 issue so far, but I don't think it will be hard to solve.

One is that it seems that rarely there are wells that enter the system, but have no production the first year they show up. Since the first year query looks for the min on each well_id, it decides that that first year is the one to go with. Then when it goes to find the first month it gets confused and leaves a blank value as it never finds a non zero field. Do you think it is easier to add criteria to the "find first year query", or to create a delete query that kills any records that have zero in every prod field?

I completed taking your code and converting it to my database and it runs 95% perfectly! You've done an amazing thing by doing it through query. I've posted this question on 5 or 6 access boards, and no one has even come close to being able to help me get to this point.

Presumably, fixing the one issue above will solve the problems for the wells that have zero sums across the board.

I owe you a 6 pack of your favorite drink at this point.

[rpeare]

The query that finds the first year would work fine but you'd have to have a formula to add all the months together and put a criteria of <>0 in there

[exo]

I adopted what you had written to find the first month, and that seems to have worked. (the nested if statements). I didn't know how to sum other fields in the criteria field, so I didn't purse that approach. But the nested iff statement seem to have worked fine.

All the examples I had written down that where picking up zero value years are now picking up the first year with production.

At this point, I think the last thing I have to figure out is the process of making sure I'm not providing values in the month3 and month 6 fields that are not representative of a full data set (eg: 4 months being summed together and then getting pasted in the 6 month sum field)

This is what I have tried:
I added a query after qry_FirstMonth_History that adds two new fields. The first takes the calculated first month and first year fields and combines them into a date field with the format mm/yyyy. The second is todays date converted to mm/yyyy. That second one is unnecessary right here, as it could be calculated at the time it is needed - but to keep things simple I added it as a field for now and if I get it working I'll pull this field and calculate this on the fly.

In the next query, qry_Production_History, the goal would be to add an iif statement before the sum of the three month and six month sums to qualify them.

What I wrote was:
IIf((DateDiff("m",[combine],[date]))>2,{current working sum calculation},"N/A")

My thought process was that if the difference between this month and the first month of production was greater than 2, then it must have at least 3, therefor it has enough to calculate the three month sum. And if it was 0, 1, or 2, the statement would fail and it would print N/A instead.

However my thoughts and reality don't line up.

It tells me that I am trying to execute a query that does not include the specified expression {expression printed here} as part of an aggregate function.

Am I remotely on the right track here?

[rpeare]

I don't have the foggiest what you're asking. If you're trying to get the 3 month or 6 month amounts to be zero if the well has not had enough time to get that far all you have to do is compare the current date to the first month/year the well was active. Those two fields are already part of the setup queries so it shouldn't be difficult to check. What I'd do is just some basic check with whatever logic you want to include.

For instance if you're running this report in June you won't have June data yet, you'll only have up to May (at best). So that means you'd have to see, for your three month calculation, if your first month is the current year/(month -4) or lower (may, april, march going backward). You'd have to build a similar expression for your six month calculation, then in your 'final' query if the indicator is present, put in a zero, otherwise perform the existing calculation.

[exo]

that's exactly what I was doing... I guess I was just doing it in a different way?

the datediff function above would take the first month of production and compare it to the current one (minus 1 or 2 obviously, to make up for lag in reporting) and only print a sum if the difference between the two dates was high enough.

What you are saying is what I want, except for one thing. I don't want them to be zero, I want them to be "N/A". Zero might suggest there has been no production on the well. It doesn't necessarily tell a person that the well isn't old enough to calculate production yet.

So yea... I am trying to put an iff statement before the 3month and 6 month sum that checks how many months exist between my last report and the first production. That is what I wrote above.

I guess my process above was confusing because I added fields instead of just calculating them? I suppose, technically, i could have dynamically created a date field from the month and year when it was needed. But that is alot a lot harder to bug check if it doesn't work than passing a value to the next table.

So if you would not do it the way I was looking at, which method would you use? Criteria field? That seems problematic, as the criteria field can prevent the sum from being calculated, but it can't make sure a code gets written into that spot.

[rpeare]

You can't have a text value and a number share the same field, you can format text to look like a number but then you lose the ability to do calculations. If this query is going to drive a report you can do that kind of formatting on the reports side.

How you approach it depends on your rules, if you are doing it the way I mentioned you have to have rules. If you don't have rules you can't provide a reliable response.
The biggest one I can think of is how quickly is the data updated at the end of a month (for instance, is january data completed by the first week of february, or is it completed by the beginning of march) The timeline/lead time/lag time of your data will drive your equation to figure out whether it needs to be reported or not.

[exo]

You can't have a text value and a number share the same field, you can format text to look like a number but then you lose the ability to do calculations. If this query is going to drive a report you can do that kind of formatting on the reports side.

How you approach it depends on your rules, if you are doing it the way I mentioned you have to have rules. If you don't have rules you can't provide a reliable response.
The biggest one I can think of is how quickly is the data updated at the end of a month (for instance, is january data completed by the first week of february, or is it completed by the beginning of march) The timeline/lead time/lag time of your data will drive your equation to figure out whether it needs to be reported or not.

Where do I have a text value and a number in the same field?

The data comparison I was talking about is date comparing to date. If you look at what I had above, it takes your text value month and text value year and converts it to date format. Then it compares that date of first production (in date format) to the current date (which can easily be modified to the current date -2 months if I want to adjust for the last time the data was updated).

So, if it is comparing date to date, I don't see why it doesn't work. Is there somewhere else I have text and numeric in the same field?

I don't mind doing it your way at all - I just don't know how to do it that way. And by "rules", what do you mean? Criteria?

[rpeare]

You are talking about having N/A appear in a column if there haven't been enough months of operation on a well for it to have a valid calculation (for instance if the well has only been active 4 months you, from my understanding, wanted N/A to appear in the 6 month production column, which you can't do unless you cstr() the value.

Rules are not criteria:

The biggest one I can think of is how quickly is the data updated at the end of a month (for instance, is january data completed by the first week of february, or is it completed by the beginning of march) The timeline/lead time/lag time of your data will drive your equation to figure out whether it needs to be reported or not.

[exo]

I don't have a hard and fast rule at this point. It all depends on when I am asked to run the process. Lets say 2 months prior to this month is the rule.

So, as I typed above, I was trying to do this:
DateofFirstProd: CDate((IIf((Len([FirstMonth])=1),"0" & [FirstMonth],[FirstMonth])) & "/" & [FirstProdYear])

CurrentDate: Format(Date(),"mm/yyyy")

Date of first prod is MM/YYYY in date format
Current date is also MM/YYYY in date format

DateDiff then should be able to compare those two dates and come up with a number, right? If the number is greater than 5, then obviously I have at least 6 months of data, so I can calculate the sum. If not, then I can print N/A... right?

As far as the "rules" above go, lets say the numbers I am working with are always 2 months behind. Can't I subtract 2 months from the Current Date and the run the datediff on that?

I looked up the CVStr function and saw that it converts an expression into a string. I now realize you are saying that N/A can not be printed to that field since it is a number. That makes sense now. But I am not at that point yet.

For example, I tried to simply get it to return the datediff in a field:
ThreeMonthProdDiff: DateDiff("m",[combine],[date])

As I understand it, this should create a field called ThreeMonthProdDiff with a value that is the absolute value difference between the current date and the date of first production.

But it tells me that I tried to execute a query that does not include the specified expression 'DateDiff("m",[combine],[date])' as part of an aggregate function.

edit: I have found it is due to the "totals" criteria. It makes it a data type mismatch.

I have tried putting both Str and Val in front of the expression to convert it to a string and a value repsectively, but it comes back as a data type mismatch both ways.

edit2:
I have found that I can't even display the values for [combine] or [date] on the query without it saying it is a type mismatch.

I guess the "totals" part is throwing me off. I haven't worked with this before, and I don't understand how to work within the confines of a total's query.

[exo]

wanted to make a new post for this rather than continually editing my old one.

I sat down and worked through the sum expressions you came up with step by step to understand them. I don't naturally think this way, but once I got it all parsed out I understood how it worked and was able to create a TwelveMonth Sum column as well.

edit:
Now that I understand the changes you made to the union query and how you are arriving at the sums, I'm hoping I can understand the method to put criteria on the calculation of how many months there are to work on.

Going back to your first suggestion, you stated

If you're trying to get the 3 month or 6 month amounts to be zero if the well has not had enough time to get that far all you have to do is compare the current date to the first month/year the well was active. Those two fields are already part of the setup queries so it shouldn't be difficult to check. What I'd do is just some basic check with whatever logic you want to include.

For instance if you're running this report in June you won't have June data yet, you'll only have up to May (at best). So that means you'd have to see, for your three month calculation, if your first month is the current year/(month -4) or lower (may, april, march going backward). You'd have to build a similar expression for your six month calculation, then in your 'final' query if the indicator is present, put in a zero, otherwise perform the existing calculation.

This suggestion is what I am trying to do. I am failing at it though, and it seems that when I tried to explain how I was approaching it, it made no sense to you. Where do I compare the current year/month to the first year/month? In the expression? Itself? My attempts to use any sort of date in the expressions is returning the aggregate function error I listed above.

Some sort of guidance on this would be appreciated. I am going to refrain from trying to explain my processes from here on out, as it just seems to confuse you and derail progress.


I was serious about owing you for helping me. Hopefully I can find a way to repay you for your assistance.

[exo]

I was finally able to figure out what you were suggesting. I kept screwing up by trying to do the check on the totaled table. Rather than letting the calculation take place and then choose whether or not to use it on the final table. After reading over your responses a few times it finally clicked. You obviously had told me to do it on the final table, but in my head I was considering the totaled table to be the final one.

Thank you for all of your help. At this point it appears to work perfectly, and of all the places I sought help on this issue, you were the only one to make any progress what-so-ever on it, let alone solve it.

[rpeare]

Well glad you resolved it. Sometimes the difference in terminology can be hard to communicate. I'm used to trying to find workarounds for bad data, I get a lot of it.