Selects 1 of many, based on both group by and order by 2

[cdck]

I have a database to track the documents our company's Quality Department uses. The documents are kept in one table, and the revision information for the documents are kept in another (many revisions to one document). I need to be able to display the following on a form:

*All the documents for a department.
*The latest revision for each document.
*All the documents each document calls.
*The latest revision for each document called.

I have a datasheet view subform on the department form that shows all of the documents, along with their revisions. This subform then has another datasheet view subform which shows all of the documents each document calls, along with their revisions. At this point, these two subforms will show multiple entries for every document that has had multiple revisions. I need to weed it down to only the latest revisions.

I have tried to write a query that orders by the Rev field, descending, and then selects all of the document information for only the Top 1 of the result. This works fine, but it gives me exactly 1 entry out of the entire database. When I try to add a "Group by" to the SQL, the select statement no longer works.

Here is an example of what I am trying:

SELECT TOP 1 Revisions.revDoc, Revisions.Rev, Revisions.revDate, Revisions.revWhy, Documents.Doc, Documents.DocType, Documents.Name, Documents.File, Documents.FileType
FROM Documents INNER JOIN Revisions ON Documents.Doc = Revisions.revDoc
ORDER BY Revisions.Rev DESC,
GROUP BY [Revisions].[revDoc];

With this select statement, I get this error message:

Syntax error (missing operator) in query expression "GROUP BY [Revisions].[revDoc]"

The query works fine if I remove the GROUP BY line, but only gives me 1 item out of the entire database. In the subform, the result is that only that one document will show any revision information.

If I make the revision information into a subform, then there will be a conflict with the existing subform, which shows the documents atht each document calls.

Does anyone have any suggestions or tips? Is there another way to write the select statement?

Cheryl dc Kern

 

[PHV]

Create a saved query named, say, qryLastRev:
SELECT revDoc, Max(Rev) AS LastRev
FROM Revisions
GROUP BY revDoc;
And now your query:
SELECT R.revDoc, R.Rev, R.revDate, R.revWhy, D.Doc, D.DocType, D.Name, D.File, D.FileType
FROM (Revisions R INNER JOIN Documents D ON R.revDoc = D.Doc)
INNER JOIN qryLastRev L ON R.revDoc = L.revDoc AND R.Rev = L.LastRev;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lupins46]

I don't think I understand the issue properly but it sounds like you need to start by choosing the revision and then deal with the document afterwards.

Select revdoc, max(rev) from
Revisions
Group by revdoc

You then do a second query which joind this result to the Revisions file on the revdoc and rev fields. That will give you all the data from the highest rev record for each document.

You then join that query with the documents table to pull out any data from that table.

 

[cdck]

Ok, this plan is working wonderfully, with only 1 hitch. If the document was never revised, then no information at all is pulled about it. How can I get it to includerecords with nulls for revisions in the final output?

Cheryl dc Kern

 

[PHV]

SELECT R.revDoc, R.Rev, R.revDate, R.revWhy, D.Doc, D.DocType, D.Name, D.File, D.FileType
FROM Documents D LEFT JOIN (Revisions R INNER JOIN qryLastRev L ON R.revDoc = L.revDoc AND R.Rev = L.LastRev) ON D.Doc = R.revDoc;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cdck]

Beautiful, thanks!

Cheryl dc Kern