Solved

Access 2000 - Top Nth function

Posted on 2002-03-27
4
579 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:bluezmasta
4 Comments
 
LVL 6

Accepted Solution

by:
xSinbad earned 50 total points
ID: 6901007
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
0
 
LVL 3

Expert Comment

by:SE081398
ID: 6901037
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.



0
 

Expert Comment

by:BarryBulsara
ID: 6902201
SELECT product.Unit_Number,max(log.Create_date) as YourDate  
FROM product INNER JOIN log ON product.Unit_Number = log.Unit_Number GROUP BY product.Unit_Number ORDER BY product.Unit_Number




0
 

Author Comment

by:bluezmasta
ID: 6903322
Thank you very much.  I must have been having a serious brain cramp.  Using max worked just fine.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

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

22 Experts available now in Live!

Get 1:1 Help Now