Solved

MS Access SQL queary on First and Last Name

Posted on 2011-03-22
5
356 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 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