Spot the difference in these queries...

[andy_dyer]

Ok...

I have two queries both of which take data from two sources and through some convoluted relationships display all tasks regardless of whether they have time booked to them or not - the first one works but doesn't use the right data for my new requirements the second one only displays those with booked time and I cannot work out what is different...

SELECT qryProjectbyTask.[Project Name], qryProjectbyTask.[Project Value (Project)], qryProjectbyTask.[Client Name], qryProjectbyTask.[Task Name], qryProjectbyTask.[Task Estimated Hrs], qryProjectbyTask.[Task Actual Hrs]
FROM qryProjectbyTask RIGHT JOIN qryProjectSelect ON qryProjectbyTask.[Project Name] = qryProjectSelect.[Project Name]
GROUP BY qryProjectbyTask.[Project Name], qryProjectbyTask.[Project Value (Project)], qryProjectbyTask.[Client Name], qryProjectbyTask.[Task Name], qryProjectbyTask.[Task Estimated Hrs], qryProjectbyTask.[Task Actual Hrs];


SELECT qryProjectbyTaskWeeklyHours5.[Task Name], qryProjectbyTaskWeeklyHours5.[Task Estimated Hrs], qryProjectbyTaskWeeklyHours5.[Task Hours], qryProjectbyTaskWeeklyHours5.[Task Discretionary Hours], qryProjectbyTaskWeeklyHours5.[New Task Hours]
FROM qryProjectbyTaskWeeklyHours5 LEFT JOIN qryProjectbyTask ON qryProjectbyTaskWeeklyHours5.[Project Name] = qryProjectbyTask.[Project Name]
GROUP BY qryProjectbyTaskWeeklyHours5.[Task Name], qryProjectbyTaskWeeklyHours5.[Task Estimated Hrs], qryProjectbyTaskWeeklyHours5.[Task Hours], qryProjectbyTaskWeeklyHours5.[Task Discretionary Hours], qryProjectbyTaskWeeklyHours5.[New Task Hours]
ORDER BY qryProjectbyTaskWeeklyHours5.[Task Name];

Any takers for a bit of spot the difference??

 

[DCrake]

The only obvious difference is that you have a left and a right join the different queries.

 

[namliam]

lets start by making them readable

SELECT     qryProjectbyTask.[Project Name]
         , qryProjectbyTask.[Project Value (Project)]
         , qryProjectbyTask.[Client Name]
         , qryProjectbyTask.[Task Name]
         , qryProjectbyTask.[Task Estimated Hrs]
         , qryProjectbyTask.[Task Actual Hrs]
FROM       qryProjectbyTask 
RIGHT JOIN qryProjectSelect ON qryProjectbyTask.[Project Name] = qryProjectSelect.[Project Name]
GROUP BY   qryProjectbyTask.[Project Name]
       ,   qryProjectbyTask.[Project Value (Project)]
       ,   qryProjectbyTask.[Client Name]
       ,   qryProjectbyTask.[Task Name]
       ,   qryProjectbyTask.[Task Estimated Hrs] 
       ,   qryProjectbyTask.[Task Actual Hrs];

SELECT    qryProjectbyTaskWeeklyHours5.[Task Name] 
        , qryProjectbyTaskWeeklyHours5.[Task Estimated Hrs]
        , qryProjectbyTaskWeeklyHours5.[Task Hours]
        , qryProjectbyTaskWeeklyHours5.[Task Discretionary Hours]
        , qryProjectbyTaskWeeklyHours5.[New Task Hours]
FROM      qryProjectbyTaskWeeklyHours5 
LEFT JOIN qryProjectbyTask ON qryProjectbyTaskWeeklyHours5.[Project Name] = qryProjectbyTask.[Project Name]
GROUP BY  qryProjectbyTaskWeeklyHours5.[Task Name]
        , qryProjectbyTaskWeeklyHours5.[Task Estimated Hrs]
        , qryProjectbyTaskWeeklyHours5.[Task Hours]
        , qryProjectbyTaskWeeklyHours5.[Task Discretionary Hours]
        , qryProjectbyTaskWeeklyHours5.[New Task Hours]

Lets not go with the obvious,
- you are not using the Group by at all... No sum,avg, count what so over.
- Joining on a name field... ussually a big cause for trouble!

The big difference offcourse is the Left vs Right join.
The left join (Second query) will show all from the qryProjectbyTaskWeeklyHours5 , while the right join (second) should show all from the qryProjectSelect query.

Now if you want to see all the tasks I would have expected that to be reverse to always show the Task query?? I.e. To be both Left Join as indicated already by DCrake