Solved

MS Access SQL queary on First and Last Name

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access creating a Chart from Search result on Form view 2 21
data analyst 3 49
Excel 2016 Not Responding Issues 6 28
Help Extract Specific in SQL 8 24
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now