Minimum values - code vs. SQL [Resolved]

This is part of the same unresoved question I posted in the database forum.
Basically, I need to get the records with the minimum value (in a field named Value :rolleyes: ) for each question/module fields combination. I thought there'd only be a few hundred matching records, but there's between 2 & 3 thousand.
I wasn't able to figure out a query to retrieve these records, so instead I used code to loop through recordsets and INSERT records into a table. But it does this one record at a time, and takes a couple minutes to run, as well as tying up the processor.
If there's no SQL to run a regular query for this, is there a way to do a batch INSERT in DAO to speed things up? Thanks.

What DB?

In Access there is a function called Min which gets the minimum value of a field. But! This only works if there aren't unique values, and if you do want unique values you need to put this in as a sub query and link to it on all the fields to get a proper filter on it.

You will have to experiment.

Yes, Access 2000, & I've used Min in queries and use it in the code. Every query I've tried either returns all records but with the minimum value in the entire table (which would be zero) for every record, or the minimum value for each question, but not what's needed, which is the minimum value for each module in a particular question, including records that that have the same minimum value.
The table is basically (and I wouldn't have named fields these):
Code - 3 letter client code
Question - question
Module - analagous to a department
Value - double

There are 89 questions and 26 modules. A question is asked in 1 or more modules, possibly all 26. I want all and only records with the minimum value for every question/module combination.
Right now I'm doing nested loops that SELECT DISTINCT on question, the same for module matching that question, getting the min value there and inserting the records into a table. I haven't been able to come up with a query alone that will return the records.

I am still confused from the other thread that we took no where...

So let's take this slow - one step at a time.

Since you are in ACCESS (which I nevered used by the way - so it's difficult to even have this conversation...) - you have some kind of query builder screen - right? You can type a query in that screen and execute it to see the results - right?

Let's try this:

Code:

---

Select Question,Module,Min(Value) From Table Group by Question,Module

---

Since I think I understand your last post to say you want the minimum VALUE for a particular QUESTION+MODULE combination - then this should work.

Does it??

Well, almost. That gives me the minimum value for all question/module combinations, but doesn't give duplicates for the combination. More than one client may have the same minimum value for a combination, and I need the code to see which clients have the minimum value. If I include the Code field in the query, it returns all the records.

Quote:

---

Originally Posted by salvelinus

Well, almost. That gives me the minimum value for all question/module combinations, but doesn't give duplicates for the combination. More than one client may have the same minimum value for a combination, and I need the code to see which clients have the minimum value. If I include the Code field in the query, it returns all the records.

---

Like I said - one step at a time - I wanted to make sure that the result set that returned on that query definately reflected the population of of records from the main table that you would want.

Are you familiar with making a sub-query in ACCESS to select from?

I'm not - unfortunately - as we do MS SQL Server here...

But I would think that this SUB-QUERY - as we just laid out - has the "three pieces" of information that you need to perform your main query.

I just don't know how to take it the next step in ACCESS!

Thanks for all your help, szlamany. Yes, know subqueries. AFAIK, the SQL in Access & MS SQL Server is similar, with a few things, like wildcard characters, different. We'll be migrating to MSS soon, but the report preparers, who this is for, will probably stick with Access.

Szlamany/Salvelinus:
Something like this... I think it is similar in Sql Server ( :confused: )

Code:

---

Select table.id, table.question, table.module
From Table Left join
(Select Question,Module,Min(Value) as MV From Table Group by Question,Module) as sqryMin ON table.question = sqryMin.question and table.module=sqryMin.Module and table.value=sqry.mv

---

Bit in blue is the sub query Szlamany was on about. And he is right, take it in smaller steps and break down the process.

Edit:
Oh if you are using the query builder, put this then go back to design view and link the 'tables'.

Code:

---

Select table.* from Table,(Select Question,Module,Min(Value) as MV From Table Group by Question,Module) as sqryMin

---

Since we are in MS SQL Server, I would have dropped the results of that sub-query into a TABLE VARIABLE (similar to a temporary table) and then JOINED to it.

So in the long run - yes similar...

But since we typically do it this way, we never have JOINS to sub-queries - so the syntax is not all that common for me.

We also like to use WHERE EXISTS IN (SUBQUERY...) sometimes...

Szlamany:
Yeah, well they could be joined in the where clause, the from clause or the select field clause. Up the the developer (thread starter) to find the best way of doing it.

I am unsure whether temporary tables in Access would be a good idea, perhaps worth the thread starter experimenting with it and re-posting back up the results.

The Where Exists is another way, possible faster.


salvelinus:
Any of this helpful?

Thanks both for all your help. Your second example seemed to work best, ecniv, except after it runs Access changes the SQL to joins and then it errors out the 2nd time.
Anyway, after all that they decided they wanted to get just the values for a specific selected combination from two comboboxes, and that I could do in a couple minutes.
Appreciate all the help.