Help with access queries!

[BD2003]

Ok, I have a giant guery that pulls out records from two tables. And in the query, theres a whole bunch of records, sorted by message IDs. Each message ID has about 50 records. And there are varying versions of each particular message set. Some have only version 1, some have version 1.05, 1.99 etc...

How can I construct the query to only pick out the highest version number of each message to put in the query?

 

[hoihtah]

can you give us some more details about these two table to work with?
you don't have to list out the whole field list... but only the necessary ones.

 

[Alphathree33]

SELECT Max (Whatever_Column) as Whatever_name FROM tablename;

 

[hoihtah]

Originally posted by: Alphathree33
SELECT Max (Whatever_Column) as Whatever_name FROM tablename;

i was asking for your field info... to avoid this.

but yeah... that sql command should work.

 

[Alphathree33]

Originally posted by: hoihtah

Originally posted by: Alphathree33
SELECT Max (Whatever_Column) as Whatever_name FROM tablename;

i was asking for your field info... to avoid this.

but yeah... that sql command should work.

Sue me.

 

[Mr.IncrediblyBored]

Originally posted by: Alphathree33
SELECT Max (Whatever_Column) as Whatever_name FROM tablename;

That's not going to work. That will return the highest value in the entire table for the particular column. He wants the highest value for each uniquely keyed record.

 

[Mr.IncrediblyBored]

Sorry, I don't have much experience with Access, and frankly I don't know how much flexibility they give you over the query. Here is how I would do this in either Oracle8i+ or SQL Server 2000:

select
t1.message_column,
(
select max(t2.version_column)
from table_name t2
where t1.message_column = t2.message_column
) as arbitrary_column_name
from table_name t1;

I don't know what, if any, version of Access supports nested selects - Access 2000 might.

 

[Mr.IncrediblyBored]

Soooo, did you get it to work?