3rd Query giving Results of 2 other Queries (1 Viewer)

[jonnymenthol]

Hello,

I am trying to get a query working in Access, but am having problems. I wonder if someone can have a look at it, and let me know where I am going wrong ?

Firstly, I run 1 grouped query based on a single table of records with a date criteria of yesterday's date.
I group by the area, then provide a count of records against each area, with an average duration of each record.

So my query looks like :

Area - Count - Duration
ABCD - 35 - 9.02
EFGH - 22 - 7.45
and so on.

I then have the exact same query but for the day before yesterday.

Now I want to combine the two to have something like :

Area - Count (Today) - Duration (Today) - Count (Yesterday) - Duration (Yesterday)
ABCD - 35 - 9.02 - 42 - 8.43
and so on.

So I have created a 3rd query and pulled in the two original queries, then added the fields from both.

with a Number 3 Join, it seems to work fine to an extent. What I mean by that is, that it provides me with all records that appear on both days.

However, if something appears today, but not yesterday (or vice versa), then the records doesn't show in the query.

I hope someone can help.

Thank you in advance.

J.

 

[krunalprajapati]

if ur first query is area1 and second is area2

than the thied query is

select area1.*, area3.* from area1
left join (select area2.* from area2) as q3 on area1.area = q3.area
union
select area1.* , area3.* from area1
right join (select area2.* from area2) as q3 on area1.area = q3.area

 

[jonnymenthol]

Thank you very much, I appreciate it.

J.