show Count of "empty" records

[Guest]

What I have been asking for is your table structure.
Access stores data in tables. It is extracted for computation and display
using queries. It is displayed using forms and reports.
What ever table that your data is in you would open it in design view.
There will be a list of the fields in the first column and the datatype in
the second column. That is the information I have been asking to see.

 

[Guest]

Hi all,

I am after what GillWeb is after also.
The SQL for the query that does a count of accidents a person has over a 12
month period is pasted below. It also shows start and end date. It works fine
as it is.
However, I want it to show null or zero values as well as I use this query
to fill a field in another query which requires these values. If you could
replace null or zero with the word "nil" it would be great also.

TIA,
Kevin

SELECT Accidents.[Staff No], Count(Accidents.[Staff No]) AS [CountOfStaff
No], DateAdd("m",-12,Date()+1) AS [Start Date], Date() AS [End Date]
FROM Accidents
WHERE (((Accidents.Date)>=DateAdd("m",-12,Date()+1)))
GROUP BY Accidents.[Staff No], DateAdd("m",-12,Date()+1), Date();

 

[Chris2]

Sincerely,

Chris O.

Hi all,

I am after what GillWeb is after also.
The SQL for the query that does a count of accidents a person has over a 12
month period is pasted below. It also shows start and end date. It works fine
as it is.
However, I want it to show null or zero values as well as I use this query
to fill a field in another query which requires these values. If you could
replace null or zero with the word "nil" it would be great also.

TIA,
Kevin

SELECT Accidents.[Staff No], Count(Accidents.[Staff No]) AS [CountOfStaff
No], DateAdd("m",-12,Date()+1) AS [Start Date], Date() AS [End Date]
FROM Accidents
WHERE (((Accidents.Date)>=DateAdd("m",-12,Date()+1)))
GROUP BY Accidents.[Staff No], DateAdd("m",-12,Date()+1), Date();

KevinT,

The following is untested (I am not even sure I am adding the function
to the correct column . . . I guessed, basically).


SELECT Accidents.[Staff No]
,Nz(Count(Accidents.[Staff No]), 0) AS [CountOfStaffNo]
,DateAdd("m",-12,Date()+1) AS [Start Date], Date() AS [End
Date]
FROM Accidents
WHERE (((Accidents.Date) >= DateAdd("m",-12,Date()+1)))
GROUP BY Accidents.[Staff No]
,DateAdd("m",-12,Date()+1), Date();

If your COUNT() is NULL for a particular group, that *should* show 0.



Sincerely,

Chris O.

 

[John Spencer]

If I understand correctly, you want to show all Staff and the number of
accidents each staff member has had during a specified period. I am
guessing that the Accidents table has one record for each accident and no
record if the staff member had no accident. I will also guess that you have
a Staff table.

Query one saved as qAccidentCount
SELECT Accidents.[Staff No]
, Count(Accidents.[Staff No]) AS [AccidentCount]
FROM Accidents
WHERE (((Accidents.Date)>=DateAdd("m",-12,Date()+1)))
GROUP BY Accidents.[Staff No], DateAdd("m",-12,Date()+1), Date();

Query two uses the Staff Table and qAccidentCount to give you the results I
think you are looking for.
SELECT Staff.[Staff No]
, qAccidentCount.AccidentCount
, DateAdd("m",-12,Date()+1) AS [Start Date]
, Date() AS [End Date]
FROM Staff LEFT JOIN qAccidentCount
On Staff.[Staff No] = qAccidentCount.[Staff No]

By the way if you avoided spaces and special characters in your field and
table names, you could do the above in one query using a sub-query

Another way (slower) to achieve the above result would be to use a
coordinated sub-query.
SELECT Staff.[Staff No]
, (SELECT Count(Accidents.[Staff No])
FROM Accidents
WHERE Accidents.Date>=DateAdd("m",-12,Date()+1)
AND Accidents.[Staff No] = Staff.[Staff No]) as AccidentCount
, DateAdd("m",-12,Date()+1) AS [Start Date]
, Date() AS [End Date]
FROM Staff


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi all,

I am after what GillWeb is after also.
The SQL for the query that does a count of accidents a person has over a
12
month period is pasted below. It also shows start and end date. It works
fine
as it is.
However, I want it to show null or zero values as well as I use this query
to fill a field in another query which requires these values. If you could
replace null or zero with the word "nil" it would be great also.

TIA,
Kevin

SELECT Accidents.[Staff No], Count(Accidents.[Staff No]) AS [CountOfStaff
No], DateAdd("m",-12,Date()+1) AS [Start Date], Date() AS [End Date]
FROM Accidents
WHERE (((Accidents.Date)>=DateAdd("m",-12,Date()+1)))
GROUP BY Accidents.[Staff No], DateAdd("m",-12,Date()+1), Date();

GillWeb,

Aircode is just like an airguitar, it sort of doesn't exist. In this
case, it was something that wasn't tested. It was something I threw
together and posted. "Aircode" is there to let you know that.

YourColumn and YourTable are generic terms I used that you are meant
to replace with your column's name and your table's name.

YT1 is a table alias.

Nz() is a function.

Nz(<expression>, <results if express IS NULL>)

<expression> = a column name
<results if expression IS NULL> = What the function returns if the
<expression> evaluates to NULL.

If the expression does not evaluate to NULL.

YourTable:
YourColumn:

1
2
NULL
3

Nz(YourColumn, 1) returns:

1
2
1 <NULL is replaced by 1>
3

Nz(YourColumn, 9999) returns:

1
2
9999 <NULL is replaced by 9999>
3

COUNT() counts the number of rows.

With Nz(YourColumn, 1) feeding the number 1 to the COUNT() function
surrounding it, and with the WHERE clause restricting the rows to ones
where YourColumn values are NULL, you are counting the NULL rows.
i.e. you are counting rows where no answer was put.

From your initial description, that is what I thought you wanted.


Create a copy of your database. Open the copy.

Create a new query.

Switch to SQL View (Menu Bar: View>SQL View)

Paste in my SQL code.


SELECT COUNT(Nz(YT1.YourColumn, 1))
FROM YourTable AS YT1
WHERE YT1.YourColumn IS NULL

Change YourTable to your table's name.

Change YT1 to an appropriate 1-3 letter/number abbreviation for your
table in all three locations.

Change YourColumn to the column name in your table where your answers
are stored (and where NULLs are stored for unanswered questions.)

That *should* work.


Sincerely,

Chris O.

 

[Guest]

Thanks John & Chris2.

The code you sent worked perfectly John. I just had to add the following
Field to the 2nd query. It then gave me "Nil" or a number.
AccoCount: IIf([AccidentCount] Is Null,"Nil",[AccidentCount])
Thanks heaps.

If I understand correctly, you want to show all Staff and the number of
accidents each staff member has had during a specified period. I am
guessing that the Accidents table has one record for each accident and no
record if the staff member had no accident. I will also guess that you have
a Staff table.

Query one saved as qAccidentCount
SELECT Accidents.[Staff No]
, Count(Accidents.[Staff No]) AS [AccidentCount]
FROM Accidents
WHERE (((Accidents.Date)>=DateAdd("m",-12,Date()+1)))
GROUP BY Accidents.[Staff No], DateAdd("m",-12,Date()+1), Date();

Query two uses the Staff Table and qAccidentCount to give you the results I
think you are looking for.
SELECT Staff.[Staff No]
, qAccidentCount.AccidentCount
, DateAdd("m",-12,Date()+1) AS [Start Date]
, Date() AS [End Date]
FROM Staff LEFT JOIN qAccidentCount
On Staff.[Staff No] = qAccidentCount.[Staff No]

By the way if you avoided spaces and special characters in your field and
table names, you could do the above in one query using a sub-query

Another way (slower) to achieve the above result would be to use a
coordinated sub-query.
SELECT Staff.[Staff No]
, (SELECT Count(Accidents.[Staff No])
FROM Accidents
WHERE Accidents.Date>=DateAdd("m",-12,Date()+1)
AND Accidents.[Staff No] = Staff.[Staff No]) as AccidentCount
, DateAdd("m",-12,Date()+1) AS [Start Date]
, Date() AS [End Date]
FROM Staff


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi all,

I am after what GillWeb is after also.
The SQL for the query that does a count of accidents a person has over a
12
month period is pasted below. It also shows start and end date. It works
fine
as it is.
However, I want it to show null or zero values as well as I use this query
to fill a field in another query which requires these values. If you could
replace null or zero with the word "nil" it would be great also.

TIA,
Kevin

SELECT Accidents.[Staff No], Count(Accidents.[Staff No]) AS [CountOfStaff
No], DateAdd("m",-12,Date()+1) AS [Start Date], Date() AS [End Date]
FROM Accidents
WHERE (((Accidents.Date)>=DateAdd("m",-12,Date()+1)))
GROUP BY Accidents.[Staff No], DateAdd("m",-12,Date()+1), Date();

On Jun 13, 2:08 am, "Chris2"




GillWeb,

Aircode to follow:

SELECT COUNT(Nz(YT1.YourColumn, 1))
FROM YourTable AS YT1
WHERE YT1.YourColumn IS NULL

Sincerely,

Chris O.- Hide quoted text -

- Show quoted text -

Thanks Chris - that's wonderful - but right over my (female) head. I
don't know the term "Aircode" - I'm just a simple soul...
The help I need has to be at the "idiot's guide.." level I'm
afraid.....


GillWeb,

Aircode is just like an airguitar, it sort of doesn't exist. In this
case, it was something that wasn't tested. It was something I threw
together and posted. "Aircode" is there to let you know that.

YourColumn and YourTable are generic terms I used that you are meant
to replace with your column's name and your table's name.

YT1 is a table alias.

Nz() is a function.

Nz(<expression>, <results if express IS NULL>)

<expression> = a column name
<results if expression IS NULL> = What the function returns if the
<expression> evaluates to NULL.

If the expression does not evaluate to NULL.

YourTable:
YourColumn:

1
2
NULL
3

Nz(YourColumn, 1) returns:

1
2
1 <NULL is replaced by 1>
3

Nz(YourColumn, 9999) returns:

1
2
9999 <NULL is replaced by 9999>
3

COUNT() counts the number of rows.

With Nz(YourColumn, 1) feeding the number 1 to the COUNT() function
surrounding it, and with the WHERE clause restricting the rows to ones
where YourColumn values are NULL, you are counting the NULL rows.
i.e. you are counting rows where no answer was put.

From your initial description, that is what I thought you wanted.


Create a copy of your database. Open the copy.

Create a new query.

Switch to SQL View (Menu Bar: View>SQL View)

Paste in my SQL code.


SELECT COUNT(Nz(YT1.YourColumn, 1))
FROM YourTable AS YT1
WHERE YT1.YourColumn IS NULL

Change YourTable to your table's name.

Change YT1 to an appropriate 1-3 letter/number abbreviation for your
table in all three locations.

Change YourColumn to the column name in your table where your answers
are stored (and where NULLs are stored for unanswered questions.)

That *should* work.


Sincerely,

Chris O.