show Count of "empty" records

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.
--
KARL DEWEY
Build a little - Test a little


"GillWeb" wrote:

On Jun 15, 2:36 am, "Chris2"
wrote:
"KARL DEWEY" wrote in message

...

Post your table structure - field names and datatype.
How many times mus I ask for the same thing?

I think she doesn't know we're talking about (yet).

Sincerely,

Chris O.

You got it Chris O!
I thought I'd posted just what I was asked to do, following the
instructions from Karl, but obviously not.

I think I'll simply paste my query results into Excel & add the
missing responses that way! It HAS to be easier than all this
confusion!

I'm going to check out the sample suggested & linked by John Spencer &
see if I get any further.

I really appreciate you all for your efforts trying to help me & I'm
sorry that I'm not "getting it" - I know how frustrating it is when
somebody doesn't undertand what you're trying to tell them :-(

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" wrote:


"GillWeb" wrote in message
ps.com...
On Jun 13, 2:08 am, "Chris2"
wrote:
"GillWeb" wrote in message

ups.com...



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: ViewSQL 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.

"KevinT" wrote in message
...

"Chris2" wrote:



big snip


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.

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

"KevinT" wrote in message
...
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" wrote:


"GillWeb" wrote in message
ps.com...
On Jun 13, 2:08 am, "Chris2"
wrote:
"GillWeb" wrote in message

ups.com...



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: ViewSQL 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.

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.

"John Spencer" wrote:

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

"KevinT" wrote in message
...
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" wrote:


"GillWeb" wrote in message
ps.com...
On Jun 13, 2:08 am, "Chris2"
wrote:
"GillWeb" wrote in message

ups.com...



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: ViewSQL 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.