Grouping Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table called "t_actions" that contains amongst others three fields
"action_ID", "OLP_ID" and "action_due_date". I'm trying to group the table
by the "OLP_ID" and display the "action_ID" of the earliest "action_due_date"
within the "OLP_ID" group.

I can create a query that groups the ""OLP_ID" and gives me the earliest
"action_due_date" as follows:

SELECT t_actions.OLP_ID, Min(t_actions.action_due_date) As
MinOfaction_due_date
FROM t_actions
GROUP BY T-actions.OLP_ID;

.... but can't work out how to get the "action_ID" value or indeed any other
values from the table without grouping them. I'd like to extract the
"action_ID" and then use this in another query to extract other fields for
this record.

Any help really appreciated.

Ian
 
Thanks very much for the help Michael. I think I've managed to do it but
will continue to test. The code looks like this:

SELECT t_actions.*, t_list_call_times.[OLP Call Time Description] AS call_time
FROM t_actions LEFT JOIN t_list_call_times ON t_actions.action_due_time_slot
= t_list_call_times.[OLP Call Time]
WHERE (((t_actions.action_due_date)=(SELECT Min(t_actions1.action_due_date)
FROM t_actions As t_actions1
WHERE t_actions1.OLP_ID = t_actions.OLP_ID AND
t_actions1.action_done=False )));

I think this is giving me all fields from the "t_actions" table (plus a
description from a lookup in another table) where the action is not done
(action_done = False) and if more than one exist the oldest one only for each
"OLP_ID"

Please let me know if you see any glaring errors.

Thanks again

Ian
 
Hi,


That sounds ok. That also assume there is only one date per "action" (else,
the MIN() can occur on two or more records with the same date).

Hoping it may help,
Vanderghast, Access MVP


ianc said:
Thanks very much for the help Michael. I think I've managed to do it but
will continue to test. The code looks like this:

SELECT t_actions.*, t_list_call_times.[OLP Call Time Description] AS
call_time
FROM t_actions LEFT JOIN t_list_call_times ON
t_actions.action_due_time_slot
= t_list_call_times.[OLP Call Time]
WHERE (((t_actions.action_due_date)=(SELECT
Min(t_actions1.action_due_date)
FROM t_actions As t_actions1
WHERE t_actions1.OLP_ID = t_actions.OLP_ID AND
t_actions1.action_done=False )));

I think this is giving me all fields from the "t_actions" table (plus a
description from a lookup in another table) where the action is not done
(action_done = False) and if more than one exist the oldest one only for
each
"OLP_ID"

Please let me know if you see any glaring errors.

Thanks again

Ian


Michel Walsh said:
Hi,


Try one of the four methods exposed in
http://www.mvps.org/access/queries/qry0020.htm


Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
Michel, you're right it does show duplicates for "actions" occuring on the
same date. I can't work out how to stop this. I only want to show the
record that is the earliest of "action_due_time" another field in the
"t_actions" table where a duplicate occurs.

Thanks in advance.

Ian

Michel Walsh said:
Hi,


That sounds ok. That also assume there is only one date per "action" (else,
the MIN() can occur on two or more records with the same date).

Hoping it may help,
Vanderghast, Access MVP


ianc said:
Thanks very much for the help Michael. I think I've managed to do it but
will continue to test. The code looks like this:

SELECT t_actions.*, t_list_call_times.[OLP Call Time Description] AS
call_time
FROM t_actions LEFT JOIN t_list_call_times ON
t_actions.action_due_time_slot
= t_list_call_times.[OLP Call Time]
WHERE (((t_actions.action_due_date)=(SELECT
Min(t_actions1.action_due_date)
FROM t_actions As t_actions1
WHERE t_actions1.OLP_ID = t_actions.OLP_ID AND
t_actions1.action_done=False )));

I think this is giving me all fields from the "t_actions" table (plus a
description from a lookup in another table) where the action is not done
(action_done = False) and if more than one exist the oldest one only for
each
"OLP_ID"

Please let me know if you see any glaring errors.

Thanks again

Ian


Michel Walsh said:
Hi,


Try one of the four methods exposed in
http://www.mvps.org/access/queries/qry0020.htm


Hoping it may help,
Vanderghast, Access MVP


I have a table called "t_actions" that contains amongst others three
fields
"action_ID", "OLP_ID" and "action_due_date". I'm trying to group the
table
by the "OLP_ID" and display the "action_ID" of the earliest
"action_due_date"
within the "OLP_ID" group.

I can create a query that groups the ""OLP_ID" and gives me the
earliest
"action_due_date" as follows:

SELECT t_actions.OLP_ID, Min(t_actions.action_due_date) As
MinOfaction_due_date
FROM t_actions
GROUP BY T-actions.OLP_ID;

... but can't work out how to get the "action_ID" value or indeed any
other
values from the table without grouping them. I'd like to extract the
"action_ID" and then use this in another query to extract other fields
for
this record.

Any help really appreciated.

Ian
Click to expand...
Click to expand...
Click to expand...
 
Hi,

Do you the date and the time in a separate field? If so, try:


WHERE (((t_actions.action_due_date+ t_actions.action_due_time)=
(SELECT Min(t_actions1.action_due_date+ t_actions1.action_due_time)
FROM t_actions As t_actions1
WHERE t_actions1.OLP_ID = t_actions.OLP_ID
AND t_actions1.action_done=False )));



I just add the time to the date.


Hoping it may help,
Vanderghast, Access MVP


ianc said:
Michel, you're right it does show duplicates for "actions" occuring on the
same date. I can't work out how to stop this. I only want to show the
record that is the earliest of "action_due_time" another field in the
"t_actions" table where a duplicate occurs.

Thanks in advance.

Ian

Michel Walsh said:
Hi,


That sounds ok. That also assume there is only one date per "action"
(else,
the MIN() can occur on two or more records with the same date).

Hoping it may help,
Vanderghast, Access MVP


ianc said:
Thanks very much for the help Michael. I think I've managed to do it
but
will continue to test. The code looks like this:

SELECT t_actions.*, t_list_call_times.[OLP Call Time Description] AS
call_time
FROM t_actions LEFT JOIN t_list_call_times ON
t_actions.action_due_time_slot
= t_list_call_times.[OLP Call Time]
WHERE (((t_actions.action_due_date)=(SELECT
Min(t_actions1.action_due_date)
FROM t_actions As t_actions1
WHERE t_actions1.OLP_ID = t_actions.OLP_ID AND
t_actions1.action_done=False )));

I think this is giving me all fields from the "t_actions" table (plus a
description from a lookup in another table) where the action is not
done
(action_done = False) and if more than one exist the oldest one only
for
each
"OLP_ID"

Please let me know if you see any glaring errors.

Thanks again

Ian


:

Hi,


Try one of the four methods exposed in
http://www.mvps.org/access/queries/qry0020.htm


Hoping it may help,
Vanderghast, Access MVP


I have a table called "t_actions" that contains amongst others three
fields
"action_ID", "OLP_ID" and "action_due_date". I'm trying to group
the
table
by the "OLP_ID" and display the "action_ID" of the earliest
"action_due_date"
within the "OLP_ID" group.

I can create a query that groups the ""OLP_ID" and gives me the
earliest
"action_due_date" as follows:

SELECT t_actions.OLP_ID, Min(t_actions.action_due_date) As
MinOfaction_due_date
FROM t_actions
GROUP BY T-actions.OLP_ID;

... but can't work out how to get the "action_ID" value or indeed
any
other
values from the table without grouping them. I'd like to extract
the
"action_ID" and then use this in another query to extract other
fields
for
this record.

Any help really appreciated.

Ian
Click to expand...
Click to expand...
Click to expand...
 
Back
Top