Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select Max query 1

Status
Not open for further replies.

DajTwo

Technical User
Jul 17, 2008
157
US
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.
 
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
 
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.
 
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.
 
A and B are alias.
Did my suggestion works ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
PHV has created aliases for the tables to make it a little easier on the wrist, but mainly to use the sub-select:
Code:
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
Code:
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
Code:
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
David W. Fenton said:
We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.
 
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
 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top