How Top N works when joining multiple tables (List the Top 5 Costs for Each Department)

Good day,

I have being trying to determine the Top 5 records for each of the Departments in the table; I keep researching but am willing to admit that I don’t understand how the Top N works when joining multiple table.  There are two tables and one subquery:

Tables:
tblNDCRelationships (4000 records)
  IEN
  UM_Cost

tblCHCS_Dispensed (100,000 records)
  IEN  
  QTY
  MEPRS – Location Field

subqryMEPRS_Identification (300 records)
  DEPARTMENT
  MEPRS

A Department has many MEPRS, my goal is to group the MEPRS by department and then get the Top 5 SubTotals for each of the Departments.

The following query gives me the information that I want, but it is including all records:

Access Database Query:

SELECT subqryMEPRS_Identification.DEPARTMENT, tblNDCRelationships.IEN, tblNDCRelationships.DRUG_LIST, Count(tblCHCS_Dispensed.RX_NUM) AS CountOfRX_NUM, Sum([tblCHCS_Dispensed.QTY]*[tblNDCRelationships.UM_Cost]) AS SubTotal

FROM (tblCHCS_Dispensed INNER JOIN tblNDCRelationships ON tblCHCS_Dispensed.DRUG_IEN = tblNDCRelationships.IEN) INNER JOIN subqryMEPRS_Identification ON tblCHCS_Dispensed.MEPRS = subqryMEPRS_Identification.MEPRS

GROUP BY subqryMEPRS_Identification.DEPARTMENT, tblNDCRelationships.IEN, tblNDCRelationships.DRUG_LIST, tblCHCS_Dispensed.[MMM-YY]

HAVING (((tblCHCS_Dispensed.[MMM-YY])="SEP-13"))

ORDER BY subqryMEPRS_Identification.DEPARTMENT, Sum([tblCHCS_Dispensed.QTY]*[tblNDCRelationships.UM_Cost]) DESC;
LVL 7
ImoutwestAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Try this:


SELECT q.* 
FROM
(SELECT subqryMEPRS_Identification.DEPARTMENT, tblNDCRelationships.IEN, tblNDCRelationships.DRUG_LIST, Count(tblCHCS_Dispensed.RX_NUM) AS CountOfRX_NUM, Sum([tblCHCS_Dispensed.QTY]*[tblNDCRelationships.UM_Cost]) AS SubTotal

FROM (tblCHCS_Dispensed INNER JOIN tblNDCRelationships ON tblCHCS_Dispensed.DRUG_IEN = tblNDCRelationships.IEN) INNER JOIN subqryMEPRS_Identification ON tblCHCS_Dispensed.MEPRS = subqryMEPRS_Identification.MEPRS

GROUP BY subqryMEPRS_Identification.DEPARTMENT, tblNDCRelationships.IEN, tblNDCRelationships.DRUG_LIST, tblCHCS_Dispensed.[MMM-YY]

HAVING (((tblCHCS_Dispensed.[MMM-YY])="SEP-13"))

ORDER BY subqryMEPRS_Identification.DEPARTMENT, Sum([tblCHCS_Dispensed.QTY]*[tblNDCRelationships.UM_Cost]) DESC) q
WHERE q.SubTotal
IN
(SELECT TOP 5 q2.SubTotal
FROM
(SELECT subqryMEPRS_Identification.DEPARTMENT, tblNDCRelationships.IEN, tblNDCRelationships.DRUG_LIST, Count(tblCHCS_Dispensed.RX_NUM) AS CountOfRX_NUM, Sum([tblCHCS_Dispensed.QTY]*[tblNDCRelationships.UM_Cost]) AS SubTotal

FROM (tblCHCS_Dispensed INNER JOIN tblNDCRelationships ON tblCHCS_Dispensed.DRUG_IEN = tblNDCRelationships.IEN) INNER JOIN subqryMEPRS_Identification ON tblCHCS_Dispensed.MEPRS = subqryMEPRS_Identification.MEPRS

GROUP BY subqryMEPRS_Identification.DEPARTMENT, tblNDCRelationships.IEN, tblNDCRelationships.DRUG_LIST, tblCHCS_Dispensed.[MMM-YY]

HAVING (((tblCHCS_Dispensed.[MMM-YY])="SEP-13"))

ORDER BY subqryMEPRS_Identification.DEPARTMENT, Sum([tblCHCS_Dispensed.QTY]*[tblNDCRelationships.UM_Cost]) DESC) q2 
WHERE q.Department = q2.Department)

Open in new window

0
ImoutwestAuthor Commented:
Thanks for the reply,

Access stops responding so I can't test, seems to be running then just locks up, suggestions?
0
mbizupCommented:
Look at the structure of the Top N records per Group query here:
http://allenbrowne.com/subquery-01.html

That's basically the idea... except since you have multiple tables, you would have to use one of two methods:

1.  Use a subquery in place of the Orders table in his article

OR

2.  Create a saved query from which you intend to draw the top 5 records per group, and use that query's name in place of the Orders table.

The hang up could be because of the large query that you posted, or I could be getting field and table names wrong.  

Can you post a sample database, with the relevant tables and queries?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ImoutwestAuthor Commented:
Thanks for your assistance. I've include a copy of the database after removing anything personal and the majority of the columns from each table.

Imoutwest
TestQuery.mdb
0
mbizupCommented:
Try this - it works syntactically... but it is hard to tell how fast it will be against your data (with the subqueries, I don't think it is going to be particularly fast.

I changed a couple of things which I thought might help performance.

SELECT q.* FROM 
(SELECT d.[MMM-YY], i.DEPARTMENT, r.IEN, r.DRUG_LIST, Sum(d.QTY * r.UM_Cost) AS SubTotal
FROM (tblCHCS_Dispensed d  INNER JOIN tblNDCRelationships r ON d.DRUG_IEN = r.IEN) INNER JOIN
(
SELECT DISTINCT DEPARTMENT, MEPRS
FROM tblMEPRS_Identification
) i ON d.MEPRS = i.MEPRS
WHERE  d.[MMM-YY] ="SEP-13"
GROUP BY d.[MMM-YY], i.DEPARTMENT, r.IEN, r.DRUG_LIST
ORDER BY i.DEPARTMENT, Sum(d.QTY * r.UM_Cost) DESC) q
WHERE q.SubTotal IN
(SELECT TOP 5 q2.SubTotal FROM 
(SELECT d.[MMM-YY], i.DEPARTMENT, r.IEN, r.DRUG_LIST, Sum(d.QTY * r.UM_Cost) AS SubTotal
FROM (tblCHCS_Dispensed d  INNER JOIN tblNDCRelationships r ON d.DRUG_IEN = r.IEN) INNER JOIN
(
SELECT DISTINCT DEPARTMENT, MEPRS
FROM tblMEPRS_Identification
) i ON d.MEPRS = i.MEPRS
WHERE  d.[MMM-YY] ="SEP-13"
GROUP BY d.[MMM-YY], i.DEPARTMENT, r.IEN, r.DRUG_LIST
ORDER BY i.DEPARTMENT, Sum(d.QTY * r.UM_Cost) DESC) q2 
WHERE q.DEPARTMENT = q2.DEPARTMENT)

Open in new window

0
mbizupCommented:
If that is still extremely slow, you could speed things up with a two-step approach.

1.  Through the query designer, turn your original query into a Make Table query.  Call the output table tblOutput (for example)

2.  Run a TOP N per Group query against the table that your query creates:

SELECT t.*
FROM tblOutput t
WHERE t.SubTotal IN
(SELECT TOP 5 q.SubTotal
FROM tblOutput  t2 
WHERE t.DEPARTMENT = t2.DEPARTMENT
ORDER BY t2.SubTotal DESC)
ORDER BY t.DEPARTMENT

Open in new window

0
ImoutwestAuthor Commented:
Good morning,

The other query still crashes my system (Not Responding) so I used the make table design.

In the last query is q.SubTotal correct?
Do I need to identify it somewhere because the sytem ask me to Enter Parameter Value for q.SubTotal

Thank you for your assistance.
imoutwest
0
mbizupCommented:
No.. that was aircode.

Try this instead:

SELECT t.*
FROM tblOutput t
WHERE t.SubTotal IN
(SELECT TOP 5 t2.SubTotal
FROM tblOutput  t2 
WHERE t.DEPARTMENT = t2.DEPARTMENT
ORDER BY t2.SubTotal DESC)
ORDER BY t.DEPARTMENT

Open in new window


Again aircode - I don't have your database handy.  That q.SubTotal was definitely a problem though... copy/paste issue.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ImoutwestAuthor Commented:
Really close, it selects 5 highest subtotals, but it doesn't sort them.  It does sort the Departments A-Z, but the Subtotal has no specific order.
0
mbizupCommented:
Tack ", t.subtotal". to the end of the last order by clause.
0
ImoutwestAuthor Commented:
Exactly what I wanted, now I just have to figure it out so I can duplicate it in the future.  Thank you for your assistance, imoutwest
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.