Union Query - Removing Duplicates from Results or a Better way to do this.

[Huddle]

The below Union Query comes from two basically identical queries. One lists a group of buildings with a count of inspection done within a certain range. The other lists a group of building that did not have inspections done during that range.

My purpose is this. I need to do a report on all the buildings that are due inspections for the quarter. It was easy enough to see the status on the ones that had at least one inspection done during the quarter but for those who did not have an inspection done the initial query did not pull up the building. I ended up making 5 total queries (including the union) to get this to work and of course I have duplicates due to the union of the final two queries. There has to be a simlar way to do this.

Any suggestions or help would be appreciated.



SELECT Q_PI_Qrtly_Insp_Ct_Range.District, Q_PI_Qrtly_Insp_Ct_Range.Bldg_Number, Q_PI_Qrtly_Insp_Ct_Range.Bldg_Name, Q_PI_Qrtly_Insp_Ct_Range.Address, Q_PI_Qrtly_Insp_Ct_Range.City, Q_PI_Qrtly_Insp_Ct_Range.State, Q_PI_Qrtly_Insp_Ct_Range.FSL, " " AS InspDateBlank, Sum(Q_PI_Qrtly_Insp_Ct_Range.InspDateCt) AS SumOfInspDateCt, Q_PI_Qrtly_Insp_Ct_Range.Due
FROM Q_PI_Qrtly_Insp_Ct_Range
GROUP BY Q_PI_Qrtly_Insp_Ct_Range.District, Q_PI_Qrtly_Insp_Ct_Range.Bldg_Number, Q_PI_Qrtly_Insp_Ct_Range.Bldg_Name, Q_PI_Qrtly_Insp_Ct_Range.Address, Q_PI_Qrtly_Insp_Ct_Range.City, Q_PI_Qrtly_Insp_Ct_Range.State, Q_PI_Qrtly_Insp_Ct_Range.FSL, Q_PI_Qrtly_Insp_Ct_Range.Due;
UNION SELECT Q_PI_Qrtly_Insp_Ct_Range_Null.District, Q_PI_Qrtly_Insp_Ct_Range_Null.Bldg_Number, Q_PI_Qrtly_Insp_Ct_Range_Null.Bldg_Name, Q_PI_Qrtly_Insp_Ct_Range_Null.Address, Q_PI_Qrtly_Insp_Ct_Range_Null.City, Q_PI_Qrtly_Insp_Ct_Range_Null.State, Q_PI_Qrtly_Insp_Ct_Range_Null.FSL, " " AS InspDateSpace, " " AS CountSpace, Q_PI_Qrtly_Insp_Ct_Range_Null.Due
FROM Q_PI_Qrtly_Insp_Ct_Range_Null
GROUP BY Q_PI_Qrtly_Insp_Ct_Range_Null.District, Q_PI_Qrtly_Insp_Ct_Range_Null.Bldg_Number, Q_PI_Qrtly_Insp_Ct_Range_Null.Bldg_Name, Q_PI_Qrtly_Insp_Ct_Range_Null.Address, Q_PI_Qrtly_Insp_Ct_Range_Null.City, Q_PI_Qrtly_Insp_Ct_Range_Null.State, Q_PI_Qrtly_Insp_Ct_Range_Null.FSL, Q_PI_Qrtly_Insp_Ct_Range_Null.Due;

[June7]

UNION does not allow duplicate records unless use the ALL parameter. Some value is different which causes records to be unique.

Could you join the query that counts the inspections to the table of buildings - jointype 'Include all records from Buildings ...". The records where the count field is null did not have inspections in that period

[Huddle]

The buildings that have had an inspection during the range show up twice as shows below. I couldn't tie it to the building table because some buildings don't have posts so I have to query those out.

District	Bldg_Number	FSL		SumOfInspDateCt	Due
D2	WY0030	4			50
D2	WY0030	4		26	50
D2	WY1150	1			2
D2	WY1150	1		1	2
D2	WY1219	2			2
D2	WY1228	3			25
D2	WY1228	3		3	25
D2	WY1235	1			2
D2	WY1307	1			2
D2	WY1324	1			2
D2	WY1336	1			2

[June7]

The buildings show twice but the records are different because of the SumOfInspDateCt value. If you don't want the building duplicated why use the UNION?

Don't understand why you can't join the count query to the table of all buildings. With the jointype I described, all buildings will show. Isn't that what you want - to see data in report on all buildings?

[Huddle]

There's two problems with joining the count query with the building table. 1) not all buildings have posts so it will list buildings that don't apply, 2) it count inspections outside the date range if I change the relationships to only list buildings with posts.

I have it where it works with the union if I can figure out how to do a count within the union. where would I add "Count(Tbl_PI_Post_Insp.Insp_Date) AS CountOfInsp_Date" into the SQL to accomplish this? Or can it be done?

SELECT Tbl_Building_Information.District, Tbl_Building_Information.Bldg_Number, Tbl_Building_Information.Bldg_Name, Tbl_Building_Information.Address, Tbl_Building_Information.City, Tbl_Building_Information.State, Tbl_Building_Information.FSL, Tbl_PI_Post_Insp.Insp_Date, IIf([FSL]=4,DateDiff("ww",[Forms]![Fm_Date_Parameter]![StartDate],[Forms]![Fm_Date_Parameter]![EndDate])*2,IIf([FSL]=3,DateDiff("ww",[Forms]![Fm_Date_Parameter]![StartDate],[Forms]![Fm_Date_Parameter]![EndDate]),2)) AS Due
FROM (Tbl_Building_Information RIGHT JOIN Tbl_PI_Post ON Tbl_Building_Information.Bldg_Number = Tbl_PI_Post.PI_Bldg_Number) RIGHT JOIN Tbl_PI_Post_Insp ON Tbl_PI_Post.PostID = Tbl_PI_Post_Insp.PostID_Number
WHERE (((Tbl_PI_Post_Insp.Insp_Date) Between [Forms]![Fm_Date_Parameter]![StartDate] And [Forms]![Fm_Date_Parameter]![EndDate]));
UNION SELECT Tbl_Building_Information.District, Tbl_PI_Post.PI_Bldg_Number, Tbl_Building_Information.Bldg_Name, Tbl_Building_Information.Address, Tbl_Building_Information.City, Tbl_Building_Information.State, Tbl_Building_Information.FSL, " " AS InspBlank, IIf([FSL]=4,DateDiff("ww",[Forms]![Fm_Date_Parameter]![StartDate],[Forms]![Fm_Date_Parameter]![EndDate])*2,IIf([FSL]=3,DateDiff("ww",[Forms]![Fm_Date_Parameter]![StartDate],[Forms]![Fm_Date_Parameter]![EndDate]),2)) AS Due
FROM (Tbl_Building_Information INNER JOIN Tbl_PI_Post ON Tbl_Building_Information.Bldg_Number = Tbl_PI_Post.PI_Bldg_Number) LEFT JOIN Tbl_PI_Post_Insp ON Tbl_PI_Post.PostID = Tbl_PI_Post_Insp.PostID_Number
GROUP BY Tbl_Building_Information.District, Tbl_PI_Post.PI_Bldg_Number, Tbl_Building_Information.Bldg_Name, Tbl_Building_Information.Address, Tbl_Building_Information.City, Tbl_Building_Information.State, Tbl_Building_Information.FSL, IIf([FSL]=4,DateDiff("ww",[Forms]![Fm_Date_Parameter]![StartDate],[Forms]![Fm_Date_Parameter]![EndDate])*2,IIf([FSL]=3,DateDiff("ww",[Forms]![Fm_Date_Parameter]![StartDate],[Forms]![Fm_Date_Parameter]![EndDate]),2));

[June7]

I would have to work with data to analyse. If you want to provide, follow instructions at bottom of my post.

[Huddle]

Actually playing around with it that isn't going to work either. Let me think it through some more and I'll get back with you when I get to a point I can be clear in what I need and the direction I'm going.