Retriving the last date

[Todd]

I have a drawing log database, I am new to VBA,
I have one table that contains drawing numbers and is linked to another
table with revisions. One revision has many drawings. I want to make a
query that will give me only the latest versions of each drawing.

anyone please help!

 

[KARL DEWEY]

One revision has many drawings.
Do you mean "One drawing has many revisions.
SELECT Drawing.Number, Drawing.Title, Max(Revision.RevLetter) AS Rev,
Max(Revision.RevDate) AS MaxOfRevDate
FROM Drawing INNER JOIN Revision ON Drawing.DrawingID = Revision.DrawingID
GROUP BY Drawing.Number, Drawing.Title;

 

[Todd]

No actually I thought it would be easier to make one revision have many
drawings because it is faster to just type in the drawing numbers instead of
having to type in the revision description over and over on a sub form.
Unfortunatly I did not include the revision letter or number I see how that
would have helped my situation.
this is what I have now:

SELECT Drawing.DwgNumber, Revision.RevDate, Revision.RevDescription,
CCSJob.CCSJobTitle
FROM (CCSJob INNER JOIN Revision ON CCSJob.CCSJobID = Revision.CCSJobID)
INNER JOIN Drawing ON Revision.RevID = Drawing.RevID
WHERE (((CCSJob.CCSJobTitle)="Emory University Freshman Halls 2 & 3"))


I tried to do the Max(Revision.RevDate) AS MaxOfRevDate
but it gave me an error saying:
You tried to execute a query that does not include the specified expression
'DwgNumber' as part of an aggregate function.

 

[Todd]

Ok I figured out the group by deal and got rid of that message, but it still
does not return the results I need. It is still giveing me all the dates. Any
Ideas?