Finding an average

  • Thread starter Thread starter Gary Nelson
  • Start date Start date
G

Gary Nelson

In Access2000, I am attempting to design a query which will give me the
average number of days it takes to ship an order. DateIn (when the order
arrives to us) DateShipped (when the order ships). Any thoughts?
 
Hi Gary,
Figured I'd answer cuz I've been following your project.
You could create the following fields in a new query.

totdays: DateDiff("d",[datein],[dateshipped])

numorders: Datein

avgdays: [totdays]/[numorders]

Change the query to a totals query. Instead of "group by"
in the total line, change first one to sum, second one to
count, and third one to expression.

It will give you total number of days for orders, total
orders, and average days for all orders
 
Les,

This portion I have done, and it's working. I should have been more
explicit with my question. What I am having difficulty with, is getting the
average of all the jobs within a specified date range. If there are twenty
jobs, what is the average of the twenty jobs. If the next date range is
five jobs, then what is the average days for the five.
Les said:
Hi Gary,
Figured I'd answer cuz I've been following your project.
You could create the following fields in a new query.

totdays: DateDiff("d",[datein],[dateshipped])

numorders: Datein

avgdays: [totdays]/[numorders]

Change the query to a totals query. Instead of "group by"
in the total line, change first one to sum, second one to
count, and third one to expression.

It will give you total number of days for orders, total
orders, and average days for all orders


-----Original Message-----
In Access2000, I am attempting to design a query which will give me the
average number of days it takes to ship an order. DateIn (when the order
arrives to us) DateShipped (when the order ships). Any thoughts?


.
Click to expand...
Click to expand...
 
Sorry,
I misread your posting.

This should give you the info that you need.
Create new totals query.

1) Datein will have Total set to "Where", criteria will
be "Between [enter from] and [enter to]"

2) create new column - numdays:DateDiff('d',[Datein],
[dateshipped]), Total set to "Sum"

3) create new column CntofOrders:Datein, Total set
to "Count"

4) create new column AvgDays: [numdays]/[CntofOrders],
Total set to "Expression"

Here it is in SQL form

SELECT Sum(DateDiff('d',[Datein],[dateshipped])) AS
numdays,
Count(Table.Datein) AS CntofOrders,
[numdays]/[cntoforders] AS AvgDays
FROM Table
WHERE (((Table.Datein) Between [enter from] And [enter
to]));
-----Original Message-----
Les,

This portion I have done, and it's working. I should have been more
explicit with my question. What I am having difficulty with, is getting the
average of all the jobs within a specified date range. If there are twenty
jobs, what is the average of the twenty jobs. If the next date range is
five jobs, then what is the average days for the five.
Hi Gary,
Figured I'd answer cuz I've been following your project.
You could create the following fields in a new query.

totdays: DateDiff("d",[datein],[dateshipped])

numorders: Datein

avgdays: [totdays]/[numorders]

Change the query to a totals query. Instead of "group by"
in the total line, change first one to sum, second one to
count, and third one to expression.

It will give you total number of days for orders, total
orders, and average days for all orders


-----Original Message-----
In Access2000, I am attempting to design a query which will give me the
average number of days it takes to ship an order.
Click to expand...
DateIn
(when the order
arrives to us) DateShipped (when the order ships). Any thoughts?


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top