hello! I have a web application in ASP which manages files, driven
from an Access 2000 database. People upload files to the system for
workgroup collaboration on the web. It stores revisions of each file
in a revisions table, related to the documents table which stores
information about each parent item.

I am stuck on some SQL code...

For example, a proposal document may be uploaded to the revisions
table two or three times as the proposal goes through successive
changes until final. For users, there is only ever one 'document'
listed in the documents interface, but when they click on it to view
it, the most recent revision is downloaded. My problem is listing the
files with each linked to the most recent revision. I would like a SQL
query that will return a result set of documents together with the
most recent revisionID for each one; for example it would return:

documents.documentID, documents.documentTitle, revisions.revisionID,
revisions.fileName, revisions.fileDate

with one row for each record in documents. Assume there is always at
least one related item in revisions for each document. This is easy to
do if we want all the revisions to be listed, but we don't. We only
want the most recent revision for each documentID.

Can this be done entirely in SQL in Access 2000? I can write
ASP/VBSCRIPT code to take the recordsets and do the processing through
scripting, but for performance I would like it to be done in SQL and
just loop through the resulting recordset to build the ASP page
interface.

The two tables are:

documents
-----------
documentID (autonumber) PRIMARY KEY
documentTitle (text)
documentDescription (memo)
 
revisions
-----------
revisionID (autonumber) PRIMARY KEY
documentID  (number - integer) (foreign key to documents table)
revisionBlob (OLE wossname)
fileName (text)
fileSize (number - integer)
fileDate (date/time)
fileTime (date/time)

sample data...
-----------------------
documentID documentTitle
1                Smith Proposal
2                Jones Proposal

revisionID  documentID  fileName            fileDate
1	 1                 proposal1.doc    25/12/04
2	 1                 proposal2.doc    26/12/04
3	 2                 proposal3.doc    25/12/04
4	 2                 proposal4.doc    26/12/04

I have uploaded a sample database based on the above to 
http://zstudio.co.uk/project/project.mdb

If you can help with the SQL it would be greatly appreciated!

Create a view with the following

SELECT *
FROM revisions AS a
WHERE a.revisionID = (
SELECT MAX(b.revisionID)
FROM revisions AS b 
where a.documentID = b.documentID
);

thanks, funkyfiddler!

However, when I try it in the database, it doesn't quite work. There
are two documents, each with two revisions. It successfully returns
one revision for each document, which is way cool.

However, for the first document it returns the wrong revision. The
correct revisionID's are 1 and 4; the query as given returns 2 and 4.

In your SQL, why doesn't it refer to fileDate in the innermost SELECT?

Many, many thanks

Flubba

Funkyfiddler, I think it will work after all.
The reason the wrong answer comes out in my test database is that in
this revisions table, the revisions for document 1 are the wrong way
round. The date for the second revision is earlier than the first
revision, and since these dates will be generated by the system on
insertion, not entered by the user, this could not happen in the
production database.

Your query selects the Max(revisionID) rather than the Max(fileDate)
for what look like sound reasons; in production as I say, I think it
will work...

Thank you ever so much...!