Apologies for the delay in replying, but I've been on the road for the past
24 hours.
One way to identify any three month period over a date range would be to
generate values in a table for all start and end months of a three month
sequence in the total range, and then join the table of sales to this so that
items sold within all possible three monthly sequences in the range can be
identified and sales values aggregated. Each sale will appear in several
sequences of course as a sale this month would be in the sequences March-May,
April-June and May-July assuming the overall range starts at or before March
2008 and ends at or after July 2008. The following procedure would generate
the values in a table by passing the start date into it, and the number of
months:
Public Sub CreateThreeMonths(dtmStart As Date, intMonths As Integer)
Const NO_TABLE = 3265
Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim strStartMonth As String
Dim strEndMonth As String
Dim n As Integer
Set dbs = CurrentDb
' does table exist? If not create it
On Error Resume Next
Set tdf = dbs.TableDefs("ThreeMonths")
Select Case Err.Number
Case NO_TABLE
' create new table
strSQL = "CREATE TABLE ThreeMonths" & _
"(StartMonth CHAR(6),EndMonth CHAR(6))"
dbs.Execute strSQL
' fill table
Case 0
' empty table
strSQL = "DELETE * FROM ThreeMonths"
dbs.Execute strSQL
Case Else
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End Select
' turn default error handling back on
On Error GoTo 0
' fill table
For n = 1 To intMonths - 2
strStartMonth = Format(DateAdd("m", n - 1, dtmStart), "yyyymm")
strEndMonth = Format(DateAdd("m", n + 1, dtmStart), "yyyymm")
strSQL = "INSERT INTO ThreeMonths(StartMonth, EndMonth) " & _
"VALUES(""" & strStartMonth & """,""" & strEndMonth & """)"
dbs.Execute strSQL
Next n
End Sub
To create the values from the current month for the next two years for
instance it would be called like this:
CreateThreeMonths Date(),24
This would create rows starting with start and end values of 200805 and
200807, representing May-July 2008 and ending with 201002 and 201004,
representing February-April 2010.
This table can then be joined with a Sales table like so to give the sum of
sales per item within all possible three month sequences:
SELECT Item, StartMonth, EndMonth,
SUM(Amount) AS TotalSales
FROM Sales, ThreeMonths
WHERE FORMAT(SaleDate,"yyyymm")
BETWEEN ThreeMonths.StartMonth
AND ThreeMonths.EndMonth
GROUP BY Item, StartMonth, EndMonth;
Other aggregation operators could be used instead of or in addition to SUM
to return different results, COUNT to return the number of sales per item,
MAX the highest sale per item, MIN the lowest, AVG the average sale per item
within each three month sequence.
To aggregate values for all items within each three month sequence Item
would simply be removed from the SELECT and GROUP BY clauses.
Ken Sheridan
Stafford, England
Steve said:
Steve:
Open the crosstab query in design view, then swith to SQL view and amend the
PIVOT clause so that in returns a separate column for each year/quarter like
so:
PIVOT Year([SaleDate]) & " Qtr " & Format([SaleDate],"q");
Ken Sheridan
Stafford, England
Steve said:
I have a database which shows all sales for the last 7 years and I am
trying to get more sophisticated with re-order points for stock held.
Specifically I want to be able to specify a start and end date and
have the system calculate the largest sales for any 3 month period
between those dates (Delivery time from manuafacturer can be up to 3
months).
I have the basic extraction of the data working well with cross tab
queries showing me sales for each month for the selected dates (used
yyyy mm as the format so they sort in ascending order).
However. while I can do a cross tab by quarter, I have two issues:
The periods covered often exceed one year so the cross tab query
wizards offer to report quarterly gives me wrong data as, for example,
there could be two first quarters
I have no idea how to find the largest 3 month sales over say a two
year period
Steve- Hide quoted text -
- Show quoted text -
Terrific- Thanks for that -
Any suggestions on rolling averages
