Select Latest Records

D

David Poweleit

Is there a way to setup a query on a subtable to return
only one record per each primary key in the main table.
Example: main table is for parts, sub table keeps track of
updates on parts... would like a query to pull by the
latest date the update for each part. I have tried many
things including a query on the subtable for updates using
Max, DMax, and DLookup to try to limit the date field.
Using Top Values does not work since it applies to the
whole query versus per part. Thanks!
 
J

John Spencer (MVP)

Yes, there is, but it would help to know your data structure. You can use a
subquery or a separate query. The Query with a Sub-query would look somethng
like the following.

SELECT M.PartPK, S.*
FROM MainTable as M INNER JOIN SubTable As S
ON M.PartPK = S.PartPK
WHERE S.Update =
(SELECT Max(Update)
FROM SubTable as Tmp
WHERE S.PartPk = Tmp.PartPK)

OR build a query that returns the Max(date) and Part# based on the subtable.
Then use that saved query and join it to a the subtable on the MaxDate and the
the PartNumber. Add your maintable in and join it to the SubTable and you
should be in business. This query WILL NOT be updatable.
 
D

Dave

Here is a sample for a database a library might use
(sample database sent to John).

Table.books
bookID (primary key)
title
Table.bookcheck (subtable to books)
checkID (primary key)
bookID (relation to book table)
checkout (date field)
name (person who checked out the book)

I would like a query to return the last time a book was
checked out along with who checked it out. In other
words, a filter on bookcheck that returns only one record
based upon the latest date per bookID.
 
J

John Spencer (MVP)

I think that one possible query would be the following.

SELECT Books.BookID, Books.Title,
BookCheck.Checkout, BookCheck.[Name]
FROM Books INNER JOIN BookCheck
On Books.BookId = BookCheck.BookID
WHERE BookCheck.Checkout =
(SELECT Max(Tmp.CheckOut)
FROM BookCheck as Tmp
WHERE Tmp.BookId = Books.BookID)

Another way to do this.

SELECT BooksCheck.BookID, MAX(BookCheck) as LatestDate
FROM BookCheck
GROUP BY BookID

Save that as qMaxCheckout

Now use that query in another query. The SQL would look something like:

SELECT Books.BookID, Books.Title,
BookCheck.Checkout, BookCheck.[Name]
FROM (Books INNER JOIN BookCheck
On Books.BookId = BookCheck.BookID)
INNER JOIN qMaxCheckout as Q
 
D

Dave

Thanks! I will give it a go!
-----Original Message-----

I think that one possible query would be the following.

SELECT Books.BookID, Books.Title,
BookCheck.Checkout, BookCheck.[Name]
FROM Books INNER JOIN BookCheck
On Books.BookId = BookCheck.BookID
WHERE BookCheck.Checkout =
(SELECT Max(Tmp.CheckOut)
FROM BookCheck as Tmp
WHERE Tmp.BookId = Books.BookID)

Another way to do this.

SELECT BooksCheck.BookID, MAX(BookCheck) as LatestDate
FROM BookCheck
GROUP BY BookID

Save that as qMaxCheckout

Now use that query in another query. The SQL would look something like:

SELECT Books.BookID, Books.Title,
BookCheck.Checkout, BookCheck.[Name]
FROM (Books INNER JOIN BookCheck
On Books.BookId = BookCheck.BookID)
INNER JOIN qMaxCheckout as Q
Here is a sample for a database a library might use
(sample database sent to John).

Table.books
bookID (primary key)
title
Table.bookcheck (subtable to books)
checkID (primary key)
bookID (relation to book table)
checkout (date field)
name (person who checked out the book)

I would like a query to return the last time a book was
checked out along with who checked it out. In other
words, a filter on bookcheck that returns only one record
based upon the latest date per bookID.
query
would look somethng the
SubTable and you track
of
.
 
D

dave

Didn't work for me. In addtion to returning the details
for checking out on the latest date, it appears to return
records that have a Checkout date that matches the Max
(Checkout) for another record. Any other thoughts?
-----Original Message-----
Thanks! I will give it a go!
-----Original Message-----

I think that one possible query would be the following.

SELECT Books.BookID, Books.Title,
BookCheck.Checkout, BookCheck.[Name]
FROM Books INNER JOIN BookCheck
On Books.BookId = BookCheck.BookID
WHERE BookCheck.Checkout =
(SELECT Max(Tmp.CheckOut)
FROM BookCheck as Tmp
WHERE Tmp.BookId = Books.BookID)

Another way to do this.

SELECT BooksCheck.BookID, MAX(BookCheck) as LatestDate
FROM BookCheck
GROUP BY BookID

Save that as qMaxCheckout

Now use that query in another query. The SQL would look something like:

SELECT Books.BookID, Books.Title,
BookCheck.Checkout, BookCheck.[Name]
FROM (Books INNER JOIN BookCheck
On Books.BookId = BookCheck.BookID)
INNER JOIN qMaxCheckout as Q
Here is a sample for a database a library might use
(sample database sent to John).

Table.books
bookID (primary key)
title
Table.bookcheck (subtable to books)
checkID (primary key)
bookID (relation to book table)
checkout (date field)
name (person who checked out the book)

I would like a query to return the last time a book was
checked out along with who checked it out. In other
words, a filter on bookcheck that returns only one record
based upon the latest date per bookID.

-----Original Message-----
Yes, there is, but it would help to know your data
structure. You can use a
subquery or a separate query. The Query with a Sub- query
would look somethng
like the following.

SELECT M.PartPK, S.*
FROM MainTable as M INNER JOIN SubTable As S
ON M.PartPK = S.PartPK
WHERE S.Update =
(SELECT Max(Update)
FROM SubTable as Tmp
WHERE S.PartPk = Tmp.PartPK)

OR build a query that returns the Max(date) and Part#
based on the subtable.
Then use that saved query and join it to a the subtable
on the MaxDate and the
the PartNumber. Add your maintable in and join it to the
SubTable and you
should be in business. This query WILL NOT be updatable.

David Poweleit wrote:

Is there a way to setup a query on a subtable to return
only one record per each primary key in the main table.
Example: main table is for parts, sub table keeps track
of
updates on parts... would like a query to pull by the
latest date the update for each part. I have tried many
things including a query on the subtable for updates
using
Max, DMax, and DLookup to try to limit the date field.
Using Top Values does not work since it applies to the
whole query versus per part. Thanks!
.
.
.
 
D

dave

Correction... tried it again and looked at the data...
some of the data had a couple of entries with the same
date; thus, it caused duplicate records with the same max
date. Thanks!
-----Original Message-----
Didn't work for me. In addtion to returning the details
for checking out on the latest date, it appears to return
records that have a Checkout date that matches the Max
(Checkout) for another record. Any other thoughts?
-----Original Message-----
Thanks! I will give it a go!
-----Original Message-----

I think that one possible query would be the following.

SELECT Books.BookID, Books.Title,
BookCheck.Checkout, BookCheck.[Name]
FROM Books INNER JOIN BookCheck
On Books.BookId = BookCheck.BookID
WHERE BookCheck.Checkout =
(SELECT Max(Tmp.CheckOut)
FROM BookCheck as Tmp
WHERE Tmp.BookId = Books.BookID)

Another way to do this.

SELECT BooksCheck.BookID, MAX(BookCheck) as LatestDate
FROM BookCheck
GROUP BY BookID

Save that as qMaxCheckout

Now use that query in another query. The SQL would
look
something like:
SELECT Books.BookID, Books.Title,
BookCheck.Checkout, BookCheck.[Name]
FROM (Books INNER JOIN BookCheck
On Books.BookId = BookCheck.BookID)
INNER JOIN qMaxCheckout as Q
ON BookCheck.BookID = Q.BookID
AND Bookcheck.CheckOut = Q.LatestDate



Dave wrote:

Here is a sample for a database a library might use
(sample database sent to John).

Table.books
bookID (primary key)
title
Table.bookcheck (subtable to books)
checkID (primary key)
bookID (relation to book table)
checkout (date field)
name (person who checked out the book)

I would like a query to return the last time a book was
checked out along with who checked it out. In other
words, a filter on bookcheck that returns only one record
based upon the latest date per bookID.

-----Original Message-----
Yes, there is, but it would help to know your data
structure. You can use a
subquery or a separate query. The Query with a Sub- query
would look somethng
like the following.

SELECT M.PartPK, S.*
FROM MainTable as M INNER JOIN SubTable As S
ON M.PartPK = S.PartPK
WHERE S.Update =
(SELECT Max(Update)
FROM SubTable as Tmp
WHERE S.PartPk = Tmp.PartPK)

OR build a query that returns the Max(date) and Part#
based on the subtable.
Then use that saved query and join it to a the subtable
on the MaxDate and the
the PartNumber. Add your maintable in and join it
to
the
SubTable and you
should be in business. This query WILL NOT be updatable.

David Poweleit wrote:

Is there a way to setup a query on a subtable to return
only one record per each primary key in the main table.
Example: main table is for parts, sub table keeps track
of
updates on parts... would like a query to pull by the
latest date the update for each part. I have
tried
many
things including a query on the subtable for updates
using
Max, DMax, and DLookup to try to limit the date field.
Using Top Values does not work since it applies to the
whole query versus per part. Thanks!
.

.
.
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top