asked on

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

Helen Feddema

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

Frank Freese

ASKER

the syntex I know. It's the modification to the query I need please

MINDSUPERB

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

Frank Freese

ASKER

Folks,
I did make a mistake; in the tblStore StoreNumberID is the PK

Frank Freese

ASKER

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

MINDSUPERB

See attache file as a sample of using DlookUp function.

Ed
StoreBonusRev2.mdb

Frank Freese

ASKER

where's the changes located please

Frank Freese

ASKER

MINDSUPERB:
open the db - where I am looking at for the dlookup

MINDSUPERB

It's in the District Manager textbox.

ed

Frank Freese

ASKER

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

Frank Freese

ASKER

ED;
OK found it....how do I get the name now to display..this may be a good option

ASKER CERTIFIED SOLUTION

MINDSUPERB

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Frank Freese

ASKER