Select Latest Records

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!
.

.

.

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!
.

.

.

.