SQL HELP

[hermanko]

Hi,

Let's say I have the following table that contains historical data on
file info:


FileCode Name Version
01 Alpha 1
01 Alpha 2
01 Alpha 3
02 Bravo 1
03 Charlie 1
03 Charlie 2


I would like an SQL statement to select distinct FileCodes in a query
(i.e. 01, 02, 03), while selecting the most recent version of the Name,

and doesn't include version field, so that the resulting query looks
like this:


01 Alpha 3
02 Bravo 1
03 Charlie 2


I'm a beginner with SQL, so any help would be greatly appreciated!
Herman

 

[hermanko]

Sorry, I forgot that the Name fields are all unique. ie. the table
should be:

FileCode Name Version
01 Alpha1 1
01 Alpha2 2
01 Alpha3 3
02 Bravo1 1
03 Charlie1 1
03 Charlie2 2

and the resulting query should display:

FileCode Name Version
01 Alpha3 3
02 Bravo1 1
03 Charlie2 2

 

[Ofer Cohen]

Something like

SELECT FileCode , [Name] , Max([Version]) AS MaxOfVersion
FROM TableName
GROUP BY FileCode , [Name]

Version and Name are key words in Access, it's not recomnded using key words
as fields name, it can cause problems.

 

[hermanko]

Ok, sorry for the confusion but this is what I am trying to do.....

Table: tblDocList
File Code File Name Version
01 Alpha1 1
01 Alpha2 2
01 Alpha3 3
02 Bravo1 1
03 Charlie1 1
03 Charlie2 2

and the resulting query should display:

FileCode Name
01 Alpha3
02 Bravo1
03 Charlie2

I am complete at a loss...
Herman

 

[Ofer Cohen]

Try

Select [File Code] , [File Name] , [Version] From TableName
Where Version = Dmax("Version","TableName","[File Code]='" & [File Code] &
"'")