Select Max query 1

[DajTwo]

Hi all.

I have 3 fields in a table

[ccan]= Client number
[amount]= in $$
[div]= Division

For each CCAN, I need to select the Division where the amount is the largest using Access 2003 visual interface.
I simply cannot achieve the desired results as either the data is not selected or the results are simply wrong.

I also tried sorting [amount] 'Ascending' and select the 'First of [Div] without success.

Can someone point me in the right direction?

SELECT [ccan],Max([amount]) AS Exposure,[Div]
FROM 0010_S_01
GROUP BY [0010_S_01].ccan, [0010_S_01].[Div Selection];

Thanks



If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[PHV]

SELECT A.ccan, B.Exposure, A.Div
FROM 0010_S_01 AS A INNER JOIN (
SELECT ccan, Max(amount) AS Exposure FROM 0010_S_01 GROUP BY ccan
) AS B ON A.ccan = B.ccan AND A.amount = B.Exposure

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DajTwo]

Thanks for the reply.

I am really struggling in understanding your code but I keep trying..

Thanks



If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[DajTwo]

I really dont understnad the A. and B. I know they are to refer to tables but since I have only one, should I put it twice in the query?

AS B on A.ccan also I do not understand..

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[PHV]

A and B are alias.
Did my suggestion works ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[oharab]

PHV has created aliases for the tables to make it a little easier on the wrist, but mainly to use the sub-select:

SELECT A.ccan, B.Exposure, A.Div
FROM 0010_S_01 [b]AS A[/b] INNER JOIN (
[i]SELECT ccan, Max(amount) AS Exposure FROM 0010_S_01 GROUP BY ccan[/i]
) [b]AS B[/b] ON A.ccan = B.ccan AND A.amount = B.Exposure

Essentially the query reads:
Take the query

SELECT ccan, Max(amount) AS Exposure FROM 0010_S_01 GROUP

and treat it ias a virtual table which we will now call B, then INNER JOIN that to the table

0010_S_01

which we'll call A from now on.

You can't enter this sort of query directly using the visual editor, what you have to do is create a new query, then instead of adding new tables, change to the SQL view (using the view menu) and paste in PHV's code.

Let us know how you get on.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[dhookom]

DajTwo,
It would be appropriate to reply with
1) did you try the suggestion
2) what were the results
3) were these the expected/desired results

You can include the same table many times in a query. Each time you use the same table, it should be given a different name using an Alias.

Duane
Hook'D on Access
MS Access MVP

 

[DajTwo]

Yes it did work great.. thanks

Guess I need to do research on aliases..

Thanks again


If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[DajTwo]

I did not know that I could create the query directly or amend a query in the SQL window.. Sounds ridiculous when I think of it. I thought that it was either in VBA or by the visual interface.

Another thing learned.

Thanks again..



If I did not say it before, I really appreciate the time and expertise of the users on this forum.