If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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 :-( |
#22
|
|||
|
|||
show Count of "empty" records
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. |
#23
|
|||
|
|||
show Count of "empty" records
"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. |
#24
|
|||
|
|||
show Count of "empty" records
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. |
#25
|
|||
|
|||
show Count of "empty" records
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. |
Thread Tools | |
Display Modes | |
|
|