Function that loops through a set of fields seeking a non zero value

[exo]

I am not so great at writing functions from scratch, rather I generally have to find something that sort of accomplishes a similar task and then modify it to fit my needs.

Unfortunately I haven't been able to find anything this specific yet.

The idea is that I have a table with products. It is joined to another table that has each products ID and then a series of fields that correspond to each month of this year (so 12 fields). These fields contain how many of each product sold in that month. However some products did not appear in inventory until a few months into the year, so they have zero's for those months in which they didn't exist yet.

What I need to do is find the first month that each product went on sale, and pass that field back to my main table to do calculations with.

First I tried to do with with a query, but I ran into a road block and realized that maybe a query wasn't best as I likely needed a loop. So I started writing a function at that point... but it is obviously non functional. It is so broken, that it seems counter productive to even post it here as I don't want this to devolve into how screwed up my function is instead of a solution for what I am trying to accomplish.

As an example, if I had a product like this:
Product: X
SalesID: 1111
Price: 9.99
Month 1: 0
Month 2: 0
Month 3: 1582
Month 4: 2790
Month 5: 4501
Month 6: 4210

Then the idea of this function would be to look at Month 1, see if it contained a zero. if it did, move to month 2. If it doesn't, then send whatever that value is to a new field in the database.

So in the new table, I would have:
Product: X
SalesID: 1111
Price: 9.99
Month 1: 1582
Month 2: 2790
Month 3: 4501
Month 4: 4210

I hope this makes sense. Thank you for any assistance.

 

[The_Doc_Man]

You are starting with a HUGE disadvantage. You are thinking EXCEL/Flat-File when you should be thinking about something else. You should have a parent table with a product entry and a child table with sales records. Then finding the first month where something in particular was sold is merely a DMin aggregate function. To build reports showing sales for a given year broken out by month is just a cross-tab query for which there is a query wizard that can help you.

Look up the subject of normalization. The structure you defined is very much not normalized. You will get better results if you normalize your data. You will get very many headaches if you don't, given the type of things you are trying to do.

 

[gemma-the-husky]

as doc man says, what you need is two tables, that look like this

product table

Product: salesID price
X           1111     9.99

and a product sales summary table
product      date          sales
X          31/1/13    0 
X          28/2/13    0
X          31/3/13   1582

then your query becomes a simple select where sales=0, without you needing to write any special code.

note that you may not even need this table at all. No doubt behind this table you have a "sales" table that stores the details of sales transactions. You could just sum those transactions.

The monthly sales totals are calculated values, and it is not always recommended to store calculated totals - although it may be useful in this case. You can end up with additional tables and code to maintain, to keep the "compressed" sales in order.

 

[exo]

You are starting with a HUGE disadvantage. You are thinking EXCEL/Flat-File when you should be thinking about something else. You should have a parent table with a product entry and a child table with sales records. Then finding the first month where something in particular was sold is merely a DMin aggregate function. To build reports showing sales for a given year broken out by month is just a cross-tab query for which there is a query wizard that can help you.

Look up the subject of normalization. The structure you defined is very much not normalized. You will get better results if you normalize your data. You will get very many headaches if you don't, given the type of things you are trying to do.

First off - I must have not been very clear in my first post. Both answers are telling me how to find or select sales that are zero. That is the opposite of what I want. I want to to find the first month of sales that are *not* zero. So if sales$1, sales$2, and sales$3 have zero in them but sales$4 has 1,000 (each one is a month and there are twelve of these in the table), then I need to know that sales$4 is the first nonzero month. It's about finding the first field in a list of sequential fields that has a non zero value.

That said:

The database in question is purchased software. it is my job to make the data in it useful. I do not have control over it's format, so i can't simply just go in and modify this program to have 2 tables from here on out. I have to work within the limitations of the design. I can't change the format, so those suggestions don't help me much even if they make 100% sense.

That said, my current query *does* accesses two tables. HIST and MAIN. I used a retail example because it was easy to explain. I am tracking production history on wells. But the concept is identical. A well may not start producing resources for several months, or it may produce gas for the first three months before it produces any oil. The fact is, I have to identify the first month of production on a well. I then need to identify the sum of months 1, 2, and 3. I then need to sum the months of 1-6. However, if a well does not have production data for a complete 6 monht period yet - then I need it to return a N/A or some other type code to the field in order to avoid having a sum of only 4 or 5 months instead of the full 6. Same for the 3 months sum - it has to have a complete 3 month sum or it isn't useful.

SO here is the significance of each table:

MAIN: Well name, ID, location, etc (well details)
HIST: Each wells production history. There is a separate row for each year. My query simply filters by 2014 here. Each year has 12 fields of interest (beyond the id field). Each field is a month of the year. prod1$1, prod1$2, prod1$3...prod1$12.

then your query becomes a simple select where sales=0, without you needing to write any special code.

Simply filtering it by <> 0 does not tell me what month production started in. I need to know that for well A, that production was 0 in Jan and Feb, but started up in March (prod1$3). eg: a non zero number on the third oil field. Then, it needs to see if there are non zero values for the next two months (prod1$4 and prod1$5). If this is a yes, then it sums them and writes that sum to my 90 day total field. It then checks 6 months total (prod1$3-prod1$8), and if the final month has data, it sums all 6 of them.

So I hope you both see the important of my dilemma in identifying the first non zero field, and then how to go about creating the various required sums. What I am stuck on is how to present these 12 fields to access in a sequential manner. How to have it check them in a specific order and how to have it look for a non zero value and return it properly.

I hope I have explain my issue clearer. I appreciate the help so far, and I hope this clarification helps get me back on track.

edit:
It seems a function that uses Recordset.Seek Method (DAO) might work? ... but I'm not sure how to build it.