UtterAccess.com
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

TBL_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,325
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,325
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

I'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,325
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,325
From: Bethesda, MD USA



(IMG:style_emoticons/default/thumbup.gif)

Awesome!!! 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: 23rd April 2014 - 11:57 AM