Minimum values - code vs. SQL [Resolved]

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

[szlamany]

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!

[Ecniv]

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

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

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

[szlamany]

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

[Ecniv]

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?