Re: SQL WHERE help
From: Tom Ellison (tellison_at_jcdoyle.com)
Date: 07/09/04
- Next message: Wayne Morgan: "Re: And/Or"
- Previous message: Alex: "Key Violation Message"
- In reply to: Kris L.: "SQL WHERE help"
- Next in thread: Kris L.: "Re: SQL WHERE help"
- Reply: Kris L.: "Re: SQL WHERE help"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 09 Jul 2004 13:57:48 -0500
Dear Kris:
I'm not sure I understand what you want this to do. You say you
entered 999 for no data available so the average would work. The
average would work if you had a NULL value there. It would just
ignore that row for purposes of average.
Let's say you have two rows to average. The first row has 2 for a
value, and the other row has no data available. The average is then
2, ignoring the second row. If you wanted to use 0 in the average for
the row with missing values, you would say:
SUM(Nz(METP_Total_Calls, 0))
This would average using 0 for that row. For the above example, the
average would then be 1
(2 + 0) / 2 = 1
Which of the above do you want for your average? 1 or 2? Or do you
want some other rule for averaging missing information.
I would recommend you should probably make sure the columns that may
have missing information allow nulls. Then update them changing the
999 values to nulls. You should be able to query on this perfectly
well, but how depends on the rules you want to use. But 999 is not
what you want to average in, is it?
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Fri, 9 Jul 2004 11:13:02 -0700, "Kris L."
<KrisL@discussions.microsoft.com> wrote:
>I have a table that has several columns of information about employee performance at the company I work at.
>>From this table I made a simple query that shows averages for each column.
>The problem is that one of the columns is not required for entry and, in most cases, the employee is missing the information for that column from their first couple of weeks.
>Because I had to enter a number to get the average to work, I used '999' as the value for no data available.
>Obviously at this point the data is skewed due to the average factoring i the '999'
>However, I did fix this by using a WHERE command in the SQL View of the query.
>
>Here is the syntax of the query:
>
>SELECT DISTINCTROW Format(Avg(QRY_Metrics_Performance.METP_Total_Calls),"0.00") AS [Avg Of METP_Total_Calls], Format(Avg(QRY_Metrics_Performance.METP_Aprod), "0.00") AS [Avg Of METP_Aprod], Format(Avg(QRY_Metrics_Performance.METP_Percent_of_Tickets_Closed), "0.00") AS [Avg Of METP_Percent_of_Tickets_Closed], Format(Avg(QRY_Metrics_Performance.METP_Aprod_Ranking),"0.00") AS [Avg Of METP_Aprod_Ranking], Format(Avg(QRY_Metrics_Performance.METP_Total_Productivity_Ranking),"0.00") AS [Avg Of METP_Total_Productivity_Ranking], Format(Avg(QRY_Metrics_Performance.METP_Valid_Quality_Codes),"0.00") AS [Avg Of METP_Valid_Quality_Codes], Format(Avg(QRY_Metrics_Performance.METP_Calls_Per_QC),"0.00") AS [Avg Of METP_Calls_Per_QC]
>FROM QRY_Metrics_Performance WHERE QRY_Metrics_Performance.METP_Calls_Per_QC <> '999';
>
>The WHERE statement took care of the average for the last field, Avg Of METP_Calls_Per_QC, but created a new one. Now all the fiends will only average items WHERE QRY_Metrics_Performance.METP_Calls_Per_QC <> '999', but these columns have applicable data in most cases for all of their entries and I want the average from all the data.
>Is there any way to isolate the WHERE statement to Avg of METP_Calls_Per_QC besides making seperate queries?
>
>Thx so much for your time!
- Next message: Wayne Morgan: "Re: And/Or"
- Previous message: Alex: "Key Violation Message"
- In reply to: Kris L.: "SQL WHERE help"
- Next in thread: Kris L.: "Re: SQL WHERE help"
- Reply: Kris L.: "Re: SQL WHERE help"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
