 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / August 2007Report Select Query Results
Hi, I am trying to create an accident report that is grouped by year and severity type and my select query result have fields,[Year],[Severity Type],[Total Accidents] and my report should look like this: Year Severity Type Total Accidents Fatal 1 Injury 0 Property Damage 1 Non-Reportable 0 Subtotal 2 My problem is that the select query result do not have all 4 severity types. How can I set up the report to show all 4 severity types even there is no record for it. and make the null value '0'? Florence N. >Hi, I am trying to create an accident report that is grouped by year and >severity type and my select query result have fields,[Year],[Severity [quoted text clipped - 14 lines] >Florence N. > Well, you don't give any indication of the structure of your tables, so this is a pure guess. Assuming you have a table of SeverityTypes (perhaps a lookup field table??), join it to your totals query. Select the Join Line and choose option 2 - "Show all records in SeverityTypes and matching records in Accidents". Group By the SeverityTypes field. If that doesn't help please post the structure of your tables, and perhaps the SQL view of your query. John W. Vinson [MVP] Hi, John: Thanks for replying so quickly. I do have a severity type table and tried to join it with my total query. I did get all 4 severity types under [Severiy Type] field but the rest of the fields are empty. That is because there is no record for the severity types. When I do the report, I ended up with a empty year with one severity type and no accidents in the first record. Here is my SQL code: SELECT [Study Locations].PIN, [Study Locations].TITLE, [Study Locations].LOCATION, [Study Locations].RT_NUM, [Study Locations].TOWN, [Study Locations].COUNTY, Format$([NYDOT Accidents].Date,'yyyy') AS [Date By Year], [NYDOT Accidents].Severity, Count(*) AS [Total Accidents] FROM ([Study Locations] RIGHT JOIN [NYDOT Accidents] ON [Study Locations].ID = [NYDOT Accidents].[Location ID]) RIGHT JOIN [Severity Code] ON [NYDOT Accidents].Severity = [Severity Code].Severity GROUP BY [Study Locations].PIN, [Study Locations].TITLE, [Study Locations].LOCATION, [Study Locations].RT_NUM, [Study Locations].TOWN, [Study Locations].COUNTY, Format$([NYDOT Accidents].Date,'yyyy'), [NYDOT Accidents].Severity, Year([NYDOT Accidents].Date) ORDER BY Format$([NYDOT Accidents].Date,'yyyy'); My other problem is that I need to show all severity types for all years. My report should look like this: 1999 Severity type Total Accidents Fatal 1 Injury 0 Property Damage 1 Non-Reportable 0 Subtotal 2 2000 Severity Type Total Accidents Fatal 0 Injury 1 Property Damage 0 Non-Reportable 0 and so on.... But now my report look like this: 1999 Severity Type Total Accident Fatal 1 Porperty Damage 1 subtotal 2000 Severity Type Total Accident Injury 1 subtotal Thanks for your help. Florence N. > >Hi, I am trying to create an accident report that is grouped by year and > >severity type and my select query result have fields,[Year],[Severity [quoted text clipped - 25 lines] > > John W. Vinson [MVP] >Hi, John: > [quoted text clipped - 4 lines] >with a empty year with one severity type and no accidents in the first >record. Here is my SQL code: Well... if there is no record in the table, what year should that "no record" be assigned!? I'm not sure I understand what you expect. You may need to use the NZ() function (Null To Zero) to convert some of the Null values from the outer-joined table to 0 or to some other appropriate value. I'll keep this thread live and try to find some time to look at the query later today. John W. Vinson [MVP] Hi, John: I have managed to twick the report so it looks the way my client wants. I created a crosstab query where I used the year as the row heading and the severity type as the column heading then I typed in all 4 severity types for the column headings and use the NZ() function to make the empty cells go "0". On the report I just set it up so I can report the total accidents by year by severity type vertically. It is probably not the most elegant way of doing this but it works. Thanks for taking time to answer me back. Here is my SQL codes: TRANSFORM NZ(Count([NYDOT Accidents by Location].ID),0) AS CountOfID SELECT Format$([NYDOT Accidents by Location].Date,'yyyy') AS [Year], [NYDOT Accidents by Location].RT_NUM, [NYDOT Accidents by Location].TOWN, [NYDOT Accidents by Location].COUNTY, Count([NYDOT Accidents by Location].ID) AS [Total Of ID] FROM [NYDOT Accidents by Location] GROUP BY Format$([NYDOT Accidents by Location].Date,'yyyy'), [NYDOT Accidents by Location].RT_NUM, [NYDOT Accidents by Location].TOWN, [NYDOT Accidents by Location].COUNTY PIVOT [NYDOT Accidents by Location].Severity In ('Fatal','Injury','Property Damage','Non-Reportable'); > >Hi, John: > > [quoted text clipped - 16 lines] > > John W. Vinson [MVP] > I have managed to twick the report so it looks the way my client wants. I >created a crosstab query where I used the year as the row heading and the >severity type as the column heading then I typed in all 4 severity types for >the column headings and use the NZ() function to make the empty cells go "0". Sounds like a better idea than what I was coming up with - thanks for posting back! John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.