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 3 1638
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.
Salad <oi*@vinegar.co m> wrote in message news:<1B******* *********@newsr ead3.news.pas.e arthlink.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.
Pea wrote: Salad <oi*@vinegar.co m> wrote in message news:<1B******* *********@newsr ead3.news.pas.e arthlink.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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Frank Maestas |
last post by:
Hello,
I have been trying to figure this out but no luck.
Lets say I have a query that searches for people living in Colorado.
That results in a list on a "results.php" page. Now I want to query
that result and search further for people who use Linux.
I know I can do this from one query, but I would like to create
several checkboxes on my results.php page where I do a further
secondary query. Possible? I guess I want to query a...
|
by: Robert Brown |
last post by:
suppose I have the following table:
CREATE TABLE (int level, color varchar, length int, width int, height
int)
It has the following rows
1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
|
by: sah |
last post by:
I need some help with the following query:
DECLARE @SRV VARCHAR(20), @date smalldatetime
SET @SRV = (select @@servername)
SET @date = '20040901'
select Srv_Name = @SRV, DB_Name = 'DB_NAME', Table_Name =
'Info_Table', Date_of_Records = @date,
count(*) AS 'Actual Total' ,
|
by: Jonathan |
last post by:
Hi all!
For a match schedule I would like to find all possible combinations of
teams playing home and away (without teams playing to themselves of course).
I now the simple version works something like this:
For a (very) simple table containing three rows like this:
row 1: A
|
by: wnstnsmith |
last post by:
Dear all,
Suppose there is a table whose records contain a textfield TXT, and a query
that returns a subset of that table. Is it possible to have that query
return all the TXT-fields gathered together in one string? I really have no
idea as to how to tackle this. For clarity, here's an example of what I
mean:
there are three tables
| |
by: Chris Ochs |
last post by:
It doesn't currently seem possible to switch between different users using
SET SESSION AUTHORIZATION. If I log in as the superuser and switch to
another user that works, but if I then switch to a second user in succession
I get permission denied when I try to select from a table that the user does
have access to. Following is a cut and paste of two sessions showing what I
mean (with certain details masked out).
defender# psql db1-U...
|
by: jim Bob |
last post by:
Hi, I have a query with the criteria of
(!..) Or
!..
When I provide the value from qmylist, a second pop up appears waiting
for the value from lstResults. If I enter nothing and click ok, it works
fine. (Vice versa)
Is there a way to suppress the popup if only 1 of the value is supplied?
There by working truly as an OR?
|
by: hdogg |
last post by:
I am using oracle with php. I am trying to accomplish a left outer join.
Here are the 3 queries.
Query 1, contains all the data on the left the will show up with data on the right.
Query 1 =
((SELECT JOB_CODE AS JOBCODE , PERIOD FROM GSI.PCTCOMP_ACC_VW WHERE ( PCT_DATE = TO_DATE('20070228000000','YYYYMMDDHH24MISS') ) AND ( PCT_COMP_CODE = '02' )))
Query 2:
|
by: DuncanIdaho |
last post by:
Hi
Apologies if this is similar to a (very) recent post.
I was wondering if it is possible to execute an update query that
contains a select statement on an MS access 2000 database.
I have included a detaled example at the bottom of this post
I have the following update query that works as I expect it to on a
MySQL database
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |