Multiple Queries - Certain records missing

[ktrasler]

Hello again

Sorry, just posted this in reports, moved it now.

Could someone point me in the right direction please. I have seen a thread similar to this before but I can't find it now.

I have a query that is made up from 3 different queries.

These 3 queries show the stats totals in the last x weeks.

qry2Week (colleague stats from last 2 Weeks)
qry6Week (colleague stats from last 6 Weeks)
qry13Week (colleague stats from last 13 Weeks)

This query shows every day and also the above 3 queries are linked in.
qryAll (every day and 2, 6, 13week stats totals)

The problem is if there are no stats in the last 2 weeks no stats will show up at all.

If I take out the (is not null) from the individual queries I get duplicate lines in my main query (qryAll)

How do I show all the information without having duplicate lines.

Cheers

Kev.

 

[Rickster57]

joins

Are you using a left outer join on those queries?

 

[ktrasler]

Hi


How do you left join.

Here is the sql - Do i just have to change the innerjoin to left join.

SELECT qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.TLName, qryColleagues.Shift, tblPerformance.OTCode, tblPerformance.ShiftWorked, tblPerformance.Date, tblPerformance.PickHours, tblPerformance.PickCases, tblPerformance.PickAssn, Max(qryStats2Week.SumOfPickHours) AS Hours2wk, Max(qryStats2Week.SumOfPickCases) AS Cases2wk, Max(qryStats2Week.SumOfPickAssn) AS Assn2wk, Max(qryStats6Week.SumOfPickHours) AS Hours6wk, Max(qryStats6Week.SumOfPickCases) AS Cases6wk, Max(qryStats6Week.SumOfPickAssn) AS Assn6wk, Max(qryStats13Week.SumOfPickHours) AS Hours13wk, Max(qryStats13Week.SumOfPickCases) AS Cases13wk, Max(qryStats13Week.SumOfPickAssn) AS Assn13wk
FROM (((qryColleagues INNER JOIN tblPerformance ON qryColleagues.WCN = tblPerformance.WCN) INNER JOIN qryStats2Week ON tblPerformance.WCN = qryStats2Week.WCN) INNER JOIN qryStats6Week ON tblPerformance.WCN = qryStats6Week.WCN) INNER JOIN qryStats13Week ON tblPerformance.WCN = qryStats13Week.WCN
GROUP BY qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.TLName, qryColleagues.Shift, tblPerformance.OTCode, tblPerformance.ShiftWorked, tblPerformance.Date, tblPerformance.PickHours, tblPerformance.PickCases, tblPerformance.PickAssn
HAVING (((qryColleagues.WCN)="1400023270") AND ((tblPerformance.Date)>=(Date()+1-Weekday(Date(),1)-91) And (tblPerformance.Date)<=(Date()+1-Weekday(Date(),1)-1)) AND ((tblPerformance.PickHours) Is Not Null))
ORDER BY qryColleagues.ClgName, tblPerformance.Date;

Kev.

 

[ktrasler]

I have changed the SQL to LEFT JOIN and it works now.

Thanks for you help.

Cheers

Kev.