Combining / merging tables and summing records

[jimbothebiologist]

Hi all,

How can I change the following table..I've tried group by and sum but no good

Here are the fields and table

Starting with...something like this

FisherID, FishedState, FishedOtherState, DaysFished
1................Y............................................2
1.......................................Y.....................3
2................Y............................................2
3.......................................Y.....................5


Wanting to get....this

FisherID, FishedState, FishedOtherState, DaysFished
1.................Y....................Y.....................5
2.................Y...........................................2
3.......................................Y.....................5

You can see that the record for Fisher 1 has been merged and DaysFished summed. I wouldn't have thought this was too tricky but its got me stumped today!

Any help is appreiciated

Cheers

 

[mcalex]

Try this query:

SELECT Fish.ID, Min(Fish.FishedState) AS MinOfFishedState, Min(Fish.FishedOtherState) AS MinOfFishedOtherState, Sum(Fish.DaysFished) AS SumOfDaysFished
FROM Fish
GROUP BY Fish.ID;


hth
mcalex