2nd highest

H

hughess7

Hi all

I have the following sql which returns the highest results of issues from
claims, please can you tell me how I would write another query to find the
2nd highest result?

SELECT [Dealer Claim Check Table].[NE Claim No], Max([Issue Codes].Result)
AS MaxOfResult
FROM [Current Review] INNER JOIN (([Dealer Claim Check Table] INNER JOIN
[Dealer Claim Check Issues] ON ([Dealer Claim Check Table].[NE Claim No] =
[Dealer Claim Check Issues].[NE Claim No]) AND ([Dealer Claim Check
Table].[Review Date] = [Dealer Claim Check Issues].[Review Date]) AND
([Dealer Claim Check Table].DEALER_CODE = [Dealer Claim Check
Issues].DEALER_CODE) AND ([Dealer Claim Check Table].[Country Code] = [Dealer
Claim Check Issues].[Country Code])) INNER JOIN [Issue Codes] ON [Dealer
Claim Check Issues].IssueCode = [Issue Codes].Code) ON ([Current
Review].[Review Date] = [Dealer Claim Check Table].[Review Date]) AND
([Current Review].[Dealer Code] = [Dealer Claim Check Table].DEALER_CODE) AND
([Current Review].CountryCode = [Dealer Claim Check Table].[Country Code])
GROUP BY [Dealer Claim Check Table].[NE Claim No];

Thanks in advance for any help.
Sue
 
K

KARL DEWEY

Change your query so the first part reads --
SELECT TOP 2 [Dealer Claim Check Table].[NE Claim No], [Issue Codes].Result
FROM...
and last reads --
ORDER BY [Issue Codes].Result DESC;

The use another query like this --
SELECT TOP 1 [Dealer Claim Check Table].[NE Claim No], [Issue Codes].Result
FROM FirstQueryName
ORDER BY [FirstQueryName].Result;
 
H

hughess7

Hi Karl

I changed as detailed but this did not work. I think it showed the top
results and corresponding NE Claim No's out of the WHOLE table, rather than
being grouped by NE Claim No first.

It needs to show every NE Claim No and the top results grouped on Issue
Codes...

ie NE Claim No 4813407 has the following results: 5, 4, 3
1234567 has 4, 4, 3

Grouped and sorted desc, the top 2 query needs to show:

4813407 5
4813407 4
1234567 4
1234567 3

ie it needs to group first on issues found with the same result so it can
then find the next desc value?


Thanks in advance for any help.
Sue


KARL DEWEY said:
Change your query so the first part reads --
SELECT TOP 2 [Dealer Claim Check Table].[NE Claim No], [Issue Codes].Result
FROM...
and last reads --
ORDER BY [Issue Codes].Result DESC;

The use another query like this --
SELECT TOP 1 [Dealer Claim Check Table].[NE Claim No], [Issue Codes].Result
FROM FirstQueryName
ORDER BY [FirstQueryName].Result;


hughess7 said:
Hi all

I have the following sql which returns the highest results of issues from
claims, please can you tell me how I would write another query to find the
2nd highest result?

SELECT [Dealer Claim Check Table].[NE Claim No], Max([Issue Codes].Result)
AS MaxOfResult
FROM [Current Review] INNER JOIN (([Dealer Claim Check Table] INNER JOIN
[Dealer Claim Check Issues] ON ([Dealer Claim Check Table].[NE Claim No] =
[Dealer Claim Check Issues].[NE Claim No]) AND ([Dealer Claim Check
Table].[Review Date] = [Dealer Claim Check Issues].[Review Date]) AND
([Dealer Claim Check Table].DEALER_CODE = [Dealer Claim Check
Issues].DEALER_CODE) AND ([Dealer Claim Check Table].[Country Code] = [Dealer
Claim Check Issues].[Country Code])) INNER JOIN [Issue Codes] ON [Dealer
Claim Check Issues].IssueCode = [Issue Codes].Code) ON ([Current
Review].[Review Date] = [Dealer Claim Check Table].[Review Date]) AND
([Current Review].[Dealer Code] = [Dealer Claim Check Table].DEALER_CODE) AND
([Current Review].CountryCode = [Dealer Claim Check Table].[Country Code])
GROUP BY [Dealer Claim Check Table].[NE Claim No];

Thanks in advance for any help.
Sue
Click to expand...
Click to expand...
 
J

John Spencer MVP

Use the results of the first query (saved) to filter out the max date and get
a new max date. I think that will look something like the following UNTESTED
query.

SELECT [Dealer Claim Check Table].[NE Claim No]
, Max([Issue Codes].Result) AS MaxResult
FROM ([Current Review]
INNER JOIN (([Dealer Claim Check Table]
INNER JOIN [Dealer Claim Check Issues]
ON ([Dealer Claim Check Table].[NE Claim No] =
[Dealer Claim Check Issues].[NE Claim No])
AND ([Dealer Claim Check Table].[Review Date] =
[Dealer Claim Check Issues].[Review Date])
AND ([Dealer Claim Check Table].DEALER_CODE =
[Dealer Claim Check Issues].DEALER_CODE)
AND ([Dealer Claim Check Table].[Country Code] =
[Dealer Claim Check Issues].[Country Code]))
INNER JOIN [Issue Codes]
ON [Dealer Claim Check Issues].IssueCode = [Issue Codes].Code)
ON ([Current Review].[Review Date] =
[Dealer Claim Check Table].[Review Date])
AND ([Current Review].[Dealer Code] =
[Dealer Claim Check Table].DEALER_CODE)
AND ([Current Review].CountryCode =
[Dealer Claim Check Table].[Country Code]))
INNER JOIN TheQuery
ON TheQuery.[NE CLAIM NO] = [Dealer Claim Check Table].[NE Claim No]
WHERE [Issue Codes].Result <> TheQuery.MaxOfResult
GROUP BY [Dealer Claim Check Table].[NE Claim No];



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
H

hughess7

Thanks very much John - just the result I wanted :).

What I now need to do is work out how to split payments between these two
levels found in a report.

[Dealer Claim Check Table] has paid amounts for Labour, Parts and Sublet. It
also has amounts for these as Partial payments. If a value exists in any of
these fields eg Partial_Labour, then the value stored in Partial_Labour (and
Partial_Parts and Partial_Sublet) are assigned to the highest level (found in
query [qry MaxIssueResults] and the remainder [Labour]-[Partial_Labour] etc
needs to be reported against the 2nd level (query [qry MaxIssueResults 2nd]).

If no Partial amounts found the full Paid amounts are used.

It is also possible a 2nd level might not exist and then the remainder is
automatically reported against a Level 0.

I have created a query which has all the amounts in and sorted the Paid
Amounts and Remainder amounts per claim, but I don't know how to assign these
values to the top 2 levels found (Pd highest level, remainder 2nd highest
level or 0 if none found)

Any ideas how I would approach this? I guess it might have to be done via
code as I am not sure I could do this with queries??

Sample data

NE Claim No: 123456

Labour 200 Parts 50 Sublet 5
Partial_Labour 0, Partial_Parts 0, Partial_Sublet 0

Paid Amounts = Labour 200 Parts 50 Sublet 5 against level 5

NE Claim No: 987654
Labour 100 Parts 500 Sublet 25
Partial_Labour 50, Partial_Parts 0, Partial_Sublet 0

Paid Amounts = Labour 50 Parts 0 Sublet 0 against level 4
AND
Paid Amounts = Labour 50 Parts 500 Sublet 25 against level 3

NE Claim No: 159753
Labour 250 Parts 0 Sublet 50
Partial_Labour 0, Partial_Parts 0, Partial_Sublet 50

Paid Amounts = Labour 0 Parts 0 Sublet 25 against level 4
AND
Paid Amounts = Labour 250 Parts 0 Sublet 25 against level 0

Thanks
Sue


John Spencer MVP said:
Use the results of the first query (saved) to filter out the max date and get
a new max date. I think that will look something like the following UNTESTED
query.

SELECT [Dealer Claim Check Table].[NE Claim No]
, Max([Issue Codes].Result) AS MaxResult
FROM ([Current Review]
INNER JOIN (([Dealer Claim Check Table]
INNER JOIN [Dealer Claim Check Issues]
ON ([Dealer Claim Check Table].[NE Claim No] =
[Dealer Claim Check Issues].[NE Claim No])
AND ([Dealer Claim Check Table].[Review Date] =
[Dealer Claim Check Issues].[Review Date])
AND ([Dealer Claim Check Table].DEALER_CODE =
[Dealer Claim Check Issues].DEALER_CODE)
AND ([Dealer Claim Check Table].[Country Code] =
[Dealer Claim Check Issues].[Country Code]))
INNER JOIN [Issue Codes]
ON [Dealer Claim Check Issues].IssueCode = [Issue Codes].Code)
ON ([Current Review].[Review Date] =
[Dealer Claim Check Table].[Review Date])
AND ([Current Review].[Dealer Code] =
[Dealer Claim Check Table].DEALER_CODE)
AND ([Current Review].CountryCode =
[Dealer Claim Check Table].[Country Code]))
INNER JOIN TheQuery
ON TheQuery.[NE CLAIM NO] = [Dealer Claim Check Table].[NE Claim No]
WHERE [Issue Codes].Result <> TheQuery.MaxOfResult
GROUP BY [Dealer Claim Check Table].[NE Claim No];



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi all

I have the following sql which returns the highest results of issues from
claims, please can you tell me how I would write another query to find the
2nd highest result?

SELECT [Dealer Claim Check Table].[NE Claim No], Max([Issue Codes].Result)
AS MaxOfResult
FROM [Current Review] INNER JOIN (([Dealer Claim Check Table] INNER JOIN
[Dealer Claim Check Issues] ON ([Dealer Claim Check Table].[NE Claim No] =
[Dealer Claim Check Issues].[NE Claim No]) AND ([Dealer Claim Check
Table].[Review Date] = [Dealer Claim Check Issues].[Review Date]) AND
([Dealer Claim Check Table].DEALER_CODE = [Dealer Claim Check
Issues].DEALER_CODE) AND ([Dealer Claim Check Table].[Country Code] = [Dealer
Claim Check Issues].[Country Code])) INNER JOIN [Issue Codes] ON [Dealer
Claim Check Issues].IssueCode = [Issue Codes].Code) ON ([Current
Review].[Review Date] = [Dealer Claim Check Table].[Review Date]) AND
([Current Review].[Dealer Code] = [Dealer Claim Check Table].DEALER_CODE) AND
([Current Review].CountryCode = [Dealer Claim Check Table].[Country Code])
GROUP BY [Dealer Claim Check Table].[NE Claim No];

Thanks in advance for any help.
Sue
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Selecting data 2
Type Mismatch 5
Creating data via code 12
left join not returning enough records 3
Prevent further inserts in subform 3
requery result 1
Finding the Date nearest a given date 1
Capturing mandatory data 6

Top