• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

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
0
Frank Freese
Asked:
Frank Freese
  • 10
  • 4
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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
 
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 10
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now