Selecting desired value in totals query

L

LGC

I have the following query:

SELECT CustID, ProdID, Sum(Amt) AS Prem
FROM tbl_dta_DivYearTran
GROUP BY CustID, ProdID
ORDER BY CustID, Sum(Amt) DESC;

Sample Output:
qry_sys_CustProd CustID ProdID Prem
BLUSAVE 5 $22,027.00
BOONBAN 2 $19,663.08
BREDSBK 4 $9,791.00
BRITFIR 5 $14,960.00
BRITFIR 4 $2,694.82
BROOPAT 5 $17,613.00
BROOPOW 5 $8,593.00
BRUNFSB 4 $28,936.00


This results in a data set of three columns: CustID (customer), ProdID
(producer), and Prem (premium). Almost all of the rows are unique except
for a few where there are two ProdID values for one CustID. I need to build
a data set containing unique rows of CustID and ProdID using the ProdID
where Prem is the greatest.

Here is my attempt:

SELECT CustID, First(ProdID) AS ProdID
FROM qry_sys_CustProd
GROUP BY CustID;

Sample Output:
Query1 CustID ProdID
BLUSAVE 5
BOONBAN 2
BREDSBK 4
BRITFIR 4
BROOPAT 5
BROOPOW 5
BRUNFSB 4


This does not work, though, as grouping by CustID in the 2nd query
apparently disrupts the order of the records from the first query. Notice
that the producer for BRITFIR is 4, not 5, which is the one with greatest
premium from the first query.

Any help is appreciated. Thanks.

LGC
 
M

Marshall Barton

LGC said:
I have the following query:

SELECT CustID, ProdID, Sum(Amt) AS Prem
FROM tbl_dta_DivYearTran
GROUP BY CustID, ProdID
ORDER BY CustID, Sum(Amt) DESC;

Sample Output:
qry_sys_CustProd CustID ProdID Prem
BLUSAVE 5 $22,027.00
BOONBAN 2 $19,663.08
BREDSBK 4 $9,791.00
BRITFIR 5 $14,960.00
BRITFIR 4 $2,694.82
BROOPAT 5 $17,613.00
BROOPOW 5 $8,593.00
BRUNFSB 4 $28,936.00


This results in a data set of three columns: CustID (customer), ProdID
(producer), and Prem (premium). Almost all of the rows are unique except
for a few where there are two ProdID values for one CustID. I need to build
a data set containing unique rows of CustID and ProdID using the ProdID
where Prem is the greatest.
Click to expand...
[]

I think this is what you want:

SELECT Q.CustID, Q.ProdID, Q.Prem
FROM qry_sys_CustProd As Q
WHERE Q.Prem = (SELECT Max(X.Prem)
FROM qry_sys_CustProd As X
WHERE X.CustID = Q.CustID)

Note that if two CustID, ProdIDs have the same Prem value,
they will both appear in the resulting dataset.
 
S

smw226 via AccessMonster.com

Hi LGC

Instead of First try max(sum(amt)) as perm.

I havn't tested it, but i think it should work.

Thanks,

Simon
 
S

smw226 via AccessMonster.com

Hi Again,

Good job I did test that as it didn't work...however, this should:

SELECT Q1.CustID, Max(Q1.Prem) AS MaxOfPrem
FROM
(SELECT CustID, ProdID, Sum(Amt) AS Prem
FROM tbl_dta_DivYearTran
GROUP BY CustID, ProdID) as Q1
GROUP BY Q1.CustID;

Incase its of interest, this uses your origional query but instead of getting
the data from a table, it pulls its data from a subquery

HTH

Simon

Marshall said:
I have the following query:
Click to expand...
[quoted text clipped - 19 lines]
a data set containing unique rows of CustID and ProdID using the ProdID
where Prem is the greatest.
Click to expand...
[]

I think this is what you want:

SELECT Q.CustID, Q.ProdID, Q.Prem
FROM qry_sys_CustProd As Q
WHERE Q.Prem = (SELECT Max(X.Prem)
FROM qry_sys_CustProd As X
WHERE X.CustID = Q.CustID)

Note that if two CustID, ProdIDs have the same Prem value,
they will both appear in the resulting dataset.
Click to expand...

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via AccessMonster.com
 
L

LGC

....
I think this is what you want:

SELECT Q.CustID, Q.ProdID, Q.Prem
FROM qry_sys_CustProd As Q
WHERE Q.Prem = (SELECT Max(X.Prem)
FROM qry_sys_CustProd As X
WHERE X.CustID = Q.CustID)

Note that if two CustID, ProdIDs have the same Prem value,
they will both appear in the resulting dataset.
Click to expand...

Yes, thank you. I was playing around with something like that but did not
rename the tables and so wasn't able to make the proper comparison.

LGC
 
Top