How do I update a Microsoft Access 2002 query given certain criteria?

What the relationship between Sales $, Sales Units and AvgWklyDollars?

02.27.2008 at 04:31PM PST, ID: 21000198

Rank: Genius

This is a little cloudy.

I'll assume you want to store the date in [Week Ending] for the latest available previous week in a new field called [NewField]. If you want the update to occur in an existing field, you'll modify the query accordingly.

Note that the combination ItmNbr & [Week Ending] is currently a candidate key for this table (there are no duplicate combinations). If you are updating the field [Week Ending], this will no longer be the case, naturally.

UPDATE YourTable
SET NewField = DMax(
"[Week Ending]",
"YourTable",
"ItmNbr = " & "ItmNbr" & " And [Week Ending] < " & CLng([Week Ending]))
WHERE AvgWklyDollars=0 AND Promo='X';

As you see, you need to use DMax() instead of the more logical (Select Top 1) syntax. This is because the (Select Top 1) syntax turns the entire query into a non-updatable query...

Good luck!
(°v°)

02.27.2008 at 07:52PM PST, ID: 21001099

The suggested code did not provide the desired results. I'll try to explain better here.

I do not want to create a new field. I want to update the existing field "AvgWklyDollars". This field was created by a custom function which averages the last 8 weeks of Sales$ by ItemNbr. It does not include the weeks where Promo = "X".

I am trying to create data for a baseline, which simply put is the average Sales$ for the last 8 weeks were there was no promotion (Promo = "X").

I had really hoped to include the calculation for the Promo weeks in the custom Moving Average function, but I did not know how to do that. So I calculated just for the weeks were there was no promo with the intent of updating the AvgWeeklyDollars field with the value of the previous week's AvgWeeklyDollars. I used the custom Moving Average function in a Make Table query in Access. The formula is

AvgWeeklyDollars: IIf([promo]="x",0,movavg([dpci],[week ending],8))

If it helps, the code used to calculate AvgWeeklyDollars.

As far as the AvgWeeklyUnits, it is calculated using a similar custom function named MovAvgUnits which is a calculation using Sales Units in the same way Sales $ was used.

                       
         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:

           
           
             '===============================================================
' The following function MovAvg computes moving averages based on
' a table with a multiple-field primary key.
'===============================================================
 
 
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 [Weekly Sales by type All Items] "
    sql = sql & "where ItemNbr = " & ItemNbr
    sql = sql & " and [Week Ending] <= #" & startDate & "#"
    sql = sql & " order by [Week Ending]"
 
    
    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
           
         

Open in New Window

02.28.2008 at 07:52AM PST, ID: 21004907

I used the following in the update query, but the 0s were updated with blanks. If you will follow the sample results below, you'll see blanks, where the value should be the same as the previous non-zero value (ie for week ending 9/24/2005 the result for Base$ should be 10355.375 and for week ending 10/1/2005 should be 10355.375 as well.

UPDATE tblTest
SET tblTest.[Base$] = DMax("[Week Ending]","tblTest","ItemNbr = " & "ItemNbr" & " And [Week Ending] < " & ([Week Ending])), tblTest.BaseUnits = DMax("[Week Ending]","tblTest","DPCI = " & "DPCI" & " And [Week Ending] < " & ([Week Ending]))
WHERE (((tblTest.[Base$])=0) AND ((tblTest.BaseUnits)=0) AND ((tblTest.Promo)='X'));

Results from update query (sample):
ItmNbr      Week Ending      Sales $      Sales Units      Base$      BaseUnits      Promo
10025      9/17/2005      14827      2918      10355.375      1951.25
10025      9/24/2005      13938      2802                  X
10025      10/1/2005      14102      2836                  X
10025      10/8/2005      11052      2090      11303.625      2160.625
10025      10/15/2005      12308      2274      11753.125      2246.25
10025      10/22/2005      11405      2112      12112.25      2316.25
10025      10/29/2005      12624      2489      12496.125      2410.75
10025      11/5/2005      11978      2412                  X
10025      11/12/2005      11550      2325                  X
10025      11/19/2005      9938      1875      11869.625      2301.625

Can you tell me what I am doing wrong?

02.28.2008 at 08:23AM PST, ID: 21005346

Rank: Genius

Are you saying the X's all belong under Promo, and the Base$ and BaseUnits had zero (0) values and were overwritten to null? Are there any zero values where Promo is not 'X'? Assuming the value you want to update are null and not zero:

UPDATE tblTest a SET
[a.[Base$]=IIf(IsNull([a.Base$]),(Select Max(b.[Base$]) FROM tblTest b WHERE b.[Week Ending] < a.[Week Ending AND b.ItmNbr=a.ItmNbr AND b.Promo = "X"),a.[Base$],
[a.[BaseUnits]=IIf(IsNull([a.BaseUnits]),(Select Max(b.[BaseUnits]) FROM tblTest b WHERE b.[Week Ending] < a.[Week Ending AND b.ItmNbr=a.ItmNbr AND b.Promo = "X"),a.[BaseUnits];

02.28.2008 at 10:32AM PST, ID: 21006655

X's all belong to Promo.
Base$ and BaseUnits had zero (0) values and were overwritten to null.
There are zero values where Promo is not "X" -- and that's a whole other problem to deal with. Those should ultimately be updated to equal Sales$

Using GRayL's code --
I tried to copy and paste your code into the SQL window of an Access query design and got syntax errors even after I created the alias tables tblTest a and tblTestb, and repaired syntax errors as best I could. Perhaps if you explain what you are trying to do, I can recreate in the design view of an Access Query or provide proper syntax that I can copy into the SQL view of an Access Query.

I am not good at syntax, but I think I understand the logic.
As I understand your process, you are (1) creating an alias table of the original table and (2) replacing the zero(0) values of the original table Base$ and BaseUnits with the maximum value Base$ and BaseUnits from the alias table where (1) ItemNbr = "x" and (2) Base$ and Base Units = Is Null and (3) where the week ending in the alias table is less than the week ending date in the original table.

02.28.2008 at 12:35PM PST, ID: 21007760

Rank: Genius

Given we are working with zeros and not nulls, and having missed two closing parentheses, here's my rework:

UPDATE tblTest a SET
[a.[Base$]=IIf([a.Base$]=0, (Select Max(b.[Base$]) FROM tblTest b WHERE b.[Week Ending] < a.[Week Ending AND b.ItmNbr=a.ItmNbr AND b.Promo = "X"),a.[Base$]),
[a.[BaseUnits]=IIf([a.BaseUnits]=0, (Select Max(b.[BaseUnits]) FROM tblTest b WHERE b.[Week Ending] < a.[Week Ending AND b.ItmNbr=a.ItmNbr AND b.Promo = "X"),a.[BaseUnits]);

You should not be creating any new tables. You have one table tblTest. In the query two instances of the table are opened, a and b. In the event a value is zero and the Promo is X and it matches the ItmNbr, the Subquery kicks in and finds the max value of the field with a date previous to the date in the zero record, and uses that, else it just replace the value with itself. If that still doesn't work, let me know and I'll create a table and work out the bug. The logic is correct, I've done this lots before - I just mess up the syntax at times.

02.28.2008 at 01:07PM PST, ID: 21008072

I copied your code into an Access 2002 query - SQL view. When I try to switch to Design view, I get the error message:
Invalid bracketing of name '[a.[base$]'

I revised the code to:

UPDATE tblTest AS a SET a.[Base$] = IIf(a.[Base$]=0,(Select Max(b.[Base$]) FROM tblTest b WHERE b.[Week Ending] < a.[Week Ending] AND b.itmNbr=a.ItmNbr AND b.Promo = "X"),a.[Base$]), a.BaseUnits = IIf(a.[BaseUnits]=0,(Select Max(b.[BaseUnits]) FROM tblTest b WHERE b.[Week Ending] < a.[Week Ending] AND b.ItmNbr=a.ItmNbr AND b.Promo = "X"),a.[BaseUnits]);

This time I got the error message:
Operation must use an updateable query.

Any suggestions?
(thanks!)

02.28.2008 at 03:00PM PST, ID: 21009142

Rank: Genius

Well, it's always the same problem. You cannot use subqueries in an update query, because a subquery anywhere but in the WHERE clause tends to make the query non-updatable.

The reason little effort is put into making these complex update queries work is that it falls into the category of "derived data". Generally speaking, one does not store derived data in a database, so the subqueries work well on select and make table queries, but not on update queries. Anyway.

For example, this works:

UPDATE YourTable
SET AvgWklyDollars = DLookUp(
'AvgWklyDollars',
'YourTable',
'ItmNbr=' & [ItmNbr] & ' And [Week Ending]=' & CLng([Week Ending]-7))
WHERE Promo='X';

You will have to run it several times, as you can have several "promo" weeks in a row.

A bit more complex: find the last previous non-promo week for the item:

UPDATE YourTable
SET AvgWklyDollars = DLookUp(
'AvgWklyDollars',
'YourTable',
'ItmNbr=' & [ItmNbr]
& ' And [Week Ending]=' & CLng(DMax(
'[Week Ending]',
'YourTable',
'ItmNbr=' & [ItmNbr]
& ' And [Week Ending]<' & CLng([Week Ending])
& ' And Promo=''''')))
WHERE Promo='X';

This needs to be run only once. You might get an error if there is no non-promo week before a promo-week, due to the conversion functions used.

This being said, your code sample above shows that you are proficient in VB and can easily write the VB loop to perform the same thing. Might be easier to read and maintain anyway, not to mention more efficient (DLookup and DMax are not the most optimized functions, far from it).

You can even rewrite your moving average function to look not at the last eight weeks, but at the eight non-promo weeks. This would solve the problem a bit higher upstream.

Cheers!
(°v°)

02.28.2008 at 03:04PM PST, ID: 21009169

Rank: Genius

Note: in my test table, the field Promo is either 'X' or '' (zero-length string). You might need to change the query to use "Promo Is Null" instead (normally, a blank field is Null).

(°v°)

02.29.2008 at 07:16AM PST, ID: 21013918

Rank: Genius

Markus: As usual, thanks for the lesson. Good to see you back!

Ray

02.29.2008 at 01:34PM PST, ID: 21017827

The code you suggested work, but I did get the type conversion error for 4 of 781 records.

I think I'll go with the VBA route, even though I'm not as good at writing VBA as you seem to think. (But thanks for the compliment!). You put me on to that fact I "can even rewrite your moving average function to look not at the last eight weeks, but at the eight non-promo weeks. This would solve the problem a bit higher upstream".

I have to admit that putting the function in VBA together was painful and took a while since I created it from looking at a lot of samples. I understand what I did well enough, but adding another condition to loop through is a bit scary. (I'm currently taking an online VBA for Access tutorial.) Would you mind helping me revamp my VBA so that it calculates the moving average value of the last 8 non-promo weeks, as well as calculates the promo weeks as a duplicate of the previous week's moving average non-promoted week's value? :)

I've been working on this off and on for a couple of weeks, and I've got other projects waiting in the queue.

02.29.2008 at 07:18PM PST, ID: 21019706

Rank: Genius

OK, but let's do it a bit differently then.

Observe first that we can ask Jet to do all calculations, for any ItmNbr and any Week, by embedding a TOP 8 query into a TOTAL query.

SELECT
Count(*) AS Nb,
Sum([Sales $]) AS TotalSales,
Sum([Sales Units]) AS TotalUnits
FROM (
SELECT TOP 8 [Sales $], [Sales Units]
FROM [Weekly Sales by type All Items]
WHERE ItmNbr=10025
AND [Week Ending]<=#6/10/2006#
AND Promo Is Null
ORDER BY [Week Ending] Desc
) AS WSAI

The inner query selects the 8 previous non-promo weeks for the item, the outer query performs the count and sums. The code below will simply run this query for each record and store the averages. It's not possible to do it entirely in SQL, because a total query is non-updatable, and you can't create an update query with non-updatable queries as source. So.

In the code, I commented out the "strict" rule for moving averages (compute only if 8 weeks of data are available), using the more lenient rule (compute average for 1 to at most 8 weeks of data). You will choose which best suits your purpose.

I hope you like it!
(°v°)

                       
         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:

           
           
             Function DateSQL(pvarDate) As String
' Create a string representation suitable for SQL
' Jet accepts only US dates or ISO dates
 
    If IsNull(pvarDate) Then
        DateSQL = "Null"
    Else
        DateSQL = Format(pvarDate, "\#yyyy\-mm\-dd\#")
    End If
    
End Function
 
Sub ComputeAllAverages()
' Calculate moving averages for sales and units in the weekly sales table
' A single total query is used to calculate both for each record
 
    Const SALES = "Weekly Sales by type All Items"
    Const SCOPE = 8   ' nb of weeks in moving average
    
    Dim mdb As DAO.Database
    Dim recMain As DAO.Recordset
    Dim recTot As DAO.Recordset
    Dim strSQL As String
    
    Set mdb = CurrentDb
    Set recMain = mdb.OpenRecordset(SALES, dbOpenDynaset)
    Do Until recMain.EOF
        
        ' build query
        strSQL _
            = " SELECT" _
            & " Count(*) AS Nb," _
            & " Sum([Sales $]) AS TotalSales," _
            & " Sum([Sales Units]) AS TotalUnits" _
            & " FROM (" _
            & "     SELECT TOP " & SCOPE & " [Sales $], [Sales Units]" _
            & "     FROM [" & SALES & "]" _
            & "     WHERE ItmNbr = " & recMain!ItmNbr _
            & "     And [Week Ending] <= " & DateSQL(recMain![Week Ending]) _
            & "     And Promo Is Null" _
            & "     ORDER BY [Week Ending] Desc" _
            & " ) AS WSAI"
        Set recTot = mdb.OpenRecordset(strSQL, dbOpenSnapshot)
        
        ' store results
        recMain.Edit
        ' If recTot!Nb <> SCOPE Then   ' strict moving average
        If recTot!Nb > 0 Then   ' lenient moving average
            recMain!AvgWklyDollars = Null
            recMain!AvgWklyUnits = Null
        Else
            recMain!AvgWklyDollars = recTot!TotalSales / recTot!Nb
            recMain!AvgWklyUnits = recTot!TotalUnits / recTot!Nb
        End If
        recMain.Update
        
        recMain.MoveNext
    Loop
 
End Sub

           
         

Open in New Window

03.03.2008 at 09:53AM PST, ID: 21033903

I am trying to understand the logic. Why did the Top 8 total query filter by a specific itmnbr and [Week Ending]? Don't I want to count and total all the records?

03.03.2008 at 02:27PM PST, ID: 21036403

I am sorry to be so dense.

Now that we have the Sub, what do I do with it? After a couple of tweaks in the code, it seems to run without errors. In my original customer function, I just used the function in my query. But now that I'm computing the averages in a Sub, I don't know how to use this to update my Weekly sales table.

Thanks, again.

03.04.2008 at 08:32AM PST, ID: 21042467

Rank: Genius

The code does calculate the moving average for all records. For each, it does average only eight (or up to eight) records, naturally.

In your own code, you implemented a function. This was then called from an update query in order to store the results. This meant for example that you needed two functions, one for sales amounts, another for units: in effect doing almost the same thing twice.

The code above is a procedure. You call it from VB (e.g. from a button on a form), and the entire weekly sales table is updated. If functions as an update query (it's data updating code). This is the meaning of the recMain.Edit / recMain.Update pair. For testing, you ran run it directly from VB: either press [Run] while the cursor is in the Sub, or use this from the Immediate Pane:

ComputeAllAverages ' and press [Enter]

If updating the entire table takes too long, you can use a query instead of the raw table as source (for example selecting only records less than a month old).

Tell me if this doesn't seem to work. Good luck!
(°v°)

03.04.2008 at 09:21AM PST, ID: 21042917