Re: SQL WHERE help

From: Kris L. (KrisL_at_discussions.microsoft.com)
Date: 07/09/04 

Date: Fri, 9 Jul 2004 12:35:02 -0700

As you might be able to tell I'm by no means an expert in Access.

Thanks for the response, from what you wrote entering "Null" in place of "999" should work perfectly.

Just one more thing... where do you go to Allow Nulls?
I can't seem to find it in the column properties.

"Tom Ellison" wrote:

> 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!
>
>

Relevant Pages

  • Re: SQL WHERE help
    ... want some other rule for averaging missing information. ... have missing information allow nulls. ... Tom Ellison ... >>From this table I made a simple query that shows averages for each column. ...
    (microsoft.public.access.queries)
  • Re: All hail Neo!
    ... Bags and nulls are quite apparently not but from where I am now the null at least seems the lesser of evils viz my post in the "beautiful mind" thread. ... With null or without logical identity, ... If the practitioner never considers where the pitfalls lie, he will only discover them from the bottom of the pit. ... They have correctly identified that missing information as of yet has no theory to address it. ...
    (comp.databases.theory)
  • Re: Date, Darwen, Pascal and the alternative to Nulls in the RM
    ... books seem to have stronger and stronger proscriptions against nulls, ... and fewer and fewer ideas about how to handle missing information. ... The only way to avoid nulls is to avoid change management. ... of specific data integrity exceptions, ...
    (comp.databases.theory)
  • Re: Figuring out what field is causing key violation on append
    ... Nz converts nulls to whatever you put as the second argument. ... Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia ... First try the query with just one field in the append query. ...
    (microsoft.public.access.queries)
  • Re: Examples of SQL anomalies?
    ... If you don't spend enough time on design, ... NULLs are only one way to deal with missing information. ... I tend to believe that interpretation of the meaning of data should be ...
    (comp.databases.theory)