Query to Assign Value based on size of group

[Frenstall]

Hello, I am trying to automate an Excel/hand process and can't get my head around how to solve this one situation:

I have a table with fields: company, part, defect

I want to give a company a score (-1) if they have a part & a defect of a certain type. The part and defect can exist multiple times but will be counted as one defect. BUT if there are five or more part-defect groups then they will be given a score of -5.

As pseudo code:

tbl[co,prt,def]
int[] score[company,prt,score,count]

for all values in table
for all values in score[]
if score.prt = tbl.prt then
score.count++
end if
end for

for all values in score[]
if score.count >= 5 then
score.score = -5
elseif score.count > 0 then
score.score = -1
else
score.score = 0
end if
end for

then total all the scores for one company and store it in a table

Hopefully this describes what i'm trying to do, again I am not great at Access so I am not even sure if a SQL query is the best method. Anyways, any input is greatly appreciated.

 

[r937]

select company
     , iif(count(*) > 5, -5,
       iif(count(*) > 0, -1, 0 )) as score
  from (
       select company
            , part
            , defect
         from yourtable
       group 
           by company
            , part
            , defect  
       ) as dt
group
    by company

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)