Summing next 10 days (1 Viewer)

SiGill

Registered User.
Local time
Today, 14:52
Joined
Dec 22, 2008
Messages
72
How can I get Access to get an average of the next 10 days orders. See example picture.
So todays date is the 20-Mar as highlighted in orange. I need to get the average of the next 10 days orders (21st to 30th March), the ones highlighted in Green. To give me the figure in Blue.
But I also need to carry this down so 21st March will get an average of 22nd to 31st March, etc, etc.
Anyone know some coding to get this?
Capture.JPG
 

bob fitz

AWF VIP
Local time
Today, 21:52
Joined
May 23, 2011
Messages
4,717
You could use DAvg() or you could use a "Totals" query.
Where do you need to use the result?
 

SiGill

Registered User.
Local time
Today, 14:52
Joined
Dec 22, 2008
Messages
72
Thanks Bob. The result will be used in a query. The closing stock will then divide by this average to give the approx number of days stock
DAvg() looks good, but how can I tell it that it needs to be 10 days after the date on the current row?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:52
Joined
May 7, 2009
Messages
19,169
DAvg("Orders", "YourTable", "[Date] Between [Date]+1 And [Date]+10")
 

bob fitz

AWF VIP
Local time
Today, 21:52
Joined
May 23, 2011
Messages
4,717
Thanks Bob. The result will be used in a query. The closing stock will then divide by this average to give the approx number of days stock
DAvg() looks good, but how can I tell it that it needs to be 10 days after the date on the current row?
Create a calculated field using the expression already given by arnelgp
 

SiGill

Registered User.
Local time
Today, 14:52
Joined
Dec 22, 2008
Messages
72
Thanks both. Have tried that, but its coming out blank. See below
Query.JPG


Result.JPG
 

bob fitz

AWF VIP
Local time
Today, 21:52
Joined
May 23, 2011
Messages
4,717
Try Saving the query and then reopening it.
 

plog

Banishment Pending
Local time
Today, 16:52
Joined
May 11, 2011
Messages
11,612
[Date] is never going to be between ([Date] +1) and ([Date]+10)

The DAvg criteria should be:

"[Date] Between #" & [Date]+1 & "# And #" &[Date]+10 & "#")

Its Kinda of inception like. You've got a DAvg inside a query so you are dealing with 2 different [Date] fields--one operating inside the Davg and one with every record of the query the Davg is inside of . The first [Date] reference is talking about the [Date] field in the DAvg. The 2nd and 3rd [Date] references are refering to the [Date] value of the current record of the query.
 

SiGill

Registered User.
Local time
Today, 14:52
Joined
Dec 22, 2008
Messages
72
[Date] is never going to be between ([Date] +1) and ([Date]+10)

The DAvg criteria should be:

"[Date] Between #" & [Date]+1 & "# And #" &[Date]+10 & "#")

Its Kinda of inception like. You've got a DAvg inside a query so you are dealing with 2 different [Date] fields--one operating inside the Davg and one with every record of the query the Davg is inside of . The first [Date] reference is talking about the [Date] field in the DAvg. The 2nd and 3rd [Date] references are refering to the [Date] value of the current record of the query.
Thanks plog, this looks like it has worked perfectly. Will try it more during the day. Thank you
 

SiGill

Registered User.
Local time
Today, 14:52
Joined
Dec 22, 2008
Messages
72
Its not quite perfect. Not sure what its doing. See below
This is what Excel says I should get, in yellow. The orange ones are what match to Access
Excel Example.JPG


And this is what Access is giving me
Access Result.JPG


This is the Access query
Access Query.JPG


I can't figure out why its so different. Anyone able to help at all?
 

SiGill

Registered User.
Local time
Today, 14:52
Joined
Dec 22, 2008
Messages
72
Could it be to do with the date formatting? i.e. 01/04/2021 (1st April in UK formatting) becomes 04/01/2021 (4th Jan in USA formatting)

If so how can I get around this?
 

SiGill

Registered User.
Local time
Today, 14:52
Joined
Dec 22, 2008
Messages
72
Got it! It was to do with the date formatting. So I had to amend the query to this

DAvg("Orders","Sheet1","[DueDate] Between #" & Format([DueDate]+1,"yyyy/mm/dd") & "# and #" & Format([DueDate]+10,"yyyy/mm/dd") & "#")
 

Users who are viewing this thread

Top Bottom