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?
 
peter57rConnect With a Mentor Commented:
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
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.