Select Latest Records

  • Thread starter Thread starter David Poweleit
  • Start date Start date
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!
 
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.
 
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.
 
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
 
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
Click to expand...
.
Click to expand...
 
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!
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
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
Click to expand...
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
Click to expand...
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
Click to expand...
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!
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top