how do I put these 2 queries into 1 ??

  • Thread starter Thread starter dannie
  • Start date Start date
D

dannie

What I would like is 1 query that displays the columns Area , CountofArea,
CountofArea2. I tried using the design query but it put a LEFT JOIN on the
whole first statement which gave me some crazy numbers. The first query is:

SELECT Area.Area, Count(InspectionsDI.Area) AS CountOfArea
FROM Area LEFT JOIN InspectionsDI ON Area.Area = InspectionsDI.Area
GROUP BY Area.Area, Area.ID
HAVING (((Area.Area)<>"" And (Area.Area)<>"ALL"))
ORDER BY Area.ID;

and then second query is:

SELECT Area.Area, Count(Inspections.Area) AS CountOfArea2
FROM Area LEFT JOIN Inspections ON Area.Area = Inspections.Area
GROUP BY Area.Area, Area.ID
HAVING (((Area.Area)<>"" And (Area.Area)<>"ALL"))
ORDER BY Area.ID;

thanks
 
Try this --
SELECT Area.Area, Count(InspectionsDI.Area) AS CountOfArea,
Count(Inspections.Area) AS CountOfArea2
FROM (Area LEFT JOIN InspectionsDI ON Area.Area = InspectionsDI.Area) LEFT
JOIN Inspections ON Area.Area = Inspections.Area
GROUP BY Area.Area, Area.ID
HAVING (((Area.Area)<>"" And (Area.Area)<>"ALL"))
ORDER BY Area.ID;
 
When I try that it takes the value I get when the tables are separated and
multiplies them together and displays the quantity in both columns instead of
the individual counts.
 
Back
Top