Solved

MS Access SQL queary on First and Last Name

Posted on 2011-03-22
5
355 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EXCEL Name Matching 13 48
Excel format formula with percentage 3 9
Need multiple Group By's 8 29
Excel Formula 6 26
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

730 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