sum fish data by run year

[Lungfish]

I have 50+ years of salmon counts at dams in the Pacific Northwest and the
data is organized in a table with the following column headings:
YEAR MONTH# SPECIES COUNT

I would like to sum the population of each cohort, or run year, but salmon
returns often span calender years. Given the table description above, what
is the best way to sum the COUNT field for the months 11 to 6 (i.e. November
2000 to June 2001) for all years?

 

[KARL DEWEY]

Define a run year. I would assume that the year would be define from the
start.
If so then use this --
Run Year: IIF([MONTH#] BETWEEN 1 AND 8, [YEAR]-1, [YEAR])

 

[Jerry Whittle]

Maybe someone will come up with something simpler.

SELECT DatePart("yyyy",DateAdd("m",2,CDate([MONTH#] & "/" & [Year]))) AS
Years, tblLungfish.SPECIES, Sum(tblLungfish.COUNT) AS SumOfCOUNT
FROM tblLungfish
WHERE (((DatePart("m",DateAdd("m",2,CDate([MONTH#] & "/" & [Year]))))
Between 1 And 8))
GROUP BY DatePart("yyyy",DateAdd("m",2,CDate([MONTH#] & "/" & [Year]))),
tblLungfish.SPECIES;

Things would be easier if (1) your Year and Month# were in one actual
Date/Time field and (2) the fields weren't named YEAR and COUNT as they are
reserved words in Access and SQL and (3) It wasn't named MONTH# as the #
denotes a date and MONTH (without the # is also a reserved word. 2 and 3
above could cause code and SQL to blow chunks if you aren't very careful to
put things in brackets.

Speaking of blowing chunks, the DateAdd, DatePart, and especially the CDate
function will cause problems if you have something that is a Null in either
MONTH# or YEAR or would be an incorrect date like 13/13/2001.

 

[NetworkTrade]

interesting project....I have some good friends whom fish in the Portland Or
area.... I would approach it via Query. In Query design view add all these
fields...then add/create a calculated value field to sum the running total.
(Look up on Access web site how to do this...lots of explanations available)

You can then define the start/stop dates by manipulating the criteria in the
year and month fields....which in turn will force your calculated value field
to begin/end as you direct.

Once you get it right using raw query design approach - then you can pretty
it up with a form as a user interface if you wish.....