Group By and Where Clause

[loki1049]

I was wondering if someone could offer some help. I have the following table structure

RESULTS:
[Contaminant] [Date] [EmployeeID] [Value] [Rank]

I need to group these values so that they have the same: Contaminant, Date, and EmployeeID. I then need to select only the lowest ranked value. If I try and use the query builder I pull in [Contaminant] and sort GROUPBY, [Date] and sort GROUPBY, [EmployeeID] and sort GROUPBY, and finally [Rank] and sort MIN. How can I add the value to the results of this query, if I use groupBy I obviously get all the records again, unless they happen to match among all values.

I think this is not allowed due to the fact that some of the values in my suggested grouping might have the same rank.. though I have entered the data so as they do not.

Any ideas on a work around? I have thought maybe using this query and joining on all fields to the original dataset.. help?

 

[CraigDolphin]

First up...avoid using reserved words as names for table/query fields. http://support.microsoft.com/kb/286335 Both 'Date' and 'Value' fall foul of this principle.

Something else to consider: What will you do if two records have equally low ranks within your grouping of the other fields? Do you want all such records to show each 'Value' individually? Or do you want to somehow select one to represent all the others? (e.g., maximum 'Value') Or perform some kind of mathematical operation instead? (e.g., average 'Value'?)

But to answer the question as asked: one way would be to use a domain aggregate function as a criterion in the Rank field.

For example:

DMin("Rank","tblYourSourceTable","[Contaminant]=" & [Contaminant] & " and [Date]=#" & [Date] & "# and [EmployeeID]=" & [EmployeeID])

This would limit the result set to only those records with the minimum 'rank' value for each combination of containment, date, and employeeid. You would need to think about what you wish to do in the instance of multiple matching records for a combination. And if there's any possibility of nulls, you would need to trap for them using the NZ() function.

There's more than one way to skin this cat but this should work.

 

[loki1049]

Thanks for the heads up, but these field names were not what I was using, just what I'm using to describe the problem in a simplified manner.

Second, I don't think that I can use Domain Aggregate functions, as this query is being designed in access, but will eventually be migrated to SQL Server, if I remember they don't allow those functions. Guess I should have anticipated that and included it.

 

[CraigDolphin]

Then you likely need to use one query to obtain the minimum rank for each combination of containment, date, and employeeid (do not include the value field in this query). Then create another query that is joined to the results of the first query (create an innner join on all of the fields except, obviously, the value field), which shows the corresponding values from your source table and does whatever aggregating you need done.

Like I said, more than one way to skin this cat.

 

[loki1049]

Thanks, I found a way that makes it much faster than joining all the fields. I simply turn the "value" into a string and add the rank to it, now when I choose MIN of my new frankinstiened string I get to retain the "value" portion which I can parse back into its original value and convert back to "double" data type.

I.E I do this:

StringData:[rank]&"."&[value] and group by MIN

Joining all the data with inner joins takes super long to execute on my rather large data set. Using this way instead is much faster, but likely very crued in terms of good programming techniques.