Only Return Lastest Date From Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database with a main table (parent) and sub-table (child) that
contain records of books checked in and out by date. The main table only has
the book ID, book name and some other basic fields. The sub-table contains
all of the transactions the book has seen (date checked out, library member,
date checked in) where the book ID is tied back to the main table. I need to
create a query that looks through all of the book's history and only returns
a single hit for each book checked in and then only the latest transaction.
Here's an example:

Book ID Date Out Member Date In
H34 12/1/00 J. Smith 3/5/01
H34 6/15/02 D.Jones 11/18/02
H34 3/15/03 R. Brown 9/28/03

I would want the query to only return the last transaction which had the
book being checked back in on 9/28/03. I want to use this query to determine
what books have been checked back in the longest and want to be featured to
the members. How can this be done? Any help, thoughts or ideas would be
greatly appreciated.

Roger
 
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
 
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
 
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 datasheet 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 said:
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 said:
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
Click to expand...
Click to expand...
 
Ken,

Thanks for the great and very detailed reply. Unfortunately I wont be able
to try it out until Wednesday (too late now and I'm out tomorrow night), but
I'll let you know how I make out with this. Can you recommend any good
reading on how to do SQL queries in Access? This seems a more powerful
method and I would like to learn more if I could. Let me know and thanks
again for your help.

Roger


p.s. - Both of my parents were from England (Cornwall and Burnham on Sea)
and I have an Uncle and many cousins who live there as well.
 
Roger:

I've not seen the book myself, but knowing one of the authors I'd imagine
that 'SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in
SQL' by Michael J. Hernandez and John L. Viescas would be a good one for
someone relatively new to SQL programming. Amazon currently list it at 24.64
USD. For more advanced techniques 'Joe Celko's SQL for Smarties: Advanced
SQL Programming Third Edition (The Morgan Kaufmann Series in Data Management
Systems)' by Joe Celko is worth its weight in gold. Amazon currently have it
at 32.97 USD.

Ken Sheridan
Stafford, England
 
Ken,

Thanks for the great references. I've already got the book "Database Design
For Mere Mortals" and liked it so I'm going to look into this other book by
Michael J. Hernandez and John L. Viescas (I have his new book Building
Access Applications).

I created the query last night per your instructions, but when I run it I
get the following error message:

Syntax error. in query expression '[DateIn] =
(SELECT MAX[DateIn]
FROM Transactions AS T2
WHERE T2.[BookID] = T1.[BookID])'.

Any idea what I'm doing wrong here? Do I need to make T1 the name of the
table (tblBooks) and T2 the name of that table (tblBookHistory)? Any help or
advice you could provide would be greatly appreciated. Thanks again for your
help with this.

Roger Tregelles
 
You are missing some parentheses in what you posted. Right after the word
MAX and before the word FROM.

[DateIn] =
(SELECT MAX([DateIn])
FROM Transactions AS T2
WHERE T2.[BookID] = T1.[BookID])

Just FYI, you don't need the [] in the above since your field and table
names consist of only letters and number characters (no spaces or other
"special" characters).
 
Roger:

You are missing the parentheses around the column name in:

MAX([DateIn])

Incidentally you don't actually need the square brackets of an object name
has no spaces or other special characters, so MAX(DateIn) would do. Most SQL
programmers avoid spaces in table or column names as some flavours of SQL
won't accept them at all. If a space is wanted then an underscore can be
used e.g. Employee_Name, but a lot of people, myself included, would use
CamelCase, EmployeeName. Some favour all lower case, employeename, for
column names and proper case Employees, for table names. The thing to be
avoided is upper case, EMPLOYEENAME, as it removes the distinction between
table/column names and SQL keywords, which is the normal convention, e.g.

SELECT EmployeeName
FROM Employees
ORDER BY Salary DESC;

Unfortunately, if you use functions like MAX, MIN etc Access puts these in
proper case if used in an outer query, but, as far as I can recall, keeps
them in upper case if you write them directly as SQL in a subquery.

Ken Sheridan
Stafford, England
 
Ken,

Thanks so much for your paitence with me on this. I must still be missing
something as I'm still getting and error. Here's the exact text as I typed
it into the SQL Query screen:

SELECT [BookID], [DateOut],
Member, [DateIn]
FROM Transactions As T1
WHERE [DateIn] =
SELECT MAX(DateIn)
FROM Transactions AS T2
WHERE T2.[BookID] = T1.[BookID]
ORDER BY [Date In]

When I execute thequery I still get an error message that states:

Syntax error. in query expression '[DateIn] =
SELECT MAX(DateIn)
FROM Transactions AS T2
WHERE T2.[BookID] = T1.[BookID]'.

Obviously I'm still doing something wrong here. What did I mess up now?
Thanks again for your help.

Roger
 
Roger:

I think you probably misunderstood me. You need parentheses around both the
column passed into the MAX function, AND around the whole subquery. Also you
need a terminating semi-colon:

SELECT [BookID], [DateOut],
Member, [DateIn]
FROM Transactions As T1
WHERE [DateIn] =
(SELECT MAX(DateIn)
FROM Transactions AS T2
WHERE T2.[BookID] = T1.[BookID])
ORDER BY [Date In];

BTW if using a query as the RecordSource of a report omit the ORDER BY
clause and use the report's internal sorting and grouping mechanism to sort
the result. The ORDER BY clause in the query would merely slow things down.

Ken Sheridan
Stafford, England
 
Ken,

Thanks it finally worked! The data looks good to me. After I run the query
and go back into SQL View it now looks like this.

SELECT T1.BookID, T1.DateOut, T1.Member, T1.DateIn
FROM tblBookHistory AS T1
WHERE (((T1.DateIn)=(SELECT MAX(DateIn)
FROM tblBookHistory AS T2
WHERE T2.[BookID] = T1.[BookID])))
ORDER BY [DateIn];

Is it normal that Access changes the layout slightly after you run it? Let
me know and untold thanks for all of your help with this ordeal I was
having.

Roger Tregelles
 
Back
Top