Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
Solved
Access 2000 - Top Nth function
Posted on 2002-03-27
I am hoping this is a simple problem. I have a database with a query that shows a unit number, and then every event log entry for that unit. I am looking to have it return just the most recent result for each unit number. The query basically looks like this:
SELECT DISTINCT product.unit_number, log.create_date
FROM (Product LEFT JOIN Log ON Product.unit_number = log.Unit_Number)
WHERE Product.Unit_Number = "Variable"
ORDER BY Product.Unit_Number
If I use a SELECT TOP 1 then I just get a single Unit Number returned. Any help would be much appriciated. If this question is not as easy as I believe it should be, I will raise the points offered accordingly.
SELECT DISTINCT product.unit_number, log.create_date
FROM (Product LEFT JOIN Log ON Product.unit_number = log.Unit_Number)
WHERE Product.Unit_Number = "Variable"
ORDER BY Product.Unit_Number
If I use a SELECT TOP 1 then I just get a single Unit Number returned. Any help would be much appriciated. If this question is not as easy as I believe it should be, I will raise the points offered accordingly.
4 Comments
Try this it should select the max date or the highest date.
SELECT DISTINCT product.unit_number, max(log.create_date) as Create_Date
FROM (Product LEFT JOIN Log ON Product.unit_number = log.Unit_Number)
WHERE Product.Unit_Number = "Variable"
ORDER BY Product.Unit_Number
Cheers
Marcus
SELECT DISTINCT product.unit_number, max(log.create_date) as Create_Date
FROM (Product LEFT JOIN Log ON Product.unit_number = log.Unit_Number)
WHERE Product.Unit_Number = "Variable"
ORDER BY Product.Unit_Number
Cheers
Marcus
first of all what establishes "most recent result"
is this based on date or what?
If you're using the SQL that you've got above then I can see where you may be having some trouble. The top predicate is used on things like top sales figure, top number of orders by date, top number of records based on date or customer. Or like the following.
eg: SELECT DISTINCTROW TOP 10 Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC;
this is out of nwind.mdb in 2000 look at the example in northinds you may be able to figure a few things out.
If the sql you posted isn't the exact sql used then please post it. it will help to decipher your problem better.
is this based on date or what?
If you're using the SQL that you've got above then I can see where you may be having some trouble. The top predicate is used on things like top sales figure, top number of orders by date, top number of records based on date or customer. Or like the following.
eg: SELECT DISTINCTROW TOP 10 Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC;
this is out of nwind.mdb in 2000 look at the example in northinds you may be able to figure a few things out.
If the sql you posted isn't the exact sql used then please post it. it will help to decipher your problem better.
Featured Post
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…
Suggested Courses
Course of the Month5 days, 23 hours left to enroll
568 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.