Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS Access SQL queary on First and Last Name

Posted on 2011-03-22
5
Medium Priority
?
358 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

636 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