Help with this query

[adwanoy]

I have a query that looks like this:

select min(priority),keyword
from table
where condition
group by keyword

which it works fine, except that I want the query to return other feilds in the select, but I keep getting errors because of the aggrigate function min. any help

thanx

 

[zoroaster]

Me, I'd do this the lazy way; save the minimal query you've got, then create another with that query (Keyword and Min(Priority)) linked to the original table on Keyword and Priority, then you can add in any fields you like from the original table. I know it can be done far more elegantly by writing a subquery, but my knowledge on this has got a little rusty through getting into bad habits like this!

Laters, Z

"42??? We're going to get lynched!

 

[adwanoy]

That doesn't work because it reurn all the keyword, where I only wants certain ones. here is me original query:

SELECT Min(Comp.Priority),Comp.Keyword
FROM Comp INNER JOIN ((Activity RIGHT JOIN (position RIGHT JOIN (Job RIGHT JOIN task ON Job.TaskID=task.TaskID) ON position.TaskID=task.TaskID) ON Activity.TaskID=task.TaskID) INNER JOIN TaskComp ON task.TaskID=TaskComp.TaskID) ON Comp.CompID=TaskComp.CompID
WHERE (((Job.JobID)=[job])) OR (((position.PositionID)=[position])) OR (((Activity.GroupID)=9))
GROUP BY Comp.Keyword;

 

[TheAceMan1]

How are ya adwanoy . . .

Create a [blue]custom field[/blue] that uses a [blue]custom function[/blue] to return the aggregate . . .

Your thoughts?

See Ya! . . . . . .

 

[adwanoy]

Thanks, this is what I don't know how to do it.
can you show how

 

[TheAceMan1]

adwanoy . . .

After looking over your query I'm not sure [blue]Dmin in a function[/blue] would return what you need. The problem is Dmin works on a single table, and although you can include criteria . . . aggregates can't take account of more than one table . . .

You show you need Dmin according to [blue]linked tables & criteria[/blue] versus a single table! . . . see what I mean?

At this point it would be better if you explained your table structure (with relatioinships) along with what your after with the query . . .

Bear in mind . . . at this point DMin can return that of the entire table Comp which may not be what you want . . .

There is the possibility of using a recordset, but at the expense of performance (it would be like running the query as many times as you have returned records)

Your thoughts . . .

See Ya! . . . . . .

 

[adwanoy]

Do you men min?
well the query returns something like this:

minimum keyword
11 A
12 B
11 C
and so on
the priority values are, 11,12,13,21,22,23,31,32,33
and 11 is the highest.
I want the higest priority within each keyword. so if the same keyword has more than on priority of the same value return one(i.e. A21, A 11, A 12, A 32 return A 11)
but I also need to return other fields from table Comp which cannot combined with Min function. and if this query is linked with other query doesn't work because the link will be either priority or keyword which is not right.

 

[TheAceMan1]

adwanoy . . .

Min() can't be use in VBA unless its within an SQL statement (as a table has to be specified). This can be done in the function with SQL, just realize the SQL in the function will be called for each line in the query where the function is posted. This could present a performance issue.I was thinking DMin() but this gives you a worse performance hit.

My best suggestion at this point is have a seperate query (as you have it) for priority alone and use a function that returns the value from a recordset that looks up the priority.

See Ya! . . . . . .

 

[adwanoy]

TheAceMan1,
Thanks for your concern.
the problem that we have many records with the same priority within the same keyword and other keywords. so what value would the function return. I mean if I link this query with the table that has the fields I wanna return, there are many of them. what I really want is the the same record the min() query returns, any one of them as long as they lay within the same keyword and share the same priority.

 

[PHV]

What about something like this ?
Create a query named, say, qryJobPosAct:
SELECT TaskComp.CompID
FROM (Activity
RIGHT JOIN (position
RIGHT JOIN (Job
RIGHT JOIN task ON Job.TaskID=task.TaskID
) ON position.TaskID=task.TaskID
) ON Activity.TaskID=task.TaskID)
INNER JOIN TaskComp ON task.TaskID=TaskComp.TaskID
WHERE Job.JobID=[job] OR position.PositionID=[position] OR Activity.GroupID=9;

Then you may try this:
SELECT A.*
FROM (Comp AS A
INNER JOIN qryJobPosAct AS B ON A.CompID = B.CompID)
INNER JOIN (
SELECT C.Keyword, Min(C.Priority) AS MinPri FROM Comp AS C INNER JOIN qryJobPosAct AS Q
ON C.CompID = Q.CompID GROUP BY C.Keyword
) AS M ON A.Keyword = M.Keyword AND A.Priority = M.MinPri;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[adwanoy]

My man PHV thanks very much, here is the result
CompID Priority Keyword Description
381 22 D Perform proper corporate record.
382 21 I Determine actions based upon.
401 11 P Uphold ARC standards and be
402 12 L Acquire full information for
408 11 P Develop action plan with Material
412 11 C Make proper communication chain
416 12 pl Use communication skills
1882 11 A Be able to clearly and concisely
1883 11 A Be able to firmly communicate an
1888 11 B Able to make logical, informed
1889 11 B Able to manage multiple priorities
1899 11 C Be able to effectively assess and
1904 11 B Demonstrates optimism
1931 12 l Understands differences in

as you can see for the same category say P we got 2 record 401 and 408, and for keyword A record 1882 and 1883. where it should return only one record either 401 or 408, and for A either 1882 or 1883.
thanks again, and your help is highly appreciated