Well, I finally got the Running Sum script to work! That was a 2-day investment. However, it is only ½ of the battle. I need to calculate off of the result.
Data:
Table: RA Contract Running Sums (I turned the query into a maketable so that I can freeze the running sum value for further calculations). This table is sorted DESC by RunSum.
Fields: (there are more, but these are the only pertinent ones)
Product Code -- unique value
Updated Buying Trend – this is the field I have been appending with each step of my analysis
12 Month Rolling Sales QTY -- number value indicating yards sold
RunSum -- calculated running sum value on field: 12 Month Rolling Sales QTY
Scenario:
We assign a Buying Trend based on 12 Month Rolling Sales QTY. Basically it is a ranking system which indicates the performance of a particular Product Code. The codes are A (best), B, C (worst), X, and NEW. Twice a year we review all the data and re-align the Product Codes giving them an updated Buying Trend based on their recent performance (sales).
I have started by creating a make table query which separates only the RA Contract population and creates a blank field called “Updated Buying Trend” to be appended to in future queries. Then, I have created various update queries to fill in the “Updated Buying Trend” based on certain criteria.
After the first update query has run, the main criteria for remaining update queries is “Updated Buying Trend” must be null, thus discounting any record which has been previously reassigned an updated Buying Trend. The point is to dwindle down the population as we go because the rules for each Buying Trend sometimes overlap, therefore forcing us to run them in a specific order (some rules over-ride others). Here is the order:
X (deleted)
NEW (created in last 12 months)
A (top 1/3)
B (middle 1/3)
C (remaining records)
This is what I need:
The next step is to identify the top-performing Product Codes based on the sum of all sales. Here is the rule for determining our A Population:
**Please bear in mind that I don’t make up these rules, therefore I cannot change them.
Buying Trend A = TOP 1/3 of best performing Product Codes
This is where the need for a running sum came in. Basically, we need to sum the entire population (records not already given an Updated Buying Trend) by 12 Month Rolling Sales QTY and divide by 3. The result of that formula (we’ll call it “A Split”) should then be compared to the running sum value calculated earlier. If the running sum value is >= A Split, then mark “Updated Buying Trend” to “A”.
Here is the rule for determining our B Population:
Buying Trend B = MIDDLE 1/3 of best performing Product Codes
So, the way they determine this is by recalculating the running sum on the remaining “unmarked” population and repeat the above process over again. The exception is this resulting set becomes our “B” population.
The remaining records are marked “C”.
--------------------
This is what I am working with now (where I am in the process):
500 - RA Contract Data: MAKE TABLE QUERY - Identifies records for this Division only; creates “Updated Buying Trend” field for me to update.
501 - RA Contract X: UPDATE QUERY – Identifies Product Codes which qualify for Buying Trend X and marks them accordingly.
502 - RA Contract NEW: UPDATE QUERY – Excludes records marked from query 501; identifies Product Codes which then qualify for Buying Trend NEW and marks them accordingly.
503a - RA Contract Running Sum Set-Up: SELECT QUERY – Initial query to create Running Sum function
503b - RA Contract Running Sums: MAKE TABLE – Applies the running sum function to records not already previously marked and creates a table with resulting records. Resulting table: RA Contract Beginning Running Sum Values
503c - RA Contract Third Split Value for A's: SELECT QUERY – Sums “12 Month Rolling Sales Quantity” from the “RA Contract Beginning Running Sum Values” table and divides by 3 to give us the value we must compare against for our A Population.
--------------------
Now, I am sure I am not doing this in the most efficient way. I am not a programmer, I am a data analyst with strong Excel and Access skills. We did all this in Excel before, but it took days to complete. I just want to put it in a database and have Access calculate everything for me. If I can just figure out how to split my population into thirds, it will be done.
Any help?
CorrieAnn
Data:
Table: RA Contract Running Sums (I turned the query into a maketable so that I can freeze the running sum value for further calculations). This table is sorted DESC by RunSum.
Fields: (there are more, but these are the only pertinent ones)
Product Code -- unique value
Updated Buying Trend – this is the field I have been appending with each step of my analysis
12 Month Rolling Sales QTY -- number value indicating yards sold
RunSum -- calculated running sum value on field: 12 Month Rolling Sales QTY
Scenario:
We assign a Buying Trend based on 12 Month Rolling Sales QTY. Basically it is a ranking system which indicates the performance of a particular Product Code. The codes are A (best), B, C (worst), X, and NEW. Twice a year we review all the data and re-align the Product Codes giving them an updated Buying Trend based on their recent performance (sales).
I have started by creating a make table query which separates only the RA Contract population and creates a blank field called “Updated Buying Trend” to be appended to in future queries. Then, I have created various update queries to fill in the “Updated Buying Trend” based on certain criteria.
After the first update query has run, the main criteria for remaining update queries is “Updated Buying Trend” must be null, thus discounting any record which has been previously reassigned an updated Buying Trend. The point is to dwindle down the population as we go because the rules for each Buying Trend sometimes overlap, therefore forcing us to run them in a specific order (some rules over-ride others). Here is the order:
X (deleted)
NEW (created in last 12 months)
A (top 1/3)
B (middle 1/3)
C (remaining records)
This is what I need:
The next step is to identify the top-performing Product Codes based on the sum of all sales. Here is the rule for determining our A Population:
**Please bear in mind that I don’t make up these rules, therefore I cannot change them.
Buying Trend A = TOP 1/3 of best performing Product Codes
This is where the need for a running sum came in. Basically, we need to sum the entire population (records not already given an Updated Buying Trend) by 12 Month Rolling Sales QTY and divide by 3. The result of that formula (we’ll call it “A Split”) should then be compared to the running sum value calculated earlier. If the running sum value is >= A Split, then mark “Updated Buying Trend” to “A”.
Here is the rule for determining our B Population:
Buying Trend B = MIDDLE 1/3 of best performing Product Codes
So, the way they determine this is by recalculating the running sum on the remaining “unmarked” population and repeat the above process over again. The exception is this resulting set becomes our “B” population.
The remaining records are marked “C”.
--------------------
This is what I am working with now (where I am in the process):
500 - RA Contract Data: MAKE TABLE QUERY - Identifies records for this Division only; creates “Updated Buying Trend” field for me to update.
501 - RA Contract X: UPDATE QUERY – Identifies Product Codes which qualify for Buying Trend X and marks them accordingly.
502 - RA Contract NEW: UPDATE QUERY – Excludes records marked from query 501; identifies Product Codes which then qualify for Buying Trend NEW and marks them accordingly.
503a - RA Contract Running Sum Set-Up: SELECT QUERY – Initial query to create Running Sum function
503b - RA Contract Running Sums: MAKE TABLE – Applies the running sum function to records not already previously marked and creates a table with resulting records. Resulting table: RA Contract Beginning Running Sum Values
503c - RA Contract Third Split Value for A's: SELECT QUERY – Sums “12 Month Rolling Sales Quantity” from the “RA Contract Beginning Running Sum Values” table and divides by 3 to give us the value we must compare against for our A Population.
--------------------
Now, I am sure I am not doing this in the most efficient way. I am not a programmer, I am a data analyst with strong Excel and Access skills. We did all this in Excel before, but it took days to complete. I just want to put it in a database and have Access calculate everything for me. If I can just figure out how to split my population into thirds, it will be done.
Any help?
CorrieAnn
