Report Select Query Results

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
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.
Click to expand...

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.

John W. Vinson said:
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.
Click to expand...

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]
Click to expand...
 
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:
Click to expand...

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');


John W. Vinson said:
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:
Click to expand...

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]
Click to expand...
 
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".
Click to expand...

Sounds like a better idea than what I was coming up with - thanks for posting
back!

John W. Vinson [MVP]
 
Back
Top