Link to home
Start Free TrialLog in
Avatar of James
JamesFlag for Canada

asked on

Microsoft Access - Calculated query field showing up with no underlying records in table

I have a database (see attached) that has employees and tools. Each tool can be assigned to one employee but it can also be assigned to no employee. So there's an EmployeeID field in the Tools table that either has an employee ID in it or is null.

I have a query of Employees that has a field called FullName that is defined as [LastName] & ", " & [FirstName]

Lastly, I have a query built on the Tools table and the Employees query that displays the tool data and also the FullName of the employee the tool is assigned to. The query is set to show all records from the Tools table and only those records from the Employees table where the values are equal.

My issue is that if the tool isn't assigned to any employee, the FullName in the Tools query shows up as just a comma. I would have expected it to be completely blank since there's no record in qryEmployees that associates with this.

If I change the join in the query to only show only show records in both tables where the joined fields are equal, it leaves the records with no employee ID out of the query (as expected).

You can see this behaviour by opening qryTools in the attached database.

I don't know why it's showing the calculated FullName field for records that don't exist in the Employees query.

Any suggestions about why this is happening and how I can stop it?

Thanks in advance.tools.accdb
ASKER CERTIFIED SOLUTION
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of James

ASKER

Thank you! That's exactly what I needed to know.
Avatar of James

ASKER

Just curious: do you have a link to any documentation about this? I'm just curious to see the extent of the issue.

Thanks again! Your solution worked perfectly and saved me a ton of time.
Afraid I don't have any official link handy as to the issue of the calculated field.

But the effect/behavior of concatenation using & or + should be easy enough to search for.