Solved

MS Access SQL queary on First and Last Name

Posted on 2011-03-22
5
354 Views
Last Modified: 2012-05-11
I have a query where Supervisors are able to search for an employee on their last name.  As expected there are multiple employees with the same last name.  Is there a way I can ask the user to input the employee's first name ONLY if there are other employees with the last name that they are searching for?  I wasn't sure if there was a way to do this within the query or if I had to write some code where I search through the records.  

Here is the query that's currently in place:

SELECT Requests.RequestID, Requests.EmployeeFirstName AS FirstName, Requests.EmployeeLastName AS LastName, Requests.Department, Requests.RequestTypeID, Requests.StartDate, Requests.EndDate, Requests.CreationDate, Requests.Comments, Requests.HoursOfVacation, Requests.SupervisorID, Requests.EmployeeSignature, Requests.SupervisorSignature, Requests.[Approved/Unapproved], Requests.[Request Type], Requests.SupervisorComments, Requests.DateProcessed, Requests.PointsIssued
FROM Requests
WHERE ((([Requests].[EmployeeLastName])=[Employee Lookup:]));


Thank you!
0
Comment
Question by:AronMcD
  • 2
  • 2
5 Comments
 
LVL 4

Accepted Solution

by:
MeLindaJohnson earned 500 total points
ID: 35193480
Are they using the query window for their request?
          If so, I would move this to a form.
          Then from a combobox, have them choose the person they want.
                       Display the lastname and firstname for them to choose.
                       RecordSource: select lastname,firstname from requests group by lastname,first
          Then on the after update or a button, run some code that updates the sql statement for the query to their selection.

Create a query first called qryRequests (use your sql statement minus the where statement)

On the button or AfterUpdate of combobox:

Dim myq as querydef
set myq = currentdb.querydefs("qryRequests")
myq.sql = "Select (use your field names here) from requests where employeelastname = '" & combobox.column(1) & "' and employeefirstname = '" & combobox.column(2) & "'"
myq.close
set myq = nothing
         
docmd.openquery("qryRequests")
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35195065
This should do it:

SELECT Requests.RequestID, Requests.EmployeeFirstName AS FirstName, Requests.EmployeeLastName AS LastName, Requests.Department, Requests.RequestTypeID, Requests.StartDate, Requests.EndDate, Requests.CreationDate, Requests.Comments, Requests.HoursOfVacation, Requests.SupervisorID, Requests.EmployeeSignature, Requests.SupervisorSignature, Requests.[Approved/Unapproved], Requests.[Request Type], Requests.SupervisorComments, Requests.DateProcessed, Requests.PointsIssued
FROM Requests
WHERE [Requests].[EmployeeLastName] & [Requests].[EmployeeLastName] Like [Employee LookupLast:] & [Employee LookupFirst] & "*";
0
 

Author Comment

by:AronMcD
ID: 35208462
They (the higher ups) decided for now they just want me to include all employees and ORDER BY the First Name.  I guess if they decide later they want something like I explained in this question I'll have to revisit it.  

Thank you for your help.  I'm not sure how to award pts or what to do with this question.  
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35209048
Notwithstanding the higher ups, you have to decide whether the question as asked was answered, and if so, by whom, and whether the answer provided was complete (for grading purposes), and award accordingly.
0
 

Author Closing Comment

by:AronMcD
ID: 35214198
This is the approach I would have went with.  Thank you both for your help.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question