Getting last 6 entries for each person

25 pts.
Tags:
Microsoft Access 2003
Microsoft Access forms
Hi everyone,

I am trying to create a query that pulls the last 6 exam scores for each of the operators at the plant I work at.  I have a table that has the last 2-3 years worth of data.  The current query I have just organizes the data without filtering the last 6 entries, and I cannot figure out the commands to use.  Any assistance will be greatly appreciated.



Software/Hardware used:
Access 2003

Answer Wiki

Thanks. We'll let you know when a new response is added.

Use ‘TOP 6′ in your query. Not sure how you would pull everybody’s top 6. I know a union would work but then you would need to create a union for each employee and that would suck if you had a bunch of people. Hopefully this is of some help to you.
Here’s a union example.
SELECT TOP 6 Table1.[name1], Table1.[num1]
FROM Table1
WHERE name1=”Larry”
UNION SELECT TOP 6 Table1.[name1], Table1.[num1]
FROM Table1
WHERE name1=”Fred”;

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DASiuda
    I was able to TOP 6 in my query, while it gave me some of the data I was looking for, it did not give me all that I needed. Some of the operators only showed 1 or 2 data points, while others showed the 6 I wanted.
    25 pointsBadges:
    report
  • carlosdl
    I would suggest posting the query you are using, and some example data.
    70,220 pointsBadges:
    report
  • DASiuda
    The following is the SQL view of the query that I am running to get the average of the exam scores.  Right now this query works to give me the average of all of the exams each operator has taken.  I want to be able to limit this query to the last 6 exams taken by each operator. SELECT Round(Avg(tbl_WrittenExams.Score),1) AS REA, [LastName] & ", " & [FirstName] AS [Operator Name]FROM tbl_OperatorInformation INNER JOIN tbl_WrittenExams ON tbl_OperatorInformation.EmployeeID = tbl_WrittenExams.EmployeeIDGROUP BY [LastName] & ", " & [FirstName], tbl_WrittenExams.EmployeeID, tbl_OperatorInformation.FirstName, tbl_OperatorInformation.LastNameORDER BY [LastName] & ", " & [FirstName]; Again, I appreciate any help with this.
    25 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following