About null...

[Joe]

I have the following query which successfully retrieves data within the dates
requested. However, some data may still be awaiting entry leaving some
fields such as "Number_Appts_Attend" empty. How can I use Is Null or Is Not
Null in my query to exclude incomplete data?

SELECT Count(Tbl_Appointments.PMKEYS) AS CountOfPMKEYS,
Avg(Tbl_Appointments.Number_Appts_Author) AS AvgOfNumber_Appts_Author,
Avg(Tbl_Appointments.Number_Appts_Attend) AS AvgOfNumber_Appts_Attend,
Tbl_Appointments.Outsource_Date
FROM Tbl_Appointments
GROUP BY Tbl_Appointments.Outsource_Date
HAVING (((Avg(Tbl_Appointments.Number_Appts_Attend))>0) AND
((Tbl_Appointments.Outsource_Date) Between [Enter Start Date:] And [Enter End
Date]));

Any help is much appreciated.

 

[Allen Browne]

The fields where you already have criteria (Number_Appts_Attend and
Outsource_Date) are already excluding nulls.

For other fields, just type:
Is Not Null
into the Criteria row in the design view, and change the Total row from
Group By to Where under those fields.

 

[John Spencer]

I would rewrite the query as follows. It is more efficient to use a
WHERE clause than a HAVING clause. You use a having clause when you
need to filter by the aggregated (sum, average, count) results; you use
WHERE when you need to eliminate records before aggregating the data.

SELECT Count(Tbl_Appointments.PMKEYS) AS CountOfPMKEYS,
Avg(Tbl_Appointments.Number_Appts_Author) AS AvgOfNumber_Appts_Author,
Avg(Tbl_Appointments.Number_Appts_Attend) AS AvgOfNumber_Appts_Attend,
Tbl_Appointments.Outsource_Date
FROM Tbl_Appointments
WHERE Tbl_Appointments.Number_Appts_Attend is Not Null
AND Tbl_Appointments.Outsource_Date Between [Enter Start Date:] And
[Enter End Date]
GROUP BY Tbl_Appointments.Outsource_Date
HAVING Avg(Tbl_Appointments.Number_Appts_Attend)>0



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Joe]

Many thanks Allen, John for your assistance. You have helped heaps!

I would rewrite the query as follows. It is more efficient to use a
WHERE clause than a HAVING clause. You use a having clause when you
need to filter by the aggregated (sum, average, count) results; you use
WHERE when you need to eliminate records before aggregating the data.

SELECT Count(Tbl_Appointments.PMKEYS) AS CountOfPMKEYS,
Avg(Tbl_Appointments.Number_Appts_Author) AS AvgOfNumber_Appts_Author,
Avg(Tbl_Appointments.Number_Appts_Attend) AS AvgOfNumber_Appts_Attend,
Tbl_Appointments.Outsource_Date
FROM Tbl_Appointments
WHERE Tbl_Appointments.Number_Appts_Attend is Not Null
AND Tbl_Appointments.Outsource_Date Between [Enter Start Date:] And
[Enter End Date]
GROUP BY Tbl_Appointments.Outsource_Date
HAVING Avg(Tbl_Appointments.Number_Appts_Attend)>0



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have the following query which successfully retrieves data within the dates
requested. However, some data may still be awaiting entry leaving some
fields such as "Number_Appts_Attend" empty. How can I use Is Null or Is Not
Null in my query to exclude incomplete data?

SELECT Count(Tbl_Appointments.PMKEYS) AS CountOfPMKEYS,
Avg(Tbl_Appointments.Number_Appts_Author) AS AvgOfNumber_Appts_Author,
Avg(Tbl_Appointments.Number_Appts_Attend) AS AvgOfNumber_Appts_Attend,
Tbl_Appointments.Outsource_Date
FROM Tbl_Appointments
GROUP BY Tbl_Appointments.Outsource_Date
HAVING (((Avg(Tbl_Appointments.Number_Appts_Attend))>0) AND
((Tbl_Appointments.Outsource_Date) Between [Enter Start Date:] And [Enter End
Date]));

Any help is much appreciated.