Getting min values via sql

[salvelinus]

A coworker has an Access table named HCOM set up like so:
Code - text, three letter client code
Question - text
Module - text
Value - double

and another table named Client set up:
Code - text, three letter client code
Name - text, client name matching the code

In HCOM, there's an entry for each client code for each question and module if that question is asked for that module, and these entries have a value entry. So some entries might look like this:

[U]Code[/U]    [u]Question[/u]   [u]Module[/u]   [u]Value[/u]
ABC        Q1         A        0.0
ABC        Q2         B        2.6
ABC        Q5         D        0.0
XYZ        Q1         A        0.0
XYZ        Q2         B        1.7
XYZ        Q4         C        0.6

I want a query that will return the minimum values, including duplicates if necessary, for each question, preferably matching code with client name from the Client table, results from above would be:

[U]Code[/U]    [u]Question[/u]   [u]Module[/u]   [u]Value[/u]   [u]Name[/u]
ABC        Q1         A     0.0      A Best Crew
XYZ        Q1         A     0.0      X You Zay
XYZ        Q2         B     1.7      X You Zay
XYZ        Q4         C     0.6      X You Zay
ABC        Q5         D     0.0      A Best Crew

Thanks.

[szlamany]

Select * from HCom HC
Where Value=(Select Min(Value) From HCom HC2 Where HC2.Code=HC.Code)

[salvelinus]

Thanks, but that doesn't quite do it. That gives me a list of every question/module combination for every client, not just the ones with minimum values. It does show the value column, but they all read zero, presumably because that's the lowest value without taking into account anything else.

[-TPM-]

I think this is what you want:
SELECT MIN(VALUE) FROM HCOM GROUPBY MODULE

TPM

[salvelinus]

Not exactly, but thanks. I'll try to be more clear.
There's probably about 90 distinct questions. Any particular client will have a subset of those, and questions may be asked for some client/module combinations and not others. These records all have a value (this is imported from Excel stuff I don't deal with).
What I want is the minimum value for each question/module combination, including duplicates, if any.
Fo example, Q1 might be asked of 30 clients, and each client might be asked it for 1 to 10 modules. Some clients may not get it at all. For every module that asks Q1, I'd like to return the data for the record(s) that have only the minimum value for that module and question.
Think of clients as businesses, and modules as departments. Employees give a y/n rating to various questions, and the percentage of n's is calculated - this is the value, and is done elsewhere.
Employer1 may have departments for secretaries, managers and machinists. Employer2 may have departments for secretaries, salaried staff, janitors and salespeople. Employer1 may ask Q1 of secretaries and machinists. Employer2 may ask it of secretaries and salespeople. Employer3 may ask it of salespeople and machinists, but not secretaries.
So for Q1 there would be three modules - secretaries, salespeople & machinists. What is the minimum value for each module? And the same for the rest of the questions.
Hope that makes it clearer, not worse.

[szlamany]

Oops - I didn't see that you wanted the min value for each QUESTION...

I just created this example in QUERY ANALYZER - to prove that the final result set is what you expected.

Drop Table HCom

Set NoCount On

Create Table HCom (Code varchar(3),Question varchar(2),Module varchar(1),Value money)

Insert into HCom Values ('ABC','Q1','A',0.0)
Insert into HCom Values ('ABC','Q2','B',2.6)
Insert into HCom Values ('ABC','Q5','D',0.0)
Insert into HCom Values ('XYZ','Q1','A',0.0)
Insert into HCom Values ('XYZ','Q2','B',1.7)
Insert into HCom Values ('XYZ','Q4','C',0.6)

Select * from HCom HC
Where Value=(Select Min(Value) From HCom HC2 Where HC2.Question=HC.Question)

Gave me these results...

Code Question Module Value                 
---- -------- ------ --------------------- 
ABC  Q1       A      .0000
XYZ  Q1       A      .0000
XYZ  Q2       B      1.7000
XYZ  Q4       C      .6000
ABC  Q5       D      .0000

[salvelinus]

I dunno, that's not working for me, at least in Access 2000. That appears to be retrieving all the records (appears to, because the query runs awfully slowly). There's almost 16K records in the table, but I wouldn't expect more than a few hundred records to be returned at most. 90 questions x 10 modules, but not all modules ask all questions.
It might be easier to do this in code, but the co-worker doesn't understand code & wants the query, also preferably with the client name (from another table) matching the client code, which both tables have.

[szlamany]

Maybe try this then...

Select * from HCom HC
Where Value=(Select Min(Value) From HCom HC2
   Where HC2.Question=HC.Question and HC2.Module=HC.Module)

I think you want to pull a row only if it's the "lowest" value for the QUESTION and MODULE - right?

[salvelinus]

No, that didn't work either, thanks though.
Yes, szalamany, you're correct. The query should only return the records with the minimum value for each question/module combination, including any records that both may have the minimum value.
So if a particular question is asked in say, 6 modules, there should be at least 6 records for that question, possibly a few more if there are more than one client in a module who also have the minimum value.
What I don't want are records for every question/module for each client, which is what I'm getting now. There should only be a few clients at most in any particular question/module combination. I'd zip the file & attach it, but not allowed to do that.
I can get sort of what I want by filtering in the table on the question, then the module, then minimum value. Unfortunately, that only returns one question/module combination.