How to calculate mean and standard deviation?

[freddyjo]

Hi.

Hope someone can help me with this problem. I have just started using access and I definitely need help.

Problem:
I have a table with sales history for different products.
The table is pretty large and contains sales history from 2002 to YTD.
There are thousands of different products.

I want to make a VBA procedure that calculate the mean and standard deviation for sales per item per week over a given period of time.
I have the following fields: [Itemno],[SalesDate] and [SalesQty].

A part of the problem is that one item may only have e.g. 3 sales withdrawals per year and the table only contains the withdrawals:

Itemno SalesDate SalesQty
02024 Week 2 100
02024 Week 16 70
02024 Week 43 200

Ideally I would like to use SalesQty = 0 in the weeks with no sales in the calculation:

Itemno SalesDate SalesQty
02024 Week 1 0
02024 Week 2 100
02024 Week 3 0
02024 Week 4 0
02024 Week 5 0
.
.
.
02024 Week 16 70
.
.
02024 Week 43 200
.
.
02024 Week 52 0

Ideally the program or procedure may return the result in a new table.

Anyone understand what I mean?
Is this achievable in Access?

Regards,
freddyjo

 

[SteveC24]

Afraid I won't be of much help - but have you tried Access Help (F1)?

You could also try looking on the Microsoft website, there are some great help files on there now-a-days.

 

[baldeagle]

What about the report function?

You could group it by Week, then turn on the summery options(Count, Ave, Min, Max)
Standard Deviation is the Square root of [ The sum of (each sale qty-avg sales qty) divided by the number of sales withdrawls].

Hope that helps some...

 

[freddyjo]

Thanks,

But still I want to take the mean and stdev over a given time period, lets 52 weeks. If I group it by week and the product only was sold in week 3, 6, and 42 I get only 3 records. When I average this it will not be the same as when I average it over 52 weeks.

 

[SunWuKung]

Don't bother with code, use views.
If you don't insist on considering 0 values it is very easy - a simple query.

Select Avg(SalesQty) As AverageSQty, Stdev(SalesQty) As StdSQty
From Sales
Where SalesDate > 10 AND SalesDate < 20

If you have to have 0's it is still doable but more tricky.

I will presume that you filter for years somewhere else so create a table called AllWeeks with all weeks in one year (which is just a table with nubers from 1 to 52).

Create a query that will have all records from AllWeeks and only the related records from Sales. Select SalesQty using Iif or Case. I don't know the Access syntax but something like Iif (SalesQty IS NULL, 0, SalesQty)
This will give you a view like you described.

Run the query on the top on this view.

hth
SWK

 

[billz260]

I'm trying to do the same thing but with callender days. I need to fill in the days that don't have any activity with a "0" so the std dev calculates correctly. And it won't be as simple as creating a table with Jan 1 - Dec 31, most of the activity will be on callendar work days only which changes year to year.

Any thoughts?

 

[billz260]

I created a Qry (also tried with a table) that pulled all the active days of all location for the year. I then tried a comparison Qry between it and a select location with and IIF statment that I thought would produce "0" for the days represented in the "ALL DAYS" set of data.

But in the resluts, I am only getting the days that had activity in the select locaiton. Is there a proporty of the DATE data type that forces Access to act this way? What I was expecting was ALL DATES + values for the dates where the select location had activity.

I'm confused.

 

[DCrake]

Simple Software Solutions

Just thinking aloud; if you have a form that asks you for a product and a date range. Why don't you (in VBA).

Have a temp table with the desired fields in it.
Use a recordset to generate the filtered records sorted by date ascending
Loop throught the recordset and add the info to a new record. At the same time have a date variable on a DateAdd("d",x,StartDate") loop. Where x is the loop count.

Check to see if you have any data in your recordset for that date and if not still add the bais info but add 0 (zero) for sales. This will then give you a full set of dates with sales quantities for all dates.

Dim nDate As Date
Dim Rs As RecordSet
Dim Tmp As RecordSet

Set Rs  = CurrentDb.OpenRecordset("Select * From TblSales Where Product='ABC123' And SalesDate Between #01/01/2006# And #31/12/2006# ORDER BY SalesDate;")

Set Tmp = CurrentDb.OpenRecordSet("TmpTable")

If Rs.EOF And Rs.BOF Then
    Set Rs = Nothing
    Exit Function
End If

nDate = Rs("SalesDate")
Do Until Rs.EOF
        
Tmp.AddNew
    'Is there any data for this date
    If Rs("SalesDate") = nDate Then
        TmpTable("SalesDate") = Rs("SalesDate")
        TmpTable("SalesQTY) = Rs("SalesQTY")
        TmpTable(ProductID") = Rs("ProductID")
        Etc...
    'Append default values
    Else
        TmpTable("SalesDate") = nDate 
        TmpTable("SalesQTY) = 0
        TmpTable(ProductID") = Rs("ProductID")
        Etc...
   End If
   TmpTable.Update
    nDate = DateAdd("d",1,nDate)

   Rs.MoveNext
Loop
Rs.Close

Set Rs = Nothing

This code has not been tested, table/field names for brevity only.

CodeMaster:

 

[billz260]

Thanks, unfortunatly I don't know much about programming but I used the logic and figured it out by using the Qry Builder.

Thanks!

 

[Andy_H]

figured it out by using the Qry Builder.

Thanks!

... But didn't bother to post the method here as it was too much effort why not give something back once in a while?