Locate item based on comparison of one value across a set of entries

So what I'm trying to do is find the listing (row) for each DRUG_CODE that has the largest SumOfTRANS_QTY and its corresponding other field (PRN, SCHEDULED, UNKNOWN).

I'd take suggestions on the title for this question as well, I just wasn't sure what nomenclature to use.
locate-pair-of-values-on-criteri.png
markloessiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Jon JaquesInformation TechnologistCommented:
This data is in Access? I think I'd be looking to group on the drug code, and then simply display only the rows having the largest sum. It should be possible to make a single SQL statement that would produce the records you're looking for.
0
markloessiAuthor Commented:
Yes data is in Access.

I'd agree some how grouping but with a criteria of largest SumOfTRANS_QTY might make sense, I'm just not sure how to do a grouping of that nature.
0
Jon JaquesInformation TechnologistCommented:
I can produce this SQL, but will have to chew on it for a few... It's definitely just a matter of grouping the data on the drug code, which will make it so that it only displays on record for each drug code, so then, probably with a descending sort, making sure that only the highest value is shown.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

peter57rCommented:
To get the max Value for each each drug you create a query(qry1)..

Select drug_code, max(sumoftrans) as mtrans from tablename

Then join this to the original table..

Select * from tablename as t inner join qry1
on t.drug_name = qry1.drug_name and t.sumoftrans = qry1.mtrans)

You can use a single sql query if you want...

Select * from tablename as t inner join
(Select drug_code, max(sumoftrans) as mtrans from tablename) as  qry1
on t.drug_name = qry1.drug_name and t.sumoftrans = qry1.mtrans)

0
markloessiAuthor Commented:
I was able to get the first bit working like this:
Select DRUG_CODE, GENERIC_NAME, max(SumOfTRANS_QTY) as mtrans from [PHM_CURES_6MONTHS - 3TEL MASSAGED]
GROUP BY DRUG_CODE, GENERIC_NAME

The second bit is struggling:
Select * from [PHM_CURES_6MONTHS - 3TEL MASSAGED] as t inner join
(Select DRUG_CODE, max(SumOfTRANS_QTY) as mtrans from [PHM_CURES_6MONTHS - 3TEL MASSAGED]) as  qry1
on (t.DRUG_CODE = qry1.DRUG_CODE and t.SumOfTRANS_QTY = qry1.mtrans)

BTW there was a missing open paren so I took a stab at where it goes.

When I run the second query I get "You tried to execute a query that does not include the specific expresstion 'DRUG_CODE' as part of an aggregate function" I tried a number of things with no luck.
0
peter57rCommented:
Sorry the typo was an extra )
And I missed out a bit while copying from my work area.

Your version should be..


Select * from [PHM_CURES_6MONTHS - 3TEL MASSAGED] as t inner join
(Select DRUG_CODE, max(SumOfTRANS_QTY) as mtrans from [PHM_CURES_6MONTHS - 3TEL MASSAGED] group by Drug_code) as  qry1
on (t.DRUG_CODE = qry1.DRUG_CODE and t.SumOfTRANS_QTY = qry1.mtrans)

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
markloessiAuthor Commented:
OK, that's working nicely.

Is there are way to combine the 3 columns PRN, SCHEDULED, UKNOWN into one column called DISP_METHOD that would clean things up a bit for my report. In the grouping there will never be a time when any row will have but one value in any of the 3.
COMBINE-TO-ONE-COLUMN.png
0
peter57rCommented:
If you are sure only one field will have a value then include another column in your query as...

Select *,  PRN &  SCHEDULED & UKNOWN as Disp_method  from [PHM
0
markloessiAuthor Commented:
As noted, great stuff here. And I appreciate the follow on question.

And Yes there will only ever be one option across the 3 fields.
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.