Prevent Repeating Parent Record in Report

[Rich Hartnett]

I have a set based on two tables: "Incidents" and "Apparatus."

The Incidents table (parent) lists fire department calls (incident types), with only one record per incident.
The Apparatus table (child) lists the vehicles that respond to each incident. Some incidents will have many vehicles which are linked to the Incidents table with a linkingkey.

On a report, I want to show tallies of the total number of Incidents (by incident type) for a given month/year, and the total number of vehicles (by vehicle type, i.e Ambulance, etc.)

So for example, I have a sub-report called "AUTO ACCIDENTS." There are only 4 auto accidents in the Incidents table, but because on two of those accidents two different Ambulances responded, the total number of AUTO ACCIDENTS is 6. (Two are repeated.)

How can I prevent the parent record from being repeated if there are multiple child records?

(I tried using the Apparatus table as the parent and the Incidents table as the child, but the results were the same.)

[TheSmitchell]

Re: Prevent Repeating Parent Record in Report

So, in MySQL I would use "GROUP BY" to prevent duplicates from showing up. Maybe something like GROUP BY incident.ID ?

Example:
For whatever reason, you've joined on a child table but don't want to show any of the child records. Even though you don't include any columns from the child table, you still get duplicates because of the join:

query:
SELECT a.ID, a.DateAdded, a.Addr_Id, a.Prim_Id
FROM company a
	 LEFT OUTER JOIN members b
		 ON  a.ID = b.ID 
ORDER BY a.ID DESC

output:
ID      DateAdded    Addr_Id  Prim_Id
[HIGHLIGHT]141	2015-01-04	51	     [/HIGHLIGHT]
140	2014-12-22		
[HIGHLIGHT]139	2013-01-30	48	130  
139	2013-01-30	48	130  
139	2013-01-30	48	130  [/HIGHLIGHT]
138	2013-01-28		130
138	2013-01-28		130
[HIGHLIGHT]137	2013-01-28	26	     [/HIGHLIGHT]
136	2013-01-28	49	
[HIGHLIGHT]135	2013-01-28	46	126  [/HIGHLIGHT]

But with the GROUP BY, the duplicates are eliminated:

query:
SELECT a.ID, a.DateAdded, a.Addr_Id, a.Prim_Id
FROM company a
	 LEFT OUTER JOIN members b
		 ON  a.ID = b.ID 
GROUP BY a.ID
ORDER BY a.ID DESC

output:
ID      DateAdded    Addr_Id  Prim_Id
[HIGHLIGHT]141	2015-01-04	51	     [/HIGHLIGHT]
140	2014-12-22		
[HIGHLIGHT]139	2013-01-30	48	130  [/HIGHLIGHT]
138	2013-01-28		130
[HIGHLIGHT]137	2013-01-28	26	     [/HIGHLIGHT]
136	2013-01-28	49	
[HIGHLIGHT]135	2013-01-28	46	126  [/HIGHLIGHT]

[Rich Hartnett]

Re: Prevent Repeating Parent Record in Report

I'm not using SQL, but the "group by" Incident Number is certainly a good idea.

I'll try it and report back.