Max value in Query

T

Ted

Hey all,
This is probably so simple but my mind is moosh right now. Can someone give
me a point in the right direction here? I'm trying to pull out all records
with the greatest Covered_Item_Limit. I've included two sample records w the
same policy number. For example I want to pull out the entire record that
has the greatest Covered_Item_Limit which in this case is the record with
400000. When I use the Group By Query and pull out the Max value it pulls
both records bc the Covered_Deductible_Amount is different.

Any help would be very much appreciated. I have to get home and get some
sleep...

Policy_Number Covered_Cause_of_Loss Covered_Item_ISO_Class_Code
Covered_Item_Limit Coverage_Deductible_Amount
RSA000070-07-00 Special 0542 400000 1,000
RSA000070-07-00 Special 0542 50000 2,500


TIA
Ted
 
C

Conan Kelly

Ted,

See if this will work for you:

SELECT Policy_Number, Covered_Cause_of_Loss, Covered_Item_ISO_Class_Code,
Covered_Item_Limit, Coverage_Deductible_Amount
FROM [YourTableName]
WHERE Covered_Item_Limit = (SELECT MAX(Covered_Item_Limit) FROM
[YourTableName])

I haven't tested this. If it doesn't work out of the box, it will need a
little work, but hopefully the format will get you goin' in the right
direction.

HTH,

Conan
 
T

Ted

Thank you Conan, that is definitely a step in the right direction. It works
but it only pulls out the Policy with the greatest limit. In my table I have
several records per policy and i need it to pull the record with the highest
limit per policy. With the below code i only get the policy with the
greatest limit. how can i pull it so i get one record per policy? Thanks
again for your help.

TIA
Ted

Conan Kelly said:
Ted,

See if this will work for you:

SELECT Policy_Number, Covered_Cause_of_Loss, Covered_Item_ISO_Class_Code,
Covered_Item_Limit, Coverage_Deductible_Amount
FROM [YourTableName]
WHERE Covered_Item_Limit = (SELECT MAX(Covered_Item_Limit) FROM
[YourTableName])

I haven't tested this. If it doesn't work out of the box, it will need a
little work, but hopefully the format will get you goin' in the right
direction.

HTH,

Conan





Ted said:
Hey all,
This is probably so simple but my mind is moosh right now. Can someone
give me a point in the right direction here? I'm trying to pull out all
records with the greatest Covered_Item_Limit. I've included two sample
records w the same policy number. For example I want to pull out the
entire record that has the greatest Covered_Item_Limit which in this case
is the record with 400000. When I use the Group By Query and pull out the
Max value it pulls both records bc the Covered_Deductible_Amount is
different.

Any help would be very much appreciated. I have to get home and get some
sleep...

Policy_Number Covered_Cause_of_Loss Covered_Item_ISO_Class_Code
Covered_Item_Limit Coverage_Deductible_Amount
RSA000070-07-00 Special 0542 400000 1,000
RSA000070-07-00 Special 0542 50000 2,500


TIA
Ted
Click to expand...
Click to expand...
 
G

Gary Walter

Hi Ted,

PMFBI

You need to "correlate" your subquery
back to the main query...maybe:

SELECT
Policy_Number,
Covered_Cause_of_Loss,
Covered_Item_ISO_Class_Code,
Covered_Item_Limit,
Coverage_Deductible_Amount
FROM [YourTableName] AS t1
WHERE
t1.Covered_Item_Limit =
(SELECT
MAX(t2.Covered_Item_Limit)
FROM
[YourTableName] AS t2
WHERE
t2.Policy_Number = t1.Policy_Number);

Every time you "are looking at" a record
in main query and deciding whether to return it,
see if it is the record with the max limit for
that policy number...

good luck,

gary

Ted said:
Thank you Conan, that is definitely a step in the right direction. It
works but it only pulls out the Policy with the greatest limit. In my
table I have several records per policy and i need it to pull the record
with the highest limit per policy. With the below code i only get the
policy with the greatest limit. how can i pull it so i get one record per
policy? Thanks again for your help.

TIA
Ted

Conan Kelly said:
Ted,

See if this will work for you:

SELECT Policy_Number, Covered_Cause_of_Loss, Covered_Item_ISO_Class_Code,
Covered_Item_Limit, Coverage_Deductible_Amount
FROM [YourTableName]
WHERE Covered_Item_Limit = (SELECT MAX(Covered_Item_Limit) FROM
[YourTableName])

I haven't tested this. If it doesn't work out of the box, it will need a
little work, but hopefully the format will get you goin' in the right
direction.

HTH,

Conan





Ted said:
Hey all,
This is probably so simple but my mind is moosh right now. Can someone
give me a point in the right direction here? I'm trying to pull out all
records with the greatest Covered_Item_Limit. I've included two sample
records w the same policy number. For example I want to pull out the
entire record that has the greatest Covered_Item_Limit which in this
case is the record with 400000. When I use the Group By Query and pull
out the Max value it pulls both records bc the Covered_Deductible_Amount
is different.

Any help would be very much appreciated. I have to get home and get some
sleep...

Policy_Number Covered_Cause_of_Loss Covered_Item_ISO_Class_Code
Covered_Item_Limit Coverage_Deductible_Amount
RSA000070-07-00 Special 0542 400000 1,000
RSA000070-07-00 Special 0542 50000 2,500


TIA
Ted
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

BTW, I personally don't think there is
anything wrong with "divide-and-conquer."

You can probably easily create an aggregate
query that groups by policy and finds max limit...

then join this query to your original table,
joining on policy and limit.

always works for me...

apologies again for butting in...

good luck,

gary
 
T

Ted

Thank you Gary...and no need to apologize for anything. I very much
appreciate your help. I tried that but I need to update fields in the
original table. It won't let me update the fields if I'm linking an
aggregate query to my table. Does that make sense?

My scenario: I may have 10 policies with 5 different coverages...each
coverage is a record(50 records). one of the coverages per policy does not
have the limit so i want to take the record with the highest limit and put
it in the record with no limit.
 
G

Gary Walter

Sure..now aggregation in WHERE clause
should not effect "updatability" so I would think
the non-divide-and-conquer might possibly work,
or you need to use DMAX() domain function...

let's see, what was it?

SELECT
Policy_Number,
Covered_Cause_of_Loss,
Covered_Item_ISO_Class_Code,
Covered_Item_Limit,
Coverage_Deductible_Amount
FROM [YourTableName] AS t1
WHERE
t1.Covered_Item_Limit =
(SELECT
MAX(t2.Covered_Item_Limit)
FROM
[YourTableName] AS t2
WHERE
t2.Policy_Number = t1.Policy_Number);


you can use Cartesion self-join to hide
aggregation in WHERE clause

"M" will be instance where limit is null

"P" will be instance with max limit that
we will assign to "M"

try this on copy of db to first verify if correct!!

UPDATE
yurtable AS M, yurtable AS P
SET
M.Covered_Item_Limit=P.Covered_Item_Limit
WHERE
(M.Covered_Item_Limit IS NULL)
AND
M.Policy_Number = P.Policy_Number
AND
P.Covered_Item_Limit =
(SELECT
Max(q.Covered_Item_Limit)
FROM
yurtable AS q
WHERE
q.Policy_Number = M.Policy_Number);

good luck,

gary
 
G

Gary Walter

On further thought...

I actually don't know why I thought
it *had* to be Cartesian join and why
inner join would not work...

the following still hides aggregate in WHERE clause...

UPDATE
yurtable AS M
INNER JOIN
yurtable AS P
ON
M.Policy_Number = P.Policy_Number
SET
M.Covered_Item_Limit=P.Covered_Item_Limit
WHERE
(M.Covered_Item_Limit IS NULL)
AND
P.Covered_Item_Limit =
(SELECT
Max(q.Covered_Item_Limit)
FROM
yurtable AS q
WHERE
q.Policy_Number = M.Policy_Number);

(also forgot to mention changing 'yurtable'
to your actual name of table)

good luck,

gary

Gary Walter said:
Sure..now aggregation in WHERE clause
should not effect "updatability" so I would think
the non-divide-and-conquer might possibly work,
or you need to use DMAX() domain function...

let's see, what was it?

SELECT
Policy_Number,
Covered_Cause_of_Loss,
Covered_Item_ISO_Class_Code,
Covered_Item_Limit,
Coverage_Deductible_Amount
FROM [YourTableName] AS t1
WHERE
t1.Covered_Item_Limit =
(SELECT
MAX(t2.Covered_Item_Limit)
FROM
[YourTableName] AS t2
WHERE
t2.Policy_Number = t1.Policy_Number);


you can use Cartesion self-join to hide
aggregation in WHERE clause

"M" will be instance where limit is null

"P" will be instance with max limit that
we will assign to "M"

try this on copy of db to first verify if correct!!

UPDATE
yurtable AS M, yurtable AS P
SET
M.Covered_Item_Limit=P.Covered_Item_Limit
WHERE
(M.Covered_Item_Limit IS NULL)
AND
M.Policy_Number = P.Policy_Number
AND
P.Covered_Item_Limit =
(SELECT
Max(q.Covered_Item_Limit)
FROM
yurtable AS q
WHERE
q.Policy_Number = M.Policy_Number);

good luck,

gary

Ted said:
Thank you Gary...and no need to apologize for anything. I very much
appreciate your help. I tried that but I need to update fields in the
original table. It won't let me update the fields if I'm linking an
aggregate query to my table. Does that make sense?

My scenario: I may have 10 policies with 5 different coverages...each
coverage is a record(50 records). one of the coverages per policy does
not have the limit so i want to take the record with the highest limit
and put it in the record with no limit.
Click to expand...
Click to expand...
 
C

Conan Kelly

apologies again for butting in...

If that was meant for me...no apologies necessary. We are all here to help.
I just got to work, so you were around before I was.

Conan.
 

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

pulling the a record by greatest value of a field 3
Query value between min-max and return another value 16
Aggregate query - Displaying field associated with MAX record 3
Max Field Value with Criteria 1
Slow Code/Query Execution 9
Program Error - MSAccess.exe has generated errors and will be closed by Windows 2

Top