Aggregating values, then finding the top 5

[Guest]

I have 25,000+ lines of sanitized medical claims data in an excel file. I
have imported into an access table, and can do a lot of what I need, but not
all.

Many mbr ID’s (bogus, for security) have many, many claims, but with
different claim numbers, dates, etc. Each claim has a mbr id, a claim nbr, a
diagnosis, a date paid, and an amount paid. All this is in a single line
(record) but again, a sick member could have 30 - 40 lines associated with
the mbr id.

I would like to end up with a line which showed member ID, Total Paid, Last
Paid Date, and then below, (or beside) the top 5 highest paid claims for that
member with associated diagnosis and dates paid.

I thought the TOP function would help, but that seems to work at the record
level, not group (mbr id) level. I have also tried queries and sub-queries
and a lot of relational tables stemming from the main table. Would
appreciate any ideas.

Talkin 70, shootin 90

 

[Allen Browne]

You will need to do this in 2 steps.

First, a Totals query to aggregate the data.
Depress the Total button on the toolbar in query design.
Access adds a Total row to the design grid.
Under the MemberID field, accept Group By in the total row.
Under the Amount field, choose Sum.
Under the date field, choose Max.
Save the query.

Now create another query that shows the top 5 rows for each one. This will
involve a subquery. The subquery will use TOP 5. Details in:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Talkin'70-Shootin'90" <Talkin'70-Shootin'(e-mail address removed)>
wrote in message
news:[email protected]...

 

[Guest]

Thank you for the help. I have tried for over a week now, and still can't
get the darn thing to work. I don't think I explained my self very well,
upon reading my orig post.

I am after a sum of all the claims per person. That is done.

Now, I want to get 5 lines of data on each person, with four fields: MbrID,
NetPaid, Date Paid, and Diagnosis. I seem to be getting the top 5 fields
summed when I try to do what your instructions said. I have checked lots of
books, and on line, and can't find a way. More detailed explanation for
newbie would be appreciated immensely.

 

[louisjohnphillips]

Thank you for the help. I have tried for over a week now, and still can't
get the darn thing to work. I don't think I explained my self very well,
upon reading my orig post.

I am after a sum of all the claims per person. That is done.

Now, I want to get 5 lines of data on each person, with four fields: MbrID,
NetPaid, Date Paid, and Diagnosis. I seem to be getting the top 5 fields
summed when I try to do what your instructions said. I have checked lots of
books, and on line, and can't find a way. More detailed explanation for
newbie would be appreciated immensely.








- Show quoted text -

An Access report with subreport would satisfy your requirements.
A single query may be possible but it would be difficult to explain to
your users. Instead, try to break the problem into its subcomponents.

The main report will driven by a query like this:

SELECT A.MemberID, A.TotalPaid, B.DatePaid
from
(
SELECT MemberID, Sum( AmountPaid ) as TotalPaid
FROM ClaimsData
GROUP BY MemberID
) as A,
(
SELECT T.MemberID, T.DatePaid
from ClaimsData as T
where DatePaid = ( SELECT Max( DatePaid )
from ClaimsData
where MemberID = T.MemberID )
GROUP BY T.MemberID, T.DatePaid
) as B
WHERE A.MemberID = B.MemberID

This query provides one row for each MemberID, the total amount paid
for that member, and the last date paid for that member.

The sub report will be driven by a query like this:

SELECT Top 5 MemberID, AmountPaid, DatePaid, Diagnosis, ClaimNumber
from ClaimsData
ORDER BY AmountPaid

The Parent report and the Child report will be linked by MemberID.
For each row the parent query produces, it will call upon the
subreport to produce the top 5 rows for that MemberID.

Since I do not have your data, I have not tested this solution.
However, I think this is a good starting point.

 

[Guest]

Thank you very much.

An Access report with subreport would satisfy your requirements.
A single query may be possible but it would be difficult to explain to
your users. Instead, try to break the problem into its subcomponents.

The main report will driven by a query like this:

SELECT A.MemberID, A.TotalPaid, B.DatePaid
from
(
SELECT MemberID, Sum( AmountPaid ) as TotalPaid
FROM ClaimsData
GROUP BY MemberID
) as A,
(
SELECT T.MemberID, T.DatePaid
from ClaimsData as T
where DatePaid = ( SELECT Max( DatePaid )
from ClaimsData
where MemberID = T.MemberID )
GROUP BY T.MemberID, T.DatePaid
) as B
WHERE A.MemberID = B.MemberID

This query provides one row for each MemberID, the total amount paid
for that member, and the last date paid for that member.

The sub report will be driven by a query like this:

SELECT Top 5 MemberID, AmountPaid, DatePaid, Diagnosis, ClaimNumber
from ClaimsData
ORDER BY AmountPaid

The Parent report and the Child report will be linked by MemberID.
For each row the parent query produces, it will call upon the
subreport to produce the top 5 rows for that MemberID.

Since I do not have your data, I have not tested this solution.
However, I think this is a good starting point.