Problem with MIN function

[Guest]

I'm trying to generate a list that shows only the lowest number of each set

For example, there are 5 possible tasks that need to be done,
each task has a number 1-5. Each time a task is done, the query doesn't
show the task anymore, but it shows all the remaining ones.

I want it to show only the lowest number task - and it's description along
with TaskID, TaskSummary... etc. (I must be able to show these other fields)

The following query the basic select query.

SELECT ID, QueueNum, TaskSummary, Issue, Task
FROM qryOutstandingIssues
WHERE
(((qryOutstandingIssues.CutoverTime)=[Forms]![frm_MainEntry]![cmb_Cutover]));

Everytime I grouped it and selected MIN(Task), I would just get a an alias
that says MinOfTask - but it still shows all the tasks instead of just the
lowest number one.

How can I accomplish this?

qryOutstandingIssues is a UNION query that has the criteria that only
selects tasks that are undone.

 

[Guest]

you need to use the group function in the query..

open it it design view and try it..

 

[Marshall Barton]

I'm trying to generate a list that shows only the lowest number of each set

For example, there are 5 possible tasks that need to be done,
each task has a number 1-5. Each time a task is done, the query doesn't
show the task anymore, but it shows all the remaining ones.

I want it to show only the lowest number task - and it's description along
with TaskID, TaskSummary... etc. (I must be able to show these other fields)

The following query the basic select query.

SELECT ID, QueueNum, TaskSummary, Issue, Task
FROM qryOutstandingIssues
WHERE
(((qryOutstandingIssues.CutoverTime)=[Forms]![frm_MainEntry]![cmb_Cutover]));

Everytime I grouped it and selected MIN(Task), I would just get a an alias
that says MinOfTask - but it still shows all the tasks instead of just the
lowest number one.

How can I accomplish this?

qryOutstandingIssues is a UNION query that has the criteria that only
selects tasks that are undone.

The calculated field is named MinOfTask by default, but you
can change it to anything you want.

If the query is selecting all the tasks, it means that you
are grouping by too many fields. Try removing the ID field
and maybe some others.

If that works, but doesn't provide the information you need,
post back with more details about each field and what you
need tot see in the query's result.

 

[Guest]

Patrick,

You didn't really read my question - note:

I said: "Everytime I grouped it and selected MIN(Task), I would just get a
an alias
that says MinOfTask - but it still shows all the tasks instead of just the
lowest number one."

Marshall answered my question, and what I figured out was that I needed two
queries.

One was qryMinTasks

SELECT ID, MIN(Task) GROUP By ID FROM qryOutstandingIssues...

The other query qrySelectTasks

included qryMinTasks, without a join with criteria for ID = qryMinTasks.ID
and Task as qryMinTasks.MinOfTasks.

That did the trick..

you need to use the group function in the query..

open it it design view and try it..
--
Regards


Patrick Stubbin

I'm trying to generate a list that shows only the lowest number of each set

For example, there are 5 possible tasks that need to be done,
each task has a number 1-5. Each time a task is done, the query doesn't
show the task anymore, but it shows all the remaining ones.

I want it to show only the lowest number task - and it's description along
with TaskID, TaskSummary... etc. (I must be able to show these other fields)

The following query the basic select query.

SELECT ID, QueueNum, TaskSummary, Issue, Task
FROM qryOutstandingIssues
WHERE
(((qryOutstandingIssues.CutoverTime)=[Forms]![frm_MainEntry]![cmb_Cutover]));

Everytime I grouped it and selected MIN(Task), I would just get a an alias
that says MinOfTask - but it still shows all the tasks instead of just the
lowest number one.

How can I accomplish this?

qryOutstandingIssues is a UNION query that has the criteria that only
selects tasks that are undone.

 

[Guest]

Thanks Marsh,
Yep, I was grouping too many fields.

I used two queries and that did the trick - Read my response to Patrick.

I'm trying to generate a list that shows only the lowest number of each set

For example, there are 5 possible tasks that need to be done,
each task has a number 1-5. Each time a task is done, the query doesn't
show the task anymore, but it shows all the remaining ones.

I want it to show only the lowest number task - and it's description along
with TaskID, TaskSummary... etc. (I must be able to show these other fields)

The following query the basic select query.

SELECT ID, QueueNum, TaskSummary, Issue, Task
FROM qryOutstandingIssues
WHERE
(((qryOutstandingIssues.CutoverTime)=[Forms]![frm_MainEntry]![cmb_Cutover]));

Everytime I grouped it and selected MIN(Task), I would just get a an alias
that says MinOfTask - but it still shows all the tasks instead of just the
lowest number one.

How can I accomplish this?

qryOutstandingIssues is a UNION query that has the criteria that only
selects tasks that are undone.

The calculated field is named MinOfTask by default, but you
can change it to anything you want.

If the query is selecting all the tasks, it means that you
are grouping by too many fields. Try removing the ID field
and maybe some others.

If that works, but doesn't provide the information you need,
post back with more details about each field and what you
need tot see in the query's result.