Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.
Course Of The Month
2 days, 15 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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.
[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
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.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Access Runtime Error '3070' | 5 | 52 | |
| Reports issues going from Access 2003 to Access 2013 | 14 | 71 | |
| Sharepoint list value updated via access or Excel | 3 | 34 | |
| Outlook mail to Access | 8 | 31 |
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
- Phishing
- Email Servers
- Microsoft Access
- Email Protocols
- Email Software
- The Email Laundry
Learn how to number pages in an Access report over each group.
Activate two pass printing by referencing the pages property:
Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Suggested Courses
Course of the Month2 days, 15 hours left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.