Query Modification

Experts,
The purpose of my frmModifyAssociatedEmployees  is to change when an employee that was associated with a store is no longer there and someone new needs to be assigned. The form is based upon the tblAssignedStoreEmployees as follows:

AssignedStoreEmployee (PK)
StoreID (FK) to tblStore
DistrictManagerID (FK) to tblEmployee
AreaSupportManagerID (FK) to tblEmployee
StoreManagerID (FK) to tblEmployee
AssistantStoreManagerID (FK) to tblEmployee

The associated tables are:
tblEmployee
EmployeeID (PK)

tblEmployeeType
EmployeeTypeID (PK) an employee type for District Manager, for example, can be a 1,5

tblStore
StoreID (PK)

I have one combo box cboAssociatedStore and four unbounded text boxes at this point. The query for the combo box is as follows:

SELECT DISTINCT tblStore.StoreNumberID, 
"Store # " & [StoreNumber] & " " & Mid([StoreName],12)
 & "  " & [StoreAddressOne] AS Store

Open in new window


The first thing I need to do is expand the above query such that the unbounded text boxes can receive data from corresponding columns in this query. The four unbound text boxes that receive data are for the District Manager, Area Support Manager, Store Manager and Assistant Store Manager. At this point I’m only looking for the District Manager since I can use that as my template. I tried this and failed:
 
DM: (Select[tblEmployee.EmployeeID] FROM
 tblEmployee INNER JOIN tblAssignedStoreEmployees.DistrictManager
WHERE tblAssignedStoreEmployees.StoreID =
 tblStore.StoreNumberID AND tblEmployeeTypeID In (1,5))

Open in new window

Afterwards, I will open a follow-up question where I will need the user to be able to modify a record if there is a change in an employee.

The attached db opens to the form needing help.
 StoreBonusRev2.mdb
Frank FreeseAsked:
Who is Participating?
 
MINDSUPERBCommented:
Here's the updated file.

It captures the name of the employee provided that their IDs are in Employees table.

Ed
StoreBonusRev2.mdb
0
 
Helen FeddemaCommented:
Here is the syntax for writing data from combo box columns to unbound textboxes (make sure the combobox is set for the number of columns from which you need to retrieve data):
Me![txtLastNameFirst] = Me![cboSelect].Column(1)
or
   strLastNameFirst = Me![cboSelect].Column(1)

(numbering is zero-based, so Column(1) is the 2nd column)

Open in new window

0
 
Frank FreeseAuthor Commented:
the syntex I know. It's the modification to the query I need please
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MINDSUPERBCommented:
I may suggest to use DlookUp function instead, if you want only to capture a single information from another table. See below link on how to do it.
http://www.techonthenet.com/access/functions/domain/dlookup.php

You need to fix the relationship of your table as well. Does StoreID=StoreNumberID?

Sincerely,
Ed
0
 
Frank FreeseAuthor Commented:
Folks,
I did make a mistake; in the tblStore StoreNumberID is the PK
0
 
Frank FreeseAuthor Commented:
folks,
I'm almost there...I think. Here's my revised code:
DM: (Select[tblEmployee.EmployeeID]
 FROM tblEmployee INNER JOIN 
tblAssignedStoreEmployees On tblEmployee.EmployeeID = tblAssignedStoreEmployees.AssignedEmployeesID
 WHERE tblAssignedStoreEmployees.StoreID =
 tblStore.StoreNumberID) And 
[tblEmployeeType].[EmployeeTypeID] In (1,5)

Open in new window


I'm missing their name
0
 
MINDSUPERBCommented:
See attache file as a sample of using DlookUp function.

Ed
StoreBonusRev2.mdb
0
 
Frank FreeseAuthor Commented:
where's the changes located please
0
 
Frank FreeseAuthor Commented:
MINDSUPERB:
open the db - where I am looking at for the dlookup
0
 
MINDSUPERBCommented:
It's in the District Manager textbox.

ed
0
 
Frank FreeseAuthor Commented:
I've made a few changes but still cannot get Employees Name:
DistrictManager: (Select[tblEmployee.EmployeeFirstName]
&" "&[tblEmployee.EmployeeLastName]
 AS DM FROM tblEmployee INNER JOIN
 tblAssignedStoreEmployees On
 tblEmployee.EmployeeID = 
tblAssignedStoreEmployees.DistrictManagerID
WHERE tblAssignedStoreEmployees.StoreID
 = tblStore.StoreNumberID) And [tblEmployeeType].[EmployeeTypeID] In (1,5)

Open in new window

0
 
Frank FreeseAuthor Commented:
ED;
OK found it....how do I get the name now to display..this may be a good option
0
 
Frank FreeseAuthor Commented:
I think I can move forward on this and will know tomorrow. My follow-up question will be changing a record when there has been an employee changed.
0
 
Frank FreeseAuthor Commented:
this looks like a solution for my objective - thank you
0
 
Frank FreeseAuthor Commented:
can you expand - I cannot capture the other columns
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.