Combining multiple queries in Access so that the data is displayed in different fields within a single table

Question

I have two queries which I want to join together. Unionoption will not work as I want the values to be displayed in different fields. The 1st query is

 SELECT Outage.DISTRICT, Count(Outage.DISTRICT) AS CountOfDISTRICT
 FROM Outage LEFT JOIN [Site ID gets generated] ON Outage.[Site ID]=[Site ID gets     generated].[Site ID]
 GROUP BY Outage.DISTRICT;

The 2nd query is

SELECT Outage.DISTRICT, Count(Outage.[Site ID]) AS [CountOfSite ID]
FROM Outage
GROUP BY Outage.DISTRICT;

I tried the below code but it is giving syntax error (missing operator) in the 1st query

SELECT Outage.DISTRICT,
(SELECT  Count(Outage.[Site ID]) AS [CountOfSite ID] FROM Outage),
(SELECT  Count(Outage.DISTRICT) AS CountOfDISTRICT FROM Outage LEFT JOIN [Site ID gets generated] ON Outage.[Site ID]=[Site ID gets generated].[Site ID])
GROUP BY Outage.DISTRICT;

It would be very helpful if answer is also given with an explanation, as I am quite new to access and trying to learn it from scratch.

Yawar · Accepted Answer · 2014-04-29 17:50:28Z

1

You can join the two queries together to get their columns side-by-side:

select
  sq1.DISTRICT
, sq1.CountOfDISTRICT
, sq2.[CountOfSite ID]
from (
  ... your first query here ...
) as sq1
inner join (
  ... your second query here ...
) as sq2
on sq1.DISTRICT = sq2.DISTRICT

The thing to remember is that unions are for joining tables/queries vertically, while joins are for joining them horizontally.

answered Apr 29, 2014 at 17:50

Yawar

11.7k4 gold badges53 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Soumyajit Over a year ago

Thanks friend it worked.#Respect. By the way can you tell me more about 'JOINS',basically their difference

Yawar Over a year ago

@Soumyajit - sure, there's a good description here: stackoverflow.com/questions/448023/…

Collectives™ on Stack Overflow

Combining multiple queries in Access so that the data is displayed in different fields within a single table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related