How to have ALL records appear in a query??

[AnnaZ]

I'm joining two tables in a query. I need to have all names from one table appear in the query results, even if those names don't appear in the joint.
I checked the second option in Join Properties to show ALL fields from that table, but when I run the query I only get the names where joined fields are equal. Changing the Join Properties doesn't seem to have any effect on my results. Any ideas what else I can do to show ALL of those names?

Thank you for any help.

 

[ScottGem]

I'm joining two tables in a query. I need to have all names from one table appear in the query results, even if those names don't appear in the joint.
I checked the second option in Join Properties to show ALL fields from that table, but when I run the query I only get the names where joined fields are equal. Changing the Join Properties doesn't seem to have any effect on my results. Any ideas what else I can do to show ALL of those names?

Thank you for any help.

This is known as an outer Join. You want to select all records from one table and only matching records from the other. This should show up in your SQL as a either a Right or a Left join depending on which option (2 or 3) of the Join properties you chose. Can you post your SQL so we can see what it looks like.

 

[The_Doc_Man]

If you change your SQL from Left Join (or Right Join) to Outer Join, you will get the effect. Be warned that the records corresponding to unmatched entries will have nulls in the fields that otherwise would have come from matching entries from the other table.

 

[AnnaZ]

I tried Outer join, but no effect. I tried Right Join, Left Join, Left Outer Join, but my results don't change... I need ALL records from Employees table and matching records from Hours table.

Here's my SQL:
SELECT Hours.[Employee ID], Employees.[First Name], Employees.[Last Name], Hours.[Funding Source], Hours.Hours, Hours.[Nature of Work], Hours.[Pay period]
FROM Employees LEFT OUTER JOIN Hours ON Employees.[Employee ID] = Hours.[Employee ID]
GROUP BY Hours.[Employee ID], Employees.[First Name], Employees.[Last Name], Hours.[Funding Source], Hours.Hours, Hours.[Nature of Work], Hours.[Pay period]
HAVING (((Hours.[Funding Source])="schaumburg") AND ((Hours.[Pay period])=#4/1/2005#))
ORDER BY Employees.[Last Name];


Thank you for your help!

 

[ScottGem]

Try Grouping by Employees.[EmployeeID] rather than Hours.[EmployeeID]

 

[AnnaZ]

I changed the Grouping to Employee as you suggested, and when I run the query here's the error message that comes up:

You tried to execute a query that does not include the specified expression 'Employee ID' as part of an aggregate function.

Any ideas what else I can do or what am I doing wrong?

thank you for your help.

 

[ScottGem]

What does the SQL look like now? You might want to attach a copy of your database so we can work with it.

 

[Brianwarnock]

Surely its illogical to Select hours.employeeID when you want all the records from the Employee table. I would atleast replace Hours.EmployeeID by Employees.EmployeID throughout the SQL and go from there

Brian

 

[AnnaZ]

I replaced Hours.EmployeeID by Employees.EmployeID and my results still don't change...

I'm attaching the database, the query is called qryHoursSumPerPayPeriod.

I really appreciate all the help, thank you.

 

[ScottGem]

Ok, the problem is your criteria. Since Amy Smith doesn't have a record in the hours table, when you filter for the Funding source and pay period you filter out her record.

The way around this is to add a criteria row and use Is Null as the criteria for those 2 columns.

A couple of other points. Last Name should be move before First Name and Nature of Work before Hours. Hours should be a Sum not a Group By and Pay period should be a Where not a Group By. Finally, you shouldn't use spaces in field/object names.

The correct SQL will look like this:

SELECT Employees.[Employee ID], Employees.[Last Name], Employees.[First Name], Hours.[Funding Source], Hours.[Nature of Work], Sum(Hours.Hours) AS SumOfHours
FROM Employees LEFT JOIN Hours ON Employees.[Employee ID] = Hours.[Employee ID]
WHERE (((Hours.[Pay period])=#7/1/2005#)) OR (((Hours.[Pay period]) Is Null))
GROUP BY Employees.[Employee ID], Employees.[Last Name], Employees.[First Name], Hours.[Funding Source], Hours.[Nature of Work]
HAVING (((Hours.[Funding Source])="2")) OR (((Hours.[Funding Source]) Is Null))
ORDER BY Employees.[Last Name];

 

[AnnaZ]

Scott, thank you so much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! this works great!!!
The only thing is that it doesn't show one of the Employees - John Willey.
It looks like it picked up Amy Smith because she doesn't have ANY hours, so she would be considered Null I guess. But John Willey has hours in other funding sources, so he didn't get listed here. Is there a way to get him to show up like Amy Smith, as a null for Funding Source 2?

Again, thank you so much. I think I'm almost there.

 

[ScottGem]

Ok, I missed that. You are going to have to use an interim query then. Create a query that on the Hours table that does your filtering:

SELECT Hours.jobId, Hours.[Employee ID], Hours.ClientID, Hours.[Funding Source], Hours.Hours, Hours.[Nature of Work], Hours.[Pay period]
FROM Hours
WHERE (((Hours.[Funding Source])="2") AND ((Hours.[Pay period])=#7/1/2005#));

Then use that query with the left join to employees:

SELECT Employees.[Employee ID], Employees.[Last Name], Employees.[First Name], qryHoursperPayPeriod.[Funding Source], qryHoursperPayPeriod.[Nature of Work], Sum(qryHoursperPayPeriod.Hours) AS SumOfHours
FROM Employees LEFT JOIN qryHoursperPayPeriod ON Employees.[Employee ID] = qryHoursperPayPeriod.[Employee ID]
GROUP BY Employees.[Employee ID], Employees.[Last Name], Employees.[First Name], qryHoursperPayPeriod.[Funding Source], qryHoursperPayPeriod.[Nature of Work]
ORDER BY Employees.[Last Name];

 

[AnnaZ]

OK, this is exactly what I need!!! thank you so much!!
Scott, thank you for all your help, really really appreciate it!!!!

 

[ScottGem]

glad to assist