Using Average In Select Query

[Guest]

I am trying to find the average of a user defined field, this is what the
query fields and statements look like:

Field: full_account
Table: dbo_FULL_ACCOUNT

Field: tank_num
Table: dbo_TANKS

Field: Average Service
TimeLeft([dbo_TRANS_DELIVERY.end_time],Len([dbo_TRANS_DELIVERY.end_time])-2)*60+Right([dbo_TRANS_DELIVERY.end_time],2))-(Left([dbo_TRANS_DELIVERY.start_time],Len([dbo_TRANS_DELIVERY.start_time])-2)*60+Right([dbo_TRANS_DELIVERY.start_time],2))-((1/60)*[units])

I have tried turning totals on using Group By for the 1st two fields and Avg
for the last field but the query complains that it is an Invalid procedure
call.

Thanks,

Paul

 

[John Spencer]

I'm not sure what you are doing, but I do know that you need to refer to the
table and field names a bit differently.
Instead of
[dbo_TRANS_DELIVERY.end_time]
You need
[dbo_TRANS_DELIVERY].[end_time]

In other words, you need brackerts around the tablename and another set
around the field name. OF course, you don't really need the brackets at all
since the table and field names don't contain spaces or other special
characters. The following is a valid reference to the table and its field.
dbo_TRANS_DELIVERY.end_time]

Once you have fixed that, you might try building up the calculated field a
step at a time. Perhaps that will point out the error.

Field: Average Service Time:
Left([dbo_TRANS_DELIVERY].[end_time],Len([dbo_TRANS_DELIVERY].[end_time])-2)*60If that works then add in the next bit + Right([dbo_TRANS_DELIVERY].[end_time],2)...--John SpencerAccess MVP 2002-2005, 2007Center for Health Program Development and ManagementUniversity of Maryland Baltimore County."Paul" <[email protected]> wrote in messagenews:[email protected]...>I am trying to find the average of a user defined field, this is what the> query fields and statements look like:>> Field: full_account> Table: dbo_FULL_ACCOUNT>> Field: tank_num> Table: dbo_TANKS>> Field: Average Service>TimeLeft([dbo_TRANS_DELIVERY.end_time],Len([dbo_TRANS_DELIVERY.end_time])-2)*60+Right([dbo_TRANS_DELIVERY.end_time],2))-(Left([dbo_TRANS_DELIVERY.start_time],Len([dbo_TRANS_DELIVERY.start_time])-2)*60+Right([dbo_TRANS_DELIVERY.start_time],2))-((1/60)*[units])>> I have tried turning totals on using Group By for the 1st two fields andAvg> for the last field but the query complains that it is an Invalid procedure> call.>> Thanks,>> Paul

 

[Guest]

John,

I apologize for being so vauge.

I am trying to average a column that contains the amount of time in minutes
each service call takes. IE: sum/ # of records =

I did break out the fields a step at a time and the field does display the
data correctly. I just lost on the next step on how to find the average of
this column.

Thanks,

Paul