Max Syntax

[northy]

I need to perform a query which to copies the SomeField field between Project Details and Project Details Copy for the project which is equal to the GetID() - just returns an integer - however, there are multiple versions of each project and I need to return the SomeField field for the latest (highest numerically) version.

The query looks like this:

DoCmd.RunSQL "INSERT INTO [Project Details Copy] (SomeField) SELECT [Project Details].[SomeField] FROM [PRS Project Details] WHERE (([Project Details].[Project ID])= GetID()) AND ([Project Details].[Version] = Maximum);", -1

I know that ([Project Details].[Version] = Maximum) is not the correct syntax...so what should I use instead?

Thanks


[This message has been edited by northy (edited 09-25-2001).]

 

[PearlGI]

Hi,

You'll need to break this down into 2 queries:

Query 1 (returns the latest version number of the chosen project):

SELECT [Project ID], Max([Version]) AS MaxVer
FROM [Project Details]
GROUP BY [Project ID]
HAVING [Project ID]=GetID();


Query 2 (then performs an INNER JOIN using Query 1 and [Project Details])

INSERT INTO [Project Details Copy].[{SomeFields}]
SELECT [Project Details].{SomeFields}
FROM [Query1] INNER JOIN [Project Details] ON [Query1].[MaxVer] = [Project Details].[Version] AND [Query1].[Project ID] = [Project Details].[Project ID];

HTH