how do I put these 2 queries into 1 ??

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;


--
Build a little, test a little.


"dannie" wrote:

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

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.

"KARL DEWEY" wrote:

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;


--
Build a little, test a little.


"dannie" wrote:

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

UNTESTED but should work --
qryAreaCounts --
SELECT Area.Area, Count(InspectionsDI.Area) AS CountOfArea, 0 AS CountOfArea2
FROM Area LEFT JOIN InspectionsDI ON Area.Area = InspectionsDI.Area
GROUP BY Area.Area
HAVING (((Area.Area)"" And (Area.Area)"ALL"))
ORDER BY Area.ID
UNION ALL SELECT Area.Area, 0 AS CountOfArea, Count(Inspections.Area) AS
CountOfArea2
FROM Area LEFT JOIN Inspections ON Area.Area = Inspections.Area
GROUP BY Area.Area
HAVING (((Area.Area)"" And (Area.Area)"ALL"))
ORDER BY Area.ID;

SELECT Area, Sum(CountOfArea) AS CountArea1,Sum(CountOfArea2) AS CountArea2
FROM qryAreaCounts
GROUP BY Area;

--
Build a little, test a little.


"dannie" wrote:

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.

"KARL DEWEY" wrote:

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;


--
Build a little, test a little.


"dannie" wrote:

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