Matching results from two queries

[Guest]

I have results of query that returns the Interval week based on the week
ending date. example - wk ending date of 01/06/07 returns 0701. We have the
entire year plus on this. I am setting the interval week from a table in
another query. Then in the matching query, I tell it to join on the interval
week using all the records from the interval week query and those that match
from the other query.

Here is what is happening. The joining query has only the records that
match in both queries. Example

Interval Week Query Other Query Output

0701 0701 0701
0702 0703
0703 0703

How can I get all the records on the interval week query and the ones that
match in the output?

Ideas?

Thanks in advance for your assistance!

 

[alex]

I have results of query that returns the Interval week based on the week
ending date. example - wk ending date of 01/06/07 returns 0701. We have the
entire year plus on this. I am setting the interval week from a table in
another query. Then in the matching query, I tell it to join on the interval
week using all the records from the interval week query and those that match
from the other query.

Here is what is happening. The joining query has only the records that
match in both queries. Example

Interval Week Query Other Query Output

0701 0701 0701
0702 0703
0703 0703

How can I get all the records on the interval week query and the ones that
match in the output?

Ideas?

Thanks in advance for your assistance!

Use an outer join...
In design view, right click your join line and select > join
properties...the rest should be self explanatory.

alex

 

[Guest]

Alex,

I was trying to explain that is what I am doing but I am not getting the
results I need. I have tried all of those options in the join properities to
no avail.

 

[John W. Vinson]

Alex,

I was trying to explain that is what I am doing but I am not getting the
results I need. I have tried all of those options in the join properities to
no avail.

Please post the SQL view of the query and indicate in what way it "isn't
working".

John W. Vinson [MVP]

 

[Guest]

Hi John!

I will post the code below. The way it is not working is as follows. The
interval Query has all the week ending dates for our fiscal year. I wish to
have an entry for everyone for each week regardless if they have data for
that week or not. There are 5 different metrics that are reported on for
each person for each week. I wish to group all of these metrics for a single
week for a single person. The code works fine as long as the person has some
data in the 'Reservation' metric but if the person was on vacation for one
week, all other data is not brought in either. I wished to group on the
Interval query to make certain all weeks are covered.

The SQL :

SELECT IntervalQuery.[Year & Week], IntervalQuery.[Week Ending Date],
[AgtResScoresYTD-final].Specialist, [AgtResScoresYTD-final].[Total Issues],
[AgtResScoresYTD-final].[Total Reservations],
[AgtResScoresYTD-final].Accuracy, [AgtResScoresYTD-final].[Res Week&Year],
[Agent Monitor Scrs-Final -top].[Monitor Week&Year], [Agent Monitor
Scrs-Final -top].AvgOfTotalScore, [Agent Monitor Scrs-Final
-top].CountOfTotalScore, [Agent CallData Scrs - Final].[Weekly Calls
Presented], [Agent CallData Scrs - Final].[Avg Talk Time], [Agent CallData
Scrs - Final].[CallWeek & Year], [AgentAttScrYTD-Final].[AttendWeek & Year],
[AgentAttScrYTD-Final].SumOfPoints, [AgentAdherScr-YTD-final].[Wkly
Adherence], [AgentAdherScr-YTD-final].[Wkly Conformance],
[AgentAdherScr-YTD-final].[AdherWeek & Year]
FROM IntervalQuery LEFT JOIN (((([AgtResScoresYTD-final] LEFT JOIN [Agent
Monitor Scrs-Final -top] ON ([AgtResScoresYTD-final].[Res Week&Year] = [Agent
Monitor Scrs-Final -top].[Monitor Week&Year]) AND
([AgtResScoresYTD-final].Specialist = [Agent Monitor Scrs-Final
-top].Specialist)) LEFT JOIN [Agent CallData Scrs - Final] ON
([AgtResScoresYTD-final].[Res Week&Year] = [Agent CallData Scrs -
Final].[CallWeek & Year]) AND ([AgtResScoresYTD-final].Specialist = [Agent
CallData Scrs - Final].Specialist)) LEFT JOIN [AgentAttScrYTD-Final] ON
([AgtResScoresYTD-final].[Res Week&Year] = [AgentAttScrYTD-Final].[AttendWeek
& Year]) AND ([AgtResScoresYTD-final].Specialist =
[AgentAttScrYTD-Final].Specialist)) LEFT JOIN [AgentAdherScr-YTD-final] ON
([AgtResScoresYTD-final].[Res Week&Year] =
[AgentAdherScr-YTD-final].[AdherWeek & Year]) AND
([AgtResScoresYTD-final].Specialist = [AgentAdherScr-YTD-final].Specialist))
ON IntervalQuery.[Year & Week] = [AgtResScoresYTD-final].[Res Week&Year]
GROUP BY IntervalQuery.[Year & Week], IntervalQuery.[Week Ending Date],
[AgtResScoresYTD-final].Specialist, [AgtResScoresYTD-final].[Total Issues],
[AgtResScoresYTD-final].[Total Reservations],
[AgtResScoresYTD-final].Accuracy, [AgtResScoresYTD-final].[Res Week&Year],
[Agent Monitor Scrs-Final -top].[Monitor Week&Year], [Agent Monitor
Scrs-Final -top].AvgOfTotalScore, [Agent Monitor Scrs-Final
-top].CountOfTotalScore, [Agent CallData Scrs - Final].[Weekly Calls
Presented], [Agent CallData Scrs - Final].[Avg Talk Time], [Agent CallData
Scrs - Final].[CallWeek & Year], [AgentAttScrYTD-Final].[AttendWeek & Year],
[AgentAttScrYTD-Final].SumOfPoints, [AgentAdherScr-YTD-final].[Wkly
Adherence], [AgentAdherScr-YTD-final].[Wkly Conformance],
[AgentAdherScr-YTD-final].[AdherWeek & Year]
ORDER BY [AgtResScoresYTD-final].Specialist;

Thanks for your help!

 

[John W. Vinson]

Hi John!

I will post the code below. The way it is not working is as follows. The
interval Query has all the week ending dates for our fiscal year. I wish to
have an entry for everyone for each week regardless if they have data for
that week or not. There are 5 different metrics that are reported on for
each person for each week. I wish to group all of these metrics for a single
week for a single person. The code works fine as long as the person has some
data in the 'Reservation' metric but if the person was on vacation for one
week, all other data is not brought in either. I wished to group on the
Interval query to make certain all weeks are covered.

The SQL :

Well, of course I don't know anything about the data in your table - but it
LOOKS like you should get this *if* IntervalQuery contains a record for every
time period. You have left joins and no criteria, so the query should return a
record for every value in IntervalQuery.

If you open IntervalQuery directly does it show all the time periods?

John W. Vinson [MVP]

 

[Guest]

Yes, It does!

Well, of course I don't know anything about the data in your table - but it
LOOKS like you should get this *if* IntervalQuery contains a record for every
time period. You have left joins and no criteria, so the query should return a
record for every value in IntervalQuery.

If you open IntervalQuery directly does it show all the time periods?

John W. Vinson [MVP]

 

[Guest]

John,

I've tried this same type of query before with great success. I was
wondering if there is some 'trick' I need to try. I have tried to re-write
the query, bring in a totally new query, change the interval week query to
write a table, but no matter what, I am always missing one or more week.

Ideas are welcome!