UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Date Calculation Query    
 
   
Grettons
post Sep 24 2010, 07:34 AM
Post #1

UtterAccess Enthusiast
Posts: 70



Hi All,
I need to create a report that will give me an cumulative average time in between 2 dates on my order forms
BL_Lines contains Brand, Order_Date and Completed_Date
I want the report to look for all orders per brand and give me the average days between the Order_Date and Completed_Date.
I tried this but it comes back blank?
Average Order: [Count] Between [«Order_Date»] And [«Completed_Date»]
Many thanks
Grettons
Go to the top of the page
 
+
RAZMaddaz
post Sep 24 2010, 08:09 AM
Post #2

UtterAccess VIP
Posts: 7,698
From: Bethesda, MD USA



Are you trying to do this in a Query?
If so, Group the Query by your Brand Field, then in the second Field/Column in the Query make it the following:
Average Order: Avg([Completed Date]-[Order Date])
Also, change the Group in the Total Row to Expression
Go to the top of the page
 
+
Grettons
post Sep 24 2010, 10:30 AM
Post #3

UtterAccess Enthusiast
Posts: 70



Hi RAZMaddaz,
I've managed to get the query returning values, but it is returning a value per Line rather than just giving an Average per brand
Current layout
Brand Average
Brand
Brand 1 10
Brand 1 2
Brand 1 8
Brand 1 14
Brand 2 11
Brand 2 12
Brand 2 13
example of how I want it to look
Brand Average
Brand 1 10
Brand 2 8
Brand 3 1
Is this possible using the criteria section or would I need to overlay another query on top?
Many Thanks
Grettons
Go to the top of the page
 
+
RAZMaddaz
post Sep 24 2010, 10:35 AM
Post #4

UtterAccess VIP
Posts: 7,698
From: Bethesda, MD USA



It looks like you are not Grouping the Query based on the Brand Field. Is that correct?
Go to the top of the page
 
+
Grettons
post Sep 24 2010, 10:41 AM
Post #5

UtterAccess Enthusiast
Posts: 70



I've grouped the total section on the brand column
've attached screen shots of the Query and the results
Attached File(s)
Attached File  AVG_LeadTimeExample.zip ( 107.01K ) Number of downloads: 7
 
Go to the top of the page
 
+
RAZMaddaz
post Sep 24 2010, 10:45 AM
Post #6

UtterAccess VIP
Posts: 7,698
From: Bethesda, MD USA



It's because you also are Grouping by the Order_Date and the Completed_Date, it is not calculating the Average per Brand correctly. Remove/Delete from the Query the Order_Date and the Completed_Date.
Go to the top of the page
 
+
Grettons
post Sep 24 2010, 10:47 AM
Post #7

UtterAccess Enthusiast
Posts: 70



Yeah that sorted it thanks for you're help
Go to the top of the page
 
+
RAZMaddaz
post Sep 24 2010, 10:48 AM
Post #8

UtterAccess VIP
Posts: 7,698
From: Bethesda, MD USA



>
wesome!!! Glad I could help!!!!
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 19th September 2014 - 08:48 AM