Is this possible in a query?

[Pea]

Is it possible to get the average in a query where I have multiple
criteria?
Here's an abbreviated example of the query in design view:

Fields: USERID DATE TIME ID_TYPE
Criteria: <>JoeSchmo <>*2003 <7AM <>this and <>that
Criteria: <>JoeSchmo <>*2003 7-9AM <>this and <>that
Criteria: <>JoeSchmo <>*2003 9-11AM <>this and <>that

Question: is it possible to get a total count of the number of rows
that meet each criteria and also add a column with an expression to
get the average from each criteria compared with the total row count
in the whole table? Or can this be done easier in a report or form?

Any hints would be greatly appreciated.

Thanks,
Tara

[Salad]

Pea wrote:

Is it possible to get the average in a query where I have multiple
criteria?
Here's an abbreviated example of the query in design view:

Fields: USERID DATE TIME ID_TYPE
Criteria: <>JoeSchmo <>*2003 <7AM <>this and <>that
Criteria: <>JoeSchmo <>*2003 7-9AM <>this and <>that
Criteria: <>JoeSchmo <>*2003 9-11AM <>this and <>that

Question: is it possible to get a total count of the number of rows
that meet each criteria and also add a column with an expression to
get the average from each criteria compared with the total row count
in the whole table? Or can this be done easier in a report or form?

Any hints would be greatly appreciated.

Thanks,
Tara

You can start off with a query and build off of that one if needed.

Qyery/New/Design. Add your table then close.

Drag UserID, Date, Time (3 times) Time1...Time3, And IDType to the
In the criteria enter
<>"JoeSchmo"
under UserID.

I'll assume the date is a value that stores m,d,y. So enter
<>Year(2003)
under Date

In Time1 to time3 it should look like
Time1 : Time
Time2 : Time
Time3 : Time
in the data field row. In the criteria enter
<#7:00:00# for Time1
Between #7:00# And #8:59:59# for Time2
Between #9:00# And #10:59:59# For Time3

Now make this a Totals Query. From the menu, View/Totals

GroupBy should be assigned to UserID and IDType
Count should be assigned to Time1,2,3
Where should be assigned to the date...unless you want to group on the date.

Save and Run.

Now you can create another query that calls this one and average the
counts or you can use the above query in a report and calc the average
in a textbox's control source. Ex:
=(Time1 + Time2 + Time3) /3

Note I used / 3. You can also use \ 3 too. Here's an example of both
? 100/3
33.33333
? 100 \ 3
33

The \ gives an integer value.

[Pea]

Salad <oi*@vinegar.com> wrote in message news:<1B****************@newsread3.news.pas.earthl ink.net>...

You can start off with a query and build off of that one if needed.

Qyery/New/Design. Add your table then close.

Drag UserID, Date, Time (3 times) Time1...Time3, And IDType to the
In the criteria enter
<>"JoeSchmo"
under UserID.

I'll assume the date is a value that stores m,d,y. So enter
<>Year(2003)
under Date

In Time1 to time3 it should look like
Time1 : Time
Time2 : Time
Time3 : Time
in the data field row. In the criteria enter
<#7:00:00# for Time1
Between #7:00# And #8:59:59# for Time2
Between #9:00# And #10:59:59# For Time3

Now make this a Totals Query. From the menu, View/Totals

GroupBy should be assigned to UserID and IDType
Count should be assigned to Time1,2,3
Where should be assigned to the date...unless you want to group on the date.

Save and Run.

Now you can create another query that calls this one and average the
counts or you can use the above query in a report and calc the average
in a textbox's control source. Ex:
=(Time1 + Time2 + Time3) /3

Note I used / 3. You can also use \ 3 too. Here's an example of both
? 100/3
33.33333
? 100 \ 3
33

The \ gives an integer value.

Thank you, Salad. I got as far as the Save and Run part. It prompted
me for a TIME parameter. I tried just entering the field as it is in
the table and then it ran but returned no rows. I'll keep working out
this method. It should work this way.

[Salad]

Pea wrote:

Salad <oi*@vinegar.com> wrote in message news:<1B****************@newsread3.news.pas.earthl ink.net>...

You can start off with a query and build off of that one if needed.

Qyery/New/Design. Add your table then close.

Drag UserID, Date, Time (3 times) Time1...Time3, And IDType to the
In the criteria enter
<>"JoeSchmo"
under UserID.

I'll assume the date is a value that stores m,d,y. So enter
<>Year(2003)
under Date

In Time1 to time3 it should look like
Time1 : Time
Time2 : Time
Time3 : Time
in the data field row. In the criteria enter
<#7:00:00# for Time1
Between #7:00# And #8:59:59# for Time2
Between #9:00# And #10:59:59# For Time3

Now make this a Totals Query. From the menu, View/Totals

GroupBy should be assigned to UserID and IDType
Count should be assigned to Time1,2,3
Where should be assigned to the date...unless you want to group on the date.

Save and Run.

Now you can create another query that calls this one and average the
counts or you can use the above query in a report and calc the average
in a textbox's control source. Ex:
=(Time1 + Time2 + Time3) /3

Note I used / 3. You can also use \ 3 too. Here's an example of both
? 100/3
33.33333
? 100 \ 3
33

The \ gives an integer value.

Thank you, Salad. I got as far as the Save and Run part. It prompted
me for a TIME parameter. I tried just entering the field as it is in
the table and then it ran but returned no rows. I'll keep working out
this method. It should work this way.

OK. I'm not sure what your time field is called.

Also. The date field should have an expression with criteria. Ex:
DateYear : Year(date()
with the criteria
<>2003
Don't use the Year(2003) that I initially supplied.

If your field is called Time, then it should be Time. Lets say it is
called TimeOfCall. Then it should be something like
Time1 : TimeOfCall
Time2 : TimeOfCall
Time3 : TimeOfCall
with the criteria listed initially.

I'm sure you are close.