CODEWITHSUNDEEP
sqlms-accessms-access-2013
GDMM1414
GDMM1414

Reputation: 23

SQL-Get highest record from group at another table

I have read several answers to related questions but none of them can be applied to this case.

I have a table TableA where several groups are listed, with their score:

GROUP|SCORE

Blue | 0      
Green| 0      
Red  | 0      
Orange| 0

On another table TableB, I have the parts of each group and their individual score (status), which can have three different values:

 - G (Good)
 - A (Average)
 - B (Bad)

So tableB is:

GROUP|PART|STATUS

Blue | 3H2| A  
Blue | 4NQ| G  
Blue | W9X| A  
Green| 65D| G  
Red  | 73F| B  
Red  | 91G| A

I need to Update the score on TableA in the following way:

I have been a couple of days going around this and I can't find a solution. Thank you guys. Btw, I am using Access 2013.

Upvotes: 1

Views: 71

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

As I have already mentioned in the comments: Don't store the score redundantly; it is implicit in tableB. And to get your database straight introduce a status table:

STATUS   DESCRIPTION   SCORE
G        Good          3
A        Avarage       2
B        Bad           1

If you want to select the score for each color group use this query for instance:

select b.colorgroup, max(s.score) as maxscore
from tableb as b
join status as s on s.status = b.status
group by b.colorgroup;

Two alternative ways to write the same query:

select 
  colorgroup, 
  (
    select max(score)
    from status as s
    where s.status = b.status
  ) as maxscore
from tableb as b;

and

select b.colorgroup, s.maxscore
from tableb as b
join 
(
  select status, max(score) as maxscore
  from status
  group by status
) as s on s.status = b.status;

(BTW: I called your group colorgroup because GROUP is a reserved name in SQL.)

UPDATE You say you cannot add a table to the database. So you must evaluate the score in the query itself unfortunately. In standard SQL you would use CASE WHEN, which MS Access doesn't feature. MS Access provides IIF instead:

select 
  colorgroup, 
  max(iif(status = 'G', 3, iif(status = 'A', 2, 1))) as maxscore
from tableb
group by colorgroup;

If you even must use the column in tableA and store redundantly, use:

update tablea as a
set score =
(
  select 
    max(iif(status = 'G', 3, iif(status = 'A', 2, 1))) as maxscore
  from tableb as b
  where b.colorgroup = a.colorgroup
);

Upvotes: 2

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

In SQL-Server you could do in following:

QUERY

update a
set a.SCORE = MaxSTATUS
from #a a
join  (select GROUP_, MAX(case when b.STATUS_ = 'G' then 3
                               when b.STATUS_ = 'A' then 2
                               when b.STATUS_ = 'B' then 1
                           end) MaxSTATUS
        from #b b
        group by GROUP_

) b ON a.GROUP_ = b.GROUP_

select * from #a

SAMPLE DATA

CREATE TABLE #a
(
GROUP_ NVARCHAR(60),
SCORE INT
)
INSERT INTO #a VALUES
  ('Blue',0)
 ,('Green',0)
 ,('Red',0)
 ,('Orange',0)

CREATE TABLE #b
(
GROUP_ NVARCHAR(60),
PART NVARCHAR(60),
STATUS_ NVARCHAR(60),
)
INSERT INTO #b VALUES
  ('Blue','3H2','A')
 ,('Blue','4NQ','G')
 ,('Blue','W9X','A')
 ,('Green','65D','G')
 ,('Red','73F','B')
 ,('Red','91G','A')

OUPUT

GROUP_  SCORE
Blue    3
Green   3
Red     2
Orange  0

Upvotes: 0

Related Questions