Unanswered: 3rd Query giving Results of 2 other Queries
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.
This one is causing me more of a headache than I thought. The best I can come up with so far is a union query with the following sql:
SELECT Area, null, null, CountOfArea, SumOfDuration From Query1
UNION SELECT Area,CountOfArea, SumOfDuration, null, null From Query2;
CountOfArea and SumOfDuration are the two names used to display the fields in each query. Unfortunately, you end up with a separate record for each area and day.
With a little more info I can write a module for you that will use a recordset to put the results into a new table in the format you require.
When queries get to this level of complexity it is often necessary to use modules.
Please attach a copy of your database for me to look at. Thanks.
Thanks for your offer of help, but I have managed to get round it by having an extra query which provides all areas logged over the past 2 days, then adding this to the query and having 2 Left Joins to my tables.
It all works fine now.
However, I have run into another problem, which judging from your knowledge so far, I think would be quite easy for you.
I am now moving a step forward now, and doing calculations based on the query, and one of them involves division. It works fine until divding by 0, then it provides #Error in the field.
Can I do an IIF statement to remove this #Error, and replace it with somethign like "N/A"?