Help- Need Maximum Record

[Guest]

I link to a table in an Oracle database to run queries. There is a table that
records every application an employee submits for a job, so a single employee
could have multiple applications. The table has a field called ProfileSeq
which sequencially numbers each employees application. Example:

EmployeeID ProfileSeq
------------- -----------
1 1
1 2
1 3
2 1
3 1
4 1
4 2

What I want returned in the query is just the row that has the maximum
ProfileSeq values, so the results would be:

EmployeeID ProfileSeq
------------- -----------
1 3
2 1
3 1
4 2

I realize this probably seems simple, but my brains not pulling it out right
now.

Thanks in advance for any help,
Joe

 

[Marshall Barton]

I link to a table in an Oracle database to run queries. There is a table that
records every application an employee submits for a job, so a single employee
could have multiple applications. The table has a field called ProfileSeq
which sequencially numbers each employees application. Example:

EmployeeID ProfileSeq
------------- -----------
1 1
1 2
1 3
2 1
3 1
4 1
4 2

What I want returned in the query is just the row that has the maximum
ProfileSeq values, so the results would be:

EmployeeID ProfileSeq
------------- -----------
1 3
2 1
3 1
4 2

Not sure if Oracle is a little different, but an Access
query would be:

SELECT EmployeeID, Max(ProfileSeq) As MaxProfileSeq
FROM thetable
GROUP BY EmployeeID

 

[Guest]

Ok, that looks like it should work, but here's the other thing, I'm not
fluent in SQL, I can semi-understand it, but how would I be able to enter
this in the Query Design view?

 

[Marshall Barton]

One way would be to open a new query, switch to SQL view and
paste the SQL statement over whatever is already there.
Then switch to design view and look at the way it is done
there.

If you feel more comfortable starting in design view, then
use the View menu item Totals. Thenm in the Totals rowm
select Group By for the EmployeeID field and Max for the
ProfileSeq field.