Update data to match based on like datasets

[JR]

I have a bunch of data in a table say:

id,myname,grouped
1,joe,123
2,joe,123
3,sam,123
4,pete,456
5,mike,456
6,alan,456
7,sally,098
8,michael,098
9,joseph,098

Records are grouped together conceptually in the single table based on
the grouped value. So joe, joe, and sam are one grouping as they all
have 123 for their grouped value. I need to run an update query that
will set the "myname" value on all matching records to that of the
last value. Don't ask why I would want to replace the names in this
way, it's just an example. So after done, I would end up with:

1,sam,123
2,sam,123
3,sam,123
4,alan,456
5,alan,456
6,alan,456
7,joseph,098
8,joseph,098
9,joseph,098

Any ideas how to accomplish this? I have this in Access but could
easily put this in SQL server if it would help.

Thanks.

JR

 

[Marshall Barton]

I have a bunch of data in a table say:

id,myname,grouped
1,joe,123
2,joe,123
3,sam,123
4,pete,456
5,mike,456
6,alan,456
7,sally,098
8,michael,098
9,joseph,098

Records are grouped together conceptually in the single table based on
the grouped value. So joe, joe, and sam are one grouping as they all
have 123 for their grouped value. I need to run an update query that
will set the "myname" value on all matching records to that of the
last value. Don't ask why I would want to replace the names in this
way, it's just an example. So after done, I would end up with:

1,sam,123
2,sam,123
3,sam,123
4,alan,456
5,alan,456
6,alan,456
7,joseph,098
8,joseph,098
9,joseph,098

SELECT T.id,
(SELECT TOP 1 X.myname
FROM table As X
WHERE X.grouped = T.grouped
ORDER BY X.id) As LastName,
T.grouped
FROM table As T

If you really need to update the original table, I think you
will have to replace the subquery with the equivalent
combination of Domain Aggregate functions.

 

[JR]

I have a bunch of data in a table say:

Records are grouped together conceptually in the single table based on
the grouped value. So joe, joe, and sam are one grouping as they all
have 123 for their grouped value. I need to run an update query that
will set the "myname" value on all matching records to that of the
last value. Don't ask why I would want to replace the names in this
way, it's just an example. So after done, I would end up with:

1,sam,123
2,sam,123
3,sam,123
4,alan,456
5,alan,456
6,alan,456
7,joseph,098
8,joseph,098
9,joseph,098

SELECT T.id,
(SELECT TOP 1 X.myname
FROM table As X
WHERE X.grouped = T.grouped
ORDER BY X.id) As LastName,
T.grouped
FROM table As T

If you really need to update the original table, I think you
will have to replace the subquery with the equivalent
combination of Domain Aggregate functions.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thanks for the help Marsh. Extremely appreciated. Since I need to
use the last value in list (highest id) for all the related records, I
just took you order by and put a desc at the end. That then set
things right. As for updating the original table, not really
necessary in this case. With the select query you provided I can then
use this as a view to finish the remainder of my work which will be to
export the results into Excel.

Thank you again.

JR