How do a copy a field value from a previous record based on criteria?

[SuzK]

In Access 2002, I have created a Make Table query to add a new field “Base$” through a custom function, moving average, that averages each 8 weeks of data. However, I also need to calculate a different result if there is an entry “X” in the Flag field--where there is a Flag "X", I want to duplicate the 8-week average number of the previous record. What is the VBA code to provide that alternate result? Here is a sample of the data, followed by the custom function.
ID WkEnd ItemNbr Sales$ Flag
464 9/3/05 Item1 22397
527 9/10/05 Item1 21742
591 10/22/05 Item1 22392
649 9/17/05 Item1 21581
715 9/24/05 Item1 21747
780 10/1/05 Item1 21424 X
845 10/8/05 Item1 21477
910 10/15/05 Item1 23806
984 10/29/05 Item1 22927
1049 11/5/05 Item1 33252 X
1114 11/12/05 Item1 20768
1179 11/19/05 Item1 20247
1447 12/17/05 Item1 26336
1513 12/24/05 Item1 32947
1583 12/31/05 Item1 20829
1651 9/3/05 Item2 23097
1719 9/10/05 Item2 44363 X
1835 10/22/05 Item2 38518
1902 9/17/05 Item2 32232
1970 9/24/05 Item2 32468
2036 10/1/05 Item2 41633 X
2105 10/8/05 Item2 27651
2174 10/15/05 Item2 26819

Function MovAvg(ItemNbr, startDate, period As Integer)
  
    Dim rst As DAO.Recordset
    Dim sql As String
    Dim ma As Currency
    Dim n As Integer
    
    sql = "Select * from tblSalesData "
    sql = sql & "where ItemNbr = '" & ItemNbr & "'"
    sql = sql & " and WkEnd <= #" & startDate & "#"
    sql = sql & " order by WkEnd"
    
    Set rst = CurrentDb.OpenRecordset(sql)
    rst.MoveLast
        For n = 0 To period - 1
        If rst.BOF Then
            MovAvg = 0
            Exit Function
        Else
            ma = ma + rst.Fields("Sales$")
        End If
        rst.MovePrevious
    Next n
    rst.Close
    MovAvg = ma / period
      
End Function

[VBWheaties]

Not 100% sure what you need but I think you should use a variable that gets updated with the value of the field you need to look at in hindsight.
Then, when you see your 'x' data, you'll have the variable from the previous record stored.

[SuzK]

Not 100% sure what you need but I think you should use a variable that gets updated with the value of the field you need to look at in hindsight.
Then, when you see your 'x' data, you'll have the variable from the previous record stored.

Thank you for your reply. Your assumption is correct. I'm not very adapt at VBA coding. Can you provide the code to accomplish your suggestion? I'm trying to learn VBA on my own, but I seem to have a mental block on the steps involved in setting variables and then manipulating them.

[VBWheaties]

Thank you for your reply. Your assumption is correct. I'm not very adapt at VBA coding. Can you provide the code to accomplish your suggestion? I'm trying to learn VBA on my own, but I seem to have a mental block on the steps involved in setting variables and then manipulating them.

Dimension a variable (done once at the beginning of your sub)

Dim myVar As String

Then, use it to store you value:

   myVar = rs("Field1").Value & ""

Then, you would check for the X value. If it exists, you'll have previous records value. So make sure you check for X before giving the variable the latest value.

[SuzK]

Dimension a variable (done once at the beginning of your sub)

Dim myVar As String

Then, use it to store you value:

   myVar = rs("Field1").Value & ""

Then, you would check for the X value. If it exists, you'll have previous records value. So make sure you check for X before giving the variable the latest value.

Thanks again for your help and patience.... but I'm still need help with coding an "IF" scenario. If you'll look at my desired results for the "Base$", you'll see that if the value of "promo" is null, I want to calculate Base$ as a moving 8-week average. However, if the value of "promo" is "X", I want to copy the previous record's value for Base$. See an example of my expected results for Base$, particularly for weeks ending 17 Sep through 01 Oct.

ItemNbr Week Ending Sales $ Base$ Promo
10025 16-Jul-05 11349 0
10025 23-Jul-05 13593 0
10025 30-Jul-05 12709 0
10025 06-Aug-05 10104 0
10025 13-Aug-05 8693 0
10025 20-Aug-05 8712 0
10025 27-Aug-05 8532 0
10025 03-Sep-05 9553 10406
10025 10-Sep-05 9713 10201
10025 17-Sep-05 14827 10355
10025 24-Sep-05 13938 10355 X
10025 01-Oct-05 14102 10355 X
10025 08-Oct-05 11052 11304

I can accomplish my goals by using an Iif statement in an Access Make Table query to get the Base$ for "non-promo" weeks and then running a Update query with Dlookup to retrieve the previous record Base$, but I was hoping to accomplish this in one VBA function -- moving average -- as indicated in my initial post.

[SuzK]

Dimension a variable (done once at the beginning of your sub)

Dim myVar As String

Then, use it to store you value:

   myVar = rs("Field1").Value & ""

Then, you would check for the X value. If it exists, you'll have previous records value. So make sure you check for X before giving the variable the latest value.

How do I "check for the X value"?

[VBWheaties]

How do I "check for the X value"?

What I meant by the X variable was to persist (save) the value you needed to perform the calculation when coming across X on the next iteration.
It seems that your logic is based on a backwards read of a subset of records and not on the entire table. Since there are a gazillion ways of doing things, your best bet is to focus on your own logic and take it from there.

[SuzK]

What I meant by the X variable was to persist (save) the value you needed to perform the calculation when coming across X on the next iteration.
It seems that your logic is based on a backwards read of a subset of records and not on the entire table. Since there are a gazillion ways of doing things, your best bet is to focus on your own logic and take it from there.

Thanks, VBWheaties for your patience. My logic is "based on a backwards read of a subset of records". My VBA coding skills are extremely limited. I have the calculations working in Excel where each subgroup is on a separate worksheet, but I need to move to Access because of the increasing number of worksheets.

I have created a working moving average custom function in Access (referenced earlier) which I use in a Make Table query, which works fine. However, I'm having problem updating the values, when the function returns a zero value. These are my needs --

1 - Moving Average function -- Calculates Weekly 8-week moving average for each of 80+ items in my database.
2 - When an item is "on promotion", indicated by an "X" value in the promotion field, I want to repeat the Moving Average value for (1) that item and (2) in the previous week.

I have the above working in an Excel formula, but because of the size of the database, I need to a way to do the same in Access.

Thanks, again. If I don't hear back, I'll assume you've given up on me, and I'll start over elsewhere.

[VBWheaties]

Thanks, VBWheaties for your patience. My logic is "based on a backwards read of a subset of records". My VBA coding skills are extremely limited. I have the calculations working in Excel where each subgroup is on a separate worksheet, but I need to move to Access because of the increasing number of worksheets.

I have created a working moving average custom function in Access (referenced earlier) which I use in a Make Table query, which works fine. However, I'm having problem updating the values, when the function returns a zero value. These are my needs --

1 - Moving Average function -- Calculates Weekly 8-week moving average for each of 80+ items in my database.
2 - When an item is "on promotion", indicated by an "X" value in the promotion field, I want to repeat the Moving Average value for (1) that item and (2) in the previous week.

I have the above working in an Excel formula, but because of the size of the database, I need to a way to do the same in Access.

Thanks, again. If I don't hear back, I'll assume you've given up on me, and I'll start over elsewhere.

I haven't given up, I just don't want to dilute the logic with mine that may cause more confusion.

For the average, when you have your values in Access, you can get the average like so:

   SELECT AVG(NumberField) AS 8WeekAverage 
   FROM MyTable
   WHERE MyItem = 'ThisItem'

If you wanted to show averages for all items:

   SELECT AVG(NumberField) AS 8WeekAverage, ItemNumber 
   FROM MyTable
   GROUP BY ItemNumber

If that doesn't do it, post again so others can have a go at it.

[SuzK]

I haven't given up, I just don't want to dilute the logic with mine that may cause more confusion.

For the average, when you have your values in Access, you can get the average like so:

   SELECT AVG(NumberField) AS 8WeekAverage 
   FROM MyTable
   WHERE MyItem = 'ThisItem'

If you wanted to show averages for all items:

   SELECT AVG(NumberField) AS 8WeekAverage, ItemNumber 
   FROM MyTable
   GROUP BY ItemNumber

If that doesn't do it, post again so others can have a go at it.

Thanks... I already have the averages for all items. My problem is not in calculating the averages -- it is updating the table to copy the previous item weekly average when the week is a promo week. A promo week is identified in my table's field "Promo", where "Promo" = "X". In my Access query, I calculated the weekly averages where Promo <> "X".

Again, thanks for your help. I'll work tonight on how to clarify my question and repost.