GROUP BY problem in access

hi,

i have a table( tblUpdate) with the following fields

UpdateID, UpdateDate, ActionID

UpdateID is the primary key
ActionID is a foreign key

i want to find the latest (by date) update of a particluar action.

and i can do this using the following:

SELECT Max(tblUpdate.UpdateDate) AS MaxOfUpdateDate, tblUpdate.ActionID
FROM tblUpdate
GROUP BY tblUpdate.ActionID;
this gives me :

"MaxOfUpdateDate","ActionID"
24/9/2005 ,24
26/8/2005 ,25
11/9/2005 ,26
26/9/2005 ,28

which is good but i also need the primary key, UpdateID

If i add updateID into the query i get

SELECT Max(tblUpdate.UpdateDate) AS MaxOfUpdateDate, tblUpdate.UpdateID,
tblUpdate.ActionID
FROM tblUpdate
GROUP BY tblUpdate.UpdateID, tblUpdate.ActionID;

i now get this

"MaxOfUpdateDate","ActionID","UpdateID"
24/9/2005 ,24,23
22/9/2005 ,24,24
26/8/2005 ,25,25
26/9/2004 ,25,26
11/9/2005 ,26,27
26/9/2005 ,28,28

which is no good because im getting results for all the updateids

i really just want :

"MaxOfUpdateDate","ActionID"
24/9/2005 ,24
26/8/2005 ,25
11/9/2005 ,26
26/9/2005 ,28

but with the primary key for each one.

any help would be much appreciated
thanks in advance

jim

[Fred Zuckerman]

<jim> wrote in message news:11*************@corp.supernews.com...

hi,

i have a table( tblUpdate) with the following fields

UpdateID, UpdateDate, ActionID

UpdateID is the primary key
ActionID is a foreign key

i want to find the latest (by date) update of a particluar action.

and i can do this using the following:

SELECT Max(tblUpdate.UpdateDate) AS MaxOfUpdateDate, tblUpdate.ActionID
FROM tblUpdate
GROUP BY tblUpdate.ActionID;
this gives me :

"MaxOfUpdateDate","ActionID"
24/9/2005 ,24
26/8/2005 ,25
11/9/2005 ,26
26/9/2005 ,28

which is good but i also need the primary key, UpdateID

If i add updateID into the query i get

SELECT Max(tblUpdate.UpdateDate) AS MaxOfUpdateDate, tblUpdate.UpdateID,
tblUpdate.ActionID
FROM tblUpdate
GROUP BY tblUpdate.UpdateID, tblUpdate.ActionID;

i now get this

"MaxOfUpdateDate","ActionID","UpdateID"
24/9/2005 ,24,23
22/9/2005 ,24,24
26/8/2005 ,25,25
26/9/2004 ,25,26
11/9/2005 ,26,27
26/9/2005 ,28,28

which is no good because im getting results for all the updateids

i really just want :

"MaxOfUpdateDate","ActionID"
24/9/2005 ,24
26/8/2005 ,25
11/9/2005 ,26
26/9/2005 ,28

but with the primary key for each one.

any help would be much appreciated
thanks in advance

jim

Create a 2nd query with two inputs, One will be your existing query and the
2nd will be tblUpdate. Link the two inputs by UpdateID. Put all three fields
in the output. If this doesn't make sense, then respond back and I'll write
the full SQL stmt.
Fred Zuckerman

Create a 2nd query with two inputs, One will be your existing query and
the
2nd will be tblUpdate. Link the two inputs by UpdateID. Put all three
fields
in the output. If this doesn't make sense, then respond back and I'll
write
the full SQL stmt.
Fred Zuckerman

many thanks for your reply fred

but how can i link the two inputs by UpdateID when i dont have UpdateID in
the the existing query....

[Mike Preston]

On Tue, 27 Sep 2005 21:24:51 +0100, <jim> wrote:

Create a 2nd query with two inputs, One will be your existing query and
the
2nd will be tblUpdate. Link the two inputs by UpdateID. Put all three
fields
in the output. If this doesn't make sense, then respond back and I'll
write
the full SQL stmt.
Fred Zuckerman

many thanks for your reply fred

but how can i link the two inputs by UpdateID when i dont have UpdateID in
the the existing query....

Try this:

SELECT a.* FROM tblUpdate a INNER JOIN
[SELECT Max(tblUpdate.UpdateDate) AS MaxOfUpdateDate,
tblUpdate.ActionID FROM tblUpdate
GROUP BY tblUpdate.ActionID]. as c
ON a.ActionID & UpdateDate = c.ActionID & MaxOfUpdateDate

Note that this is not a perfect solution. If your table also contains
the following:

"UpdateDate","ActionID","UpdateID"
22/9/2005 ,2,29
22/9/2005 ,2,30

You are going to end up with referencing only one of these and I
think, although I'm not sure, that it is random as to whether the 29
or the 30 comes out.

To rectify this, you can make the combination of ActionID & UpdateDate
a unique key. If you do that, then the query "works", I think.

mike

> Try this:

SELECT a.* FROM tblUpdate a INNER JOIN
[SELECT Max(tblUpdate.UpdateDate) AS MaxOfUpdateDate,
tblUpdate.ActionID FROM tblUpdate
GROUP BY tblUpdate.ActionID]. as c
ON a.ActionID & UpdateDate = c.ActionID & MaxOfUpdateDate

Note that this is not a perfect solution. If your table also contains
the following:

"UpdateDate","ActionID","UpdateID"
22/9/2005 ,2,29
22/9/2005 ,2,30

You are going to end up with referencing only one of these and I
think, although I'm not sure, that it is random as to whether the 29
or the 30 comes out.

To rectify this, you can make the combination of ActionID & UpdateDate
a unique key. If you do that, then the query "works", I think.

mike

Mike, you are a genius thanks.

I will alter the UpdateDate field to include the time so ActionID &
UpdateDate can be a unique key and more than one update can be made on the
same day.If you don't mind could you talk me through your SQL statement, in
particular could you explain the a. & c.

Thanks again

jim

[Mike Preston]

On Wed, 28 Sep 2005 09:22:24 +0100, <jim> wrote:

Try this:

SELECT a.* FROM tblUpdate a INNER JOIN
[SELECT Max(tblUpdate.UpdateDate) AS MaxOfUpdateDate,
tblUpdate.ActionID FROM tblUpdate
GROUP BY tblUpdate.ActionID]. as c
ON a.ActionID & UpdateDate = c.ActionID & MaxOfUpdateDate

Note that this is not a perfect solution. If your table also contains
the following:

"UpdateDate","ActionID","UpdateID"
22/9/2005 ,2,29
22/9/2005 ,2,30

You are going to end up with referencing only one of these and I
think, although I'm not sure, that it is random as to whether the 29
or the 30 comes out.

To rectify this, you can make the combination of ActionID & UpdateDate
a unique key. If you do that, then the query "works", I think.

mike

Mike, you are a genius thanks.

I will alter the UpdateDate field to include the time so ActionID &
UpdateDate can be a unique key and more than one update can be made on the
same day.If you don't mind could you talk me through your SQL statement, in
particular could you explain the a. & c.

Describing SQL in english is laborious. Below is a typically futile
attempt. Hope it gives some insight.

I am not particularly skilled at SQL. If *I* can suss out the correct
syntax, it can't be a terribly complicated bit of logic.

Fred gave an answer that was close:

1. Create a 2nd query with two inputs
1a One will be your existing query
1b and the 2nd will be tblUpdate.

The SQL statement I wrote is this "2nd query". It, as Fred indicated,
has two inputs. The way I wrote the query, I reversed the order that
Fred mentioned. Instead of the first one being your SQL statement and
the second one being tblUpdate, the SQL statement I wrote has
tblUpdate as the first input and your SQL statement for the second.

Here is the SQL I wrote:

SELECT a.* FROM tblUpdate a INNER JOIN
[SELECT Max(tblUpdate.UpdateDate) AS MaxOfUpdateDate,
tblUpdate.ActionID FROM tblUpdate
GROUP BY tblUpdate.ActionID]. as c
ON a.ActionID & UpdateDate = c.ActionID & MaxOfUpdateDate

2. Link the two inputs by UpdateID.

I linked the two inputs on both ActionID and UpdateDate, rather
than on UpdateID.

3. Put all three fields in the output.

Note that the initial selection is a.*, which takes all the
fields of table a. "a" is the abbreviation for tblUpdate (which is
specified just before the word INNER). "c" is the abbreviation for
the result of your query (which is specified by the phrase "as c" just
after your SQL statement. Access ignores the word "as". So I could
just as easily followed your SQL statement with just the letter "c";
or I could just as easily have used "as" before "a" just before the
word INNER. Either syntax is ok.

Here is the English equivalent to the SQL I wrote:

Select all of the fields from tblUpdate from the resulting rows that
you get by matching tblUpdate against a subset of itself. That is,
you want to take only those things that show up on both sides of the
comparison you are about to perform (this is an INNER JOIN). On the
left side of this comparison you will use the rows from tblUpdate. On
the right side of this comparison you will use the rows that result
from your SQL statement. The comparison is that the left side's
ActionID and UpdateDate fields must be equal to the right side's
ActionID and UpdateDate fields. Of course, the right hand side
doesn't have an UpdateDate field. Instead, it has the results from
your aggregate function (Max). That field is names MaxOfUpdateDate.

At its lowest level, where you combine everything you can, the query
is: select all the fields in tblupdate that result from joining
tblupdate on the left side against a subset of itself on the right
side. Let's call the left side "a", rather than tblUpdate. Let's
call the right side "c" rather than "the result we get from your
original SQL statement". I could just as easily used "a" and "b"
rather than "a" and "c". Or "tblUpdate_1" and "tblUpdate_2". It is
just an alias, which I choose to use more for its ability to be used
as an abbreviation.

Note that I haven't explained your original SQL statement, because
that is what you originally developed.

Hope that helps a bit.

mike

Thats brilliant mike, many thanks for your time and effort, it is much
appreciated!

cheers

jim
"Mike Preston" <mb******@pacbell.net.invalid> wrote in message
news:43****************@news.INDIVIDUAL.NET...

On Wed, 28 Sep 2005 09:22:24 +0100, <jim> wrote:

Try this:

SELECT a.* FROM tblUpdate a INNER JOIN
[SELECT Max(tblUpdate.UpdateDate) AS MaxOfUpdateDate,
tblUpdate.ActionID FROM tblUpdate
GROUP BY tblUpdate.ActionID]. as c
ON a.ActionID & UpdateDate = c.ActionID & MaxOfUpdateDate

Note that this is not a perfect solution. If your table also contains
the following:

"UpdateDate","ActionID","UpdateID"
22/9/2005 ,2,29
22/9/2005 ,2,30

You are going to end up with referencing only one of these and I
think, although I'm not sure, that it is random as to whether the 29
or the 30 comes out.

To rectify this, you can make the combination of ActionID & UpdateDate
a unique key. If you do that, then the query "works", I think.

mike

Mike, you are a genius thanks.

I will alter the UpdateDate field to include the time so ActionID &
UpdateDate can be a unique key and more than one update can be made on the
same day.If you don't mind could you talk me through your SQL statement,
in
particular could you explain the a. & c.

Describing SQL in english is laborious. Below is a typically futile
attempt. Hope it gives some insight.

I am not particularly skilled at SQL. If *I* can suss out the correct
syntax, it can't be a terribly complicated bit of logic.

Fred gave an answer that was close:

1. Create a 2nd query with two inputs
1a One will be your existing query
1b and the 2nd will be tblUpdate.

The SQL statement I wrote is this "2nd query". It, as Fred indicated,
has two inputs. The way I wrote the query, I reversed the order that
Fred mentioned. Instead of the first one being your SQL statement and
the second one being tblUpdate, the SQL statement I wrote has
tblUpdate as the first input and your SQL statement for the second.

Here is the SQL I wrote:

SELECT a.* FROM tblUpdate a INNER JOIN
[SELECT Max(tblUpdate.UpdateDate) AS MaxOfUpdateDate,
tblUpdate.ActionID FROM tblUpdate
GROUP BY tblUpdate.ActionID]. as c
ON a.ActionID & UpdateDate = c.ActionID & MaxOfUpdateDate

2. Link the two inputs by UpdateID.

I linked the two inputs on both ActionID and UpdateDate, rather
than on UpdateID.

3. Put all three fields in the output.

Note that the initial selection is a.*, which takes all the
fields of table a. "a" is the abbreviation for tblUpdate (which is
specified just before the word INNER). "c" is the abbreviation for
the result of your query (which is specified by the phrase "as c" just
after your SQL statement. Access ignores the word "as". So I could
just as easily followed your SQL statement with just the letter "c";
or I could just as easily have used "as" before "a" just before the
word INNER. Either syntax is ok.

Here is the English equivalent to the SQL I wrote:

Select all of the fields from tblUpdate from the resulting rows that
you get by matching tblUpdate against a subset of itself. That is,
you want to take only those things that show up on both sides of the
comparison you are about to perform (this is an INNER JOIN). On the
left side of this comparison you will use the rows from tblUpdate. On
the right side of this comparison you will use the rows that result
from your SQL statement. The comparison is that the left side's
ActionID and UpdateDate fields must be equal to the right side's
ActionID and UpdateDate fields. Of course, the right hand side
doesn't have an UpdateDate field. Instead, it has the results from
your aggregate function (Max). That field is names MaxOfUpdateDate.

At its lowest level, where you combine everything you can, the query
is: select all the fields in tblupdate that result from joining
tblupdate on the left side against a subset of itself on the right
side. Let's call the left side "a", rather than tblUpdate. Let's
call the right side "c" rather than "the result we get from your
original SQL statement". I could just as easily used "a" and "b"
rather than "a" and "c". Or "tblUpdate_1" and "tblUpdate_2". It is
just an alias, which I choose to use more for its ability to be used
as an abbreviation.

Note that I haven't explained your original SQL statement, because
that is what you originally developed.

Hope that helps a bit.

mike

[Fred Zuckerman]

<jim> wrote in message news:11*************@corp.supernews.com...

Create a 2nd query with two inputs, One will be your existing query and
the
2nd will be tblUpdate. Link the two inputs by UpdateID. Put all three
fields
in the output. If this doesn't make sense, then respond back and I'll
write
the full SQL stmt.
Fred Zuckerman
many thanks for your reply fred

but how can i link the two inputs by UpdateID when i dont have UpdateID

in the the existing query....

I'm sorry, you are correct. You would link on UpdateDate & ActionID.
Since that may not not a key field, there might be duplicate UpdateID, which
will yield multiple records in the 2nd query.

If this is not what you want and are willing to accept any of the matching
UpdateID, then change your 2nd query to include the 1st query alone, and a
column defined by:

(caution for line wrap)
DLookup("UpdateID","tblUpdate","ActionID=" & [ActionID] & " And
UpdateDate=#" & [UpdateDate] & "#")
(caution for line wrap)

If ActionID is text then you'll need some extra (') symbols.

Good Luck
Fred

> I'm sorry, you are correct. You would link on UpdateDate & ActionID.

Since that may not not a key field, there might be duplicate UpdateID,
which
will yield multiple records in the 2nd query.

If this is not what you want and are willing to accept any of the matching
UpdateID, then change your 2nd query to include the 1st query alone, and a
column defined by:

(caution for line wrap)
DLookup("UpdateID","tblUpdate","ActionID=" & [ActionID] & " And
UpdateDate=#" & [UpdateDate] & "#")
(caution for line wrap)

If ActionID is text then you'll need some extra (') symbols.

Good Luck
Fred

Many thanks for replying - i think i actually get it now... thanks again

jim