Calculate most sold in a consecutive 3 day period for each item

[Schon731]

I am looking for some help to figure out the largest quantity sold for each item in my inventory for any given 3 day period. So basically I want it to look at say a 6 week or 3 month range of dates and calculate each total sold for every 3 day consecutive period. So day 1 + day 2 + day 3 =x, then day 2 + day 3 + day 4 = y, then if x is greater then y, then x, else Y and have it continue through each day within the table and get the highest value. I think I can sort of get this first part but its the part of continuing on from the day to next day that I am confused. Thanks in advance.

[Schon731]

The other thing I need to account for are days that I don't sell any so if I don't sell one on day 3 I still want to add day 1 + day 2 + day 3.

[ranman256]

'Build Qry1 that is based on 2 dates on form (the date range)
Qry1= between forms!frmRpt!StartDate and forms!frmRpt!EndDate

'then run the loop that executes a query for every 3 day block from Qry1

iDays =datediff("d", forms!frm!StartDate,forms!frm!EndDate) 
for i = 0 and iDays +1
   vStart = dateadd("d",i ,date1)
   VEnd = dateadd("d",3, vStart )
      'append results to rpt tbl
   ssql = "INSERT INTO tRptTbl( 3DayPd, Amt ) SELECT " _ 
   vStart & "-to-" & VEnd & " AS Expr1 FROM Qry1 _
   WHERE ((Qry1.Date) Between " & vStart & " and " & VEnd & ")) GROUP BY "date1 to date2";
   docmd.runsql ssql
next

[Schon731]

Will this work still if my first query for the range doesn't contain anything for certain dates? In other words, we are closed on weekends or I may not sell that item every day so I will have dates for Mon-Fri but on Sat or sunday there will be no date for that item or have it for M, Tue, Thur, Fri but not Wednesday. I tried using a table with a date for every day and tying it into the first query but I only get results with dates that the item was sold.

[rpeare]

no that won't work for non consecutive days, it is also creating a record for every 3 day period within the 3 - 6 months. Do you really want all of that or are you only interested in the MOST activity. I am assuming also that your table is not summarized and you may have multiple sales records for the same item on the same day. Here's what I did:

1. Create this table:

Sale_ID	Item_ID	Customer_ID	Sale_Date	Sale_Qty
1	1	1	1/1/2014	1
2	1	2	1/1/2014	5
3	1	3	1/1/2014	1
4	1	1	1/5/2014	10
5	1	2	1/6/2014	2
6	1	2	1/7/2014	5
7	1	3	1/10/2014	4
8	2	1	1/1/2014	5
9	2	2	1/2/2014	10
10	2	3	1/3/2014	1
11	2	4	1/4/2014	2
12	2	1	1/5/2014	6

2. Create a table named tblDATERANGES with two fields STARTDATE and ENDDATE, make the primary key of this table a combination of startdate and enddate

3. Run this code

Dim dMinDate As Date
Dim dMaxDate As Date
Dim dCurrDate As Date
Dim dStartRange As Date
Dim dEndRange As Date
Dim sSQL As String
Dim db As Database

dMinDate = #1/1/2014#
dMaxDate = #1/31/2014#

dCurrDate = dMinDate

Set db = CurrentDb
db.Execute ("DELETE * FROM tblDateRanges")
Do While dCurrDate <= dMaxDate - 3
    If DatePart("w", dCurrDate) = 7 Then
        dStartRange = dCurrDate + 2
        dEndRange = dCurrDate + 5
    ElseIf DatePart("w", dCurrDate) = 1 Then
        dStartRange = dCurrDate + 1
        dEndRange = dCurrDate + 4
    Else
        dStartRange = dCurrDate
        dEndRange = dCurrDate + 3
    End If
    
    sSQL = "INSERT INTO tblDateRanges ("
    sSQL = sSQL & "StartDate, "
    sSQL = sSQL & "EndDate"
    sSQL = sSQL & ") VALUES ("
    sSQL = sSQL & "#" & dStartRange & "#, "
    sSQL = sSQL & "#" & dEndRange & "#"
    sSQL = sSQL & ")"

    db.Execute sSQL
    dCurrDate = dCurrDate + 1
Loop

This will populate the table tblDateRanges with 'valid' date ranges that excludes weekends.

4. Run this query:

SELECT tblSales.Item_ID, tblDateRanges.StartDate, tblDateRanges.EndDate, Sum(IIf([sale_date] Between [startdate] And [enddate],[sale_qty],0)) AS 3DayNet
FROM tblDateRanges, tblSales
GROUP BY tblSales.Item_ID, tblDateRanges.StartDate, tblDateRanges.EndDate;

This query is known as a cartesian query, where you are taking every possible permuation of the start/enddate and the item_ID, I've just summarized them by the item id/startdate/enddate to give you the result you want. I perfer this method because you are processing a minimum of data rather than processing all of your sales information every time you run it. You're just re-arranging the valid sets of dates and running a query which is far, far more efficient than trying to process the sales table every time.

[Schon731]

Thank you, I will set this up and give it a try.

[zpele]

Mesed up this post lol.... Please delete.

[zpele]

no that won't work for non consecutive days, it is also creating a record for every 3 day period within the 3 - 6 months. Do you really want all of that or are you only interested in the MOST activity. I am assuming also that your table is not summarized and you may have multiple sales records for the same item on the same day. Here's what I did:

1. Create this table:

Sale_ID	Item_ID	Customer_ID	Sale_Date	Sale_Qty
1	1	1	1/1/2014	1
2	1	2	1/1/2014	5
3	1	3	1/1/2014	1
4	1	1	1/5/2014	10
5	1	2	1/6/2014	2
6	1	2	1/7/2014	5
7	1	3	1/10/2014	4
8	2	1	1/1/2014	5
9	2	2	1/2/2014	10
10	2	3	1/3/2014	1
11	2	4	1/4/2014	2
12	2	1	1/5/2014	6

2. Create a table named tblDATERANGES with two fields STARTDATE and ENDDATE, make the primary key of this table a combination of startdate and enddate

3. Run this code

Dim dMinDate As Date
Dim dMaxDate As Date
Dim dCurrDate As Date
Dim dStartRange As Date
Dim dEndRange As Date
Dim sSQL As String
Dim db As Database

dMinDate = #1/1/2014#
dMaxDate = #1/31/2014#

dCurrDate = dMinDate

Set db = CurrentDb
db.Execute ("DELETE * FROM tblDateRanges")
Do While dCurrDate <= dMaxDate - 3
    If DatePart("w", dCurrDate) = 7 Then
        dStartRange = dCurrDate + 2
        dEndRange = dCurrDate + 5
    ElseIf DatePart("w", dCurrDate) = 1 Then
        dStartRange = dCurrDate + 1
        dEndRange = dCurrDate + 4
    Else
        dStartRange = dCurrDate
        dEndRange = dCurrDate + 3
    End If
    
    sSQL = "INSERT INTO tblDateRanges ("
    sSQL = sSQL & "StartDate, "
    sSQL = sSQL & "EndDate"
    sSQL = sSQL & ") VALUES ("
    sSQL = sSQL & "#" & dStartRange & "#, "
    sSQL = sSQL & "#" & dEndRange & "#"
    sSQL = sSQL & ")"

    db.Execute sSQL
    dCurrDate = dCurrDate + 1
Loop

This will populate the table tblDateRanges with 'valid' date ranges that excludes weekends.

4. Run this query:

SELECT tblSales.Item_ID, tblDateRanges.StartDate, tblDateRanges.EndDate, Sum(IIf([sale_date] Between [startdate] And [enddate],[sale_qty],0)) AS 3DayNet
FROM tblDateRanges, tblSales
GROUP BY tblSales.Item_ID, tblDateRanges.StartDate, tblDateRanges.EndDate;

This query is known as a cartesian query, where you are taking every possible permuation of the start/enddate and the item_ID, I've just summarized them by the item id/startdate/enddate to give you the result you want. I perfer this method because you are processing a minimum of data rather than processing all of your sales information every time you run it. You're just re-arranging the valid sets of dates and running a query which is far, far more efficient than trying to process the sales table every time.

But the code you listed wont quite work. It is doing 4 day intervals instead of 3 since it is not counting the first day. Wouldnt this be correct instead?

Option Compare Database


Sub a()
Dim dMinDate As Date
Dim dMaxDate As Date
Dim dCurrDate As Date
Dim dStartRange As Date
Dim dEndRange As Date
Dim sSQL As String
Dim db As Database


dMinDate = #7/1/2014#
dMaxDate = #7/31/2014#


dCurrDate = dMinDate


Set db = CurrentDb
db.Execute ("DELETE * FROM tblDateRanges")
Do While dCurrDate <= dMaxDate - 3
If DatePart("w", dCurrDate) = 7 Then
dCurrDate = dCurrDate + 2
End If
If DatePart("w", dCurrDate) = 1 Then
dCurrDate = dCurrDate + 1
End If
dStartRange = dCurrDate
dEndRange = dCurrDate + 2



sSQL = "INSERT INTO tblDateRanges ("
sSQL = sSQL & "StartDate, "
sSQL = sSQL & "EndDate"
sSQL = sSQL & ") VALUES ("
sSQL = sSQL & "#" & dStartRange & "#, "
sSQL = sSQL & "#" & dEndRange & "#"
sSQL = sSQL & ")"


db.Execute sSQL
dCurrDate = dCurrDate + 1
Loop
End Sub