RE: Only Return Lastest Date From Records

Roger:

Yes it is SQL and it will show all the books in the result. To create the
query open the query designer in the usual way, but don't add any tables,
just close the 'Show Table' dialogue. Then select 'SQL View' from the View
menu. In the SQL window paste in the SQL from my message and edit it,
substituting your own table and field names where necessary. Don't change
the T1 or T2 aliases though, just the actual table or field names. Remember
than any table or field name which includes spaces or special characters like
the # etc need to be wrapped in square brackets [like this]. Save the query
as the name of your choice.

Switching to data*** view should give you one row for every book which is
represented in the Transactions table with its latest Date In value. If any
books from the Books table are not represented in the Transactions table,
then they won't be returned by the query of course, but should you want to
include them in the list just join the Books table to the query on Book ID in
another query, and change the join properties so that it 'includes ALL
records from Books and only those from Transactions where the joined fields
are equal'. This second query can be created entirely in design view; you
don't need to go into SQL view this time. This creates what's known as an
outer join. You can include whatever columns form the Books table and the
query that you wish to see in the final result, which you get simply by
opening the new query.

It is possible to do the entire thing entirely in design view. This
involves creating a query on the Transactions table which groups by Book ID
and returns the MAX(Date In) value per book. This gives you a list of each
Book ID with its latest Date In values. You can then join this query to the
Books and Transactions tables (or just to the Transactions table if you don't
want any fields from Books) in another query, joining the query to
Transactions on the BookID/BookID and Date In/MaxOfDate In fields. The more
elegant subquery approach would normally be used in this sort of situation,
however.

Ken Sheridan
Stafford, England

"Roger Tregelles" wrote:

Ken,

Thanks for the detailed reply. I'm assuming that your solution is written in
SQL and is not your standard query. I've never worked with SQL before in
Access. How would I go about using your text to contruct this query? Also,
will this only return the results on one book at a time? I need to be able to
query the entire collection of books and get only that one record per book
that is the latest to help calculate all of the checked in books aging. Any
further help you could provide me would be greatly appreciated. Thanks so
much.

Roger


"Ken Sheridan" wrote:

Roger;

Use a subquery to get the MAX Date In value from those rows in the
Transactions table (or whatever your referencing (child) table is called)
where the Book ID = the Book ID of the outer query's current row. By using
aliases T1 and T2 to differentiate between the two instances of the
Transactions table the subquery is correlated with the outer query, so, by
including the subquery in the outer query's WHERE clause, its rows are
restricted to those where the Date In is that returned by the subquery:

SELECT [Book ID], [Date Out],
Member, [Date In]
FROM Transactions As T1
WHERE [Date In] =
(SELECT MAX[Date In]
FROM Transactions AS T2
WHERE T2.[Book ID] = T1.[Book ID])
ORDER BY [Date In];

If you are using the query as the RecordSource for a report omit the ORDER
BY clause and sort the report using its internal sorting and grouping
mechanism. An ORDER BY clause in the query would be ignored and only slow
down performance.

Ken Sheridan
Stafford, England



.