Have Query Return Data Unconditionally

[Fasopus]

So right now I have a query that fetches all the information I need from one table. Now I need to add a field that will fetch data from another table, the problem is that the data in the second table may not have an entry for a given ID in the first table. This results in my query returning only those who have entries in both tables. What Im looking for is to take the original query and simply add a field to it, if the persons ID has no entry in the second table I want the query to output null or 0 as this field on the record. Heres my original SQL statement:

SELECT [Most Recent Performances Per Person].ID, [Most Recent Performances Per Person].[Most Recent Date], Performance.Logged, Performance.Score, Names.[Full Name], [Warehouse Info].Hours
FROM ([Most Recent Performances Per Person] INNER JOIN (Performance INNER JOIN [Warehouse Info] ON Performance.ID = [Warehouse Info].Alias) ON ([Most Recent Performances Per Person].[Most Recent Date] = Performance.Date) AND ([Most Recent Performances Per Person].ID = [Warehouse Info].Alias)) INNER JOIN [Names] ON (Performance.ID = Names.Alias) AND ([Warehouse Info].ID = Names.ID)
WHERE ((([Warehouse Info].[Position Title])="Inventory Control Clerk"))
GROUP BY [Most Recent Performances Per Person].ID, [Most Recent Performances Per Person].[Most Recent Date], Performance.Logged, Performance.Score, Names.[Full Name], [Warehouse Info].Hours;

 

[namliam]

what you need to do is to Double click on the lines in query design.
There you can set what type of join you want...

[Most Recent Performances Per Person] INNER JOIN
Will then change to something like:
Most Recent Performances Per Person] LEFT JOIN
or
Most Recent Performances Per Person] RIGHT JOIN
Depending on the option you choose, this is commonly known as an "OUTER JOIN"

Also you might want to read up on "naming convention" and why you are setting yourself up for potential trouble

Finaly, why the "Group by" when your not using it?

 

[Fasopus]

Thanks for the help namliam, now my query works almost perfectly! I just need to figure out how to turn the null value into a zero when it appears on the report. With the whole GROUP BY thing, I'm not too certain what I'm doing, I'm not versed in SQL in any way and I've been building all of my querys though Access 2007's design view

 

[namliam]

The NZ function will do the Null > Zero 'transformation'

The group by is because you clicked the Sigma sign button (M turned sideways)
Click it again to undo it