Left Join is returning wrong data

[Victor70]

Hello, Access experts,

I have query "qryEmployeesB&N" (unique records, certain employees but not all)
ID LastName FirstName MName Suffix Department Group

and
table "tblActivities".
ID Date Activity
X 1/1/12 YYY
X 1/2/12 YYY
Y 1/1/12 YYY

I am working on a query that would give me name and group for every line in tblActivities.
The expected outcome is.
ID Date Activity LastName Group
X 1/1/12 YYY Smith B
X 1/2/12 YYY Smith B
Y 1/1/12 YYY Jones NB
Z 1/1/12 YYY 'This employee is not on my list

I am using a left outer join to link qryEmployeesB&N and tblActivities via ID because tblActivities has activities for all employees and I do not want to lose this data.

When there is no matching ID in qryEmployeesB&N (the activity is done by someone I am not interested in right now), I get:
- blank field for the name (which is logical for this join: no match - no name)
- BUT it puts "B" for the group (despite of the fact that this person is not on my list/query, and is not associated with group B or NB).

Why am I getting this "B" value when it is not supposed to be there?

Please advise.

SELECT [tblActivities].[ID], [qryEmployeesB&NB].Group, [qryEmployeesB&NB].LastName
FROM tblActivities LEFT JOIN [qryEmployeesB&NB] ON [tblActivities].[ID] = [qryEmployeesB&NB].[ID#];

 

[PNGBill]

You are not making it any easier when using reserved names in your fields.

ID - IDNum
Date - DateJoined
Activity - ActivityType
LastName
Group - GroupName

Just suggestions. Good practice to use double words and avoid possible issues.

Date and Group are both reserved words. Maybe ID and Activity are ??

 

[jdraw]

I agree with Bill re reserved words isn't helping.

Here is sample with Left Join
http://www.w3schools.com/sql/sql_join_left.asp

 

[Victor70]

Thank you, PNGBill and Jdraw!

I changed Group to Group1, Date to Date1.

In fact I did not have Activity field (I simplified the name for this example, not realizing that this may impact the result).

The result is still the same (see the picture attached). Field10 there is the ID. Everything works fine except the Group1 field - it gets filled regardless of whether the match exists or not.

I tried to convert the query into a table. Two tables linked worked fine afterwards. I guess, I need to look into the sourcing query to see why this is happening.

 

[Victor70]

The expression in the query is

Group1: IIf([emsection]="UP",IIf(Mid([emshift],2,1)="B","B","NB"),"[COLOR="Red"]B[/COLOR]")

The last "B" gets assigned all over the place in my final left join query. However, since the expression is only in the sourcing query, it is supposed to assign "B" to every non-UP entry in my employee list only.

What is wrong with this expression??? Why does it spill over to the final query?

 

[PNGBill]

Where is the False part of your first IIF ?

IIf([emsection]="UP", "B",)

Will return B where emsection is not UP

 

[Victor70]

Thank you, Bill.

The false part of my iif statement is "B" highlighted red. The true part is somewhat convoluted because I have to "extract" the second letter from emshift and check if it is B or not B.

The problem still persists (as it can be seen in the picture attached). I get Bs for records that are not supposed to get those. Like I said, this only happens with this field, other fields in the final query are working fine (again, see the picture).

 

[PNGBill]

I may be on the wrong track but it appears to me your IIf is asking for all [emsection]="UP" to be B or NB.

 

[Victor70]

Thanks for your input, Bill!

Group1: IIf([emsection]="UP",IIf(Mid([emshift],2,1)="B","B","NB"),"B")

Your are right. The UP can be B or NB. This expression is part of the query. I am filtering out the emsections that I do not need in it.

This query qryEmployeesB&N and tblActivities are left join linked via ID in the final query.

I do not need any data (name, group) for the person's activities if that person is not in the results of the query qryEmployeesB&N. This works fine for all the fields but one - Group is giving Bs to everyone's activity. In the picture attached to my previous post you can see that the only match was Covey, and I ma not getting names, platoons, emsections for others.
But I do get the Group for everyone....

 

[jdraw]

Have you tried
Group1: IIf([emsection]="UP" AND Mid([emshift],2,1)="B","B","NB")

 

[Victor70]

That's exactly what I have there right now.

I should be getting Bs, NBs, and blanks, while I am getting Bs and NBs.

 

[jdraw]

Where would the blanks come from?
With the set up mentioned you have B if True, otherwise NB

 

[Victor70]

Like I said, this expression is in qryEmployeesB&N. I am using then a left outer join to join it with tblActivities, from which I should be getting all records from the table and only those records from the query where the joined fields are equal.
The picture I attached before shows that the link "works" fine for every field in qryEmployeesB&N but the Group field.