Query help with WHERE statement

Experts,
Every store has differet types of employees assigned to it. My table tblAssignedStoreEmployees is designed to capture these employees:
DesignatedEmployeeID (Number)
StoreID (FK)
DistrictManager (FK)
StoreManager (FK)
I have three additional tables:
tblStore
StoreID (PK)

tblEmployee
EmployeeID (PK)

tblEmployeeType
EmployeeTypeID (PK)

I have struggled with this query listed below getting no where. My objective is to query the employee, e.g. DistrictManager and StoreManager associated with the store. This example only is looking for the District Manager but I get that solved I can do the store manager

DistrictManager: (Select [tblEmployee.EmployeeFirstName]
&" "&[tblEmployee.EmployeeLastName] AS DMName
FROM tblEmployee INNER JOIN 
tblAssignedStoreEmployees.DistrictManager 
Where tblAssignedStoreEmployees.StoreID
= tblStore.StorNumberID and 
tblEmployee.EmployeeID IN (1,5))

Open in new window

Frank FreeseAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
test this


StoreBonusRev2.mdb
0
 
Rey Obrero (Capricorn1)Commented:
shouldn't you be using the table/field  
 tblEmployeeType.EmployeeTypeID  ?


0
 
Frank FreeseAuthor Commented:
I think your right - let me try that now....
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Frank FreeseAuthor Commented:
I changed to this and received a syntax error:

DistrictManager: (Select [tblEmployee.EmployeeFirstName]
&" "&[tblEmployee.EmployeeLastName]
AS DMName FROM tblEmployee
INNER JOIN 
tblAssignedStoreEmployees.DistrictManager 
Where tblAssignedStoreEmployees.StoreID
= tblStore.StorNumberID and 
tblEmployeeType.EmployeeTypeID IN (1,5))

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
post the whole query
0
 
Frank FreeseAuthor Commented:
I found two errors - but still get syntex error
0
 
hnasrCommented:
"INNER JOIN
tblAssignedStoreEmployees.DistrictManager " INNER JOIN table not a field!

INNER JOIN table ON field1=field2

Save time and effort of reproducing your database, by including a sample database. List the required output.

0
 
Frank FreeseAuthor Commented:
I'm getting closer:
DistrictManager: (Select [tblEmployee.EmployeeFirstName]
&" "&[tblEmployee.EmployeeLastName] 
AS DMName FROM tblEmployee 
INNER JOIN tblAssignedStoreEmployees
ON tblEmployee.EmployeeID = 
tblAssignedStoreEmployees.DistrictManager 
Where tblAssignedStoreEmployees.StoreID
= tblStore.StoreID and 
tblEmployeeType.EmployeeTypeID IN (1,5))

Open in new window

But get an error at tblStore.StoreID
0
 
Frank FreeseAuthor Commented:
error at = tblStore.StoreID which should have been
tblStore.StoreNumberID but still not there?
0
 
Rey Obrero (Capricorn1)Commented:
upload a copy of the db
0
 
Frank FreeseAuthor Commented:
the attached db open to the form with the problem and the error.
StoreBonusRev2.mdb
0
 
Frank FreeseAuthor Commented:
I've confused things cap and I apologize for wasting your time . I'm wanting to list in that combo box just District Managers. All I needed to do was under employee type enter a 1 or 5.  
0
 
Frank FreeseAuthor Commented:
I thank you and again I'm sorry for wasting your time. Guess just tired..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.