use fields more than one time?

[jb]

I have:
TblPrecipitation
Year
Month
PrecipValue

I would like Access to calculate the water year
precipitation for several years. This is accomplished by
adding the last three months of one year to the next
seven months of the following year. I can do this
individually for each year, but I am looking for a query
design that will allow me to calculate the water year
precipitation for 10 years at a time.

I tried adding the three fields of my table to the query
design two times. For example, I added Year with the
criteria of "1980" and Month with criteria of "10 or 11
or 12" and on the second criteria line I added "1981" in
Year and "1 or 2 or 3 or 4 or 5 or 6 or 7" in month.
This works fine for the water year for 1981, but I would
like to be able to calculate the water year precip for
1981, 1982, 1983...1990 in one query. I tried adding
three fields of TblPrecip for the 1981 calculation and
adding the three fields again for the 1982 calculation.
Access will not to that. I have not figured it out.

If I am able to get the data for all 10 years in one
query. I think I will need to write a second query to
sum the values for each year.

Any suggestions, or is this just not the way Access works?

 

[Allen Browne]

Try typing this expression into your query (Field row):
WotYear: Year(DateAdd("m", 3, DateSerial([Year], [Month], 1)))

It uses DateSerial() create a date from the month and year, adds 3 months to
bump them into the desired year name, and then gets just the year part of
the result.

By depressing the Totals button on the toolbar (upper sigma icon), you
should now be able to GROUP BY this field, and SUM the PrecipValue field to
get the total for each of these "years".

 

[jb]

Thank you for your help! I used the syntax you suggested
in my query. I am not sure exactly how this functions,
so I have some questions on how to adapt it.

The answer table pulls up months 10, 11 and 12 for year
1980 and months 1 through 9 for 1981. I need it to pull
up just months 1 through 7 for 1981. How can I adjust
the query for this?

Secondly, I am not sure how to sum the precipitation
values for each year (1981, 1982, etc...) independently.
I do not want one large sum of all these months, rather a
sum of the 3 1980 months with the 7 1981 months, and the
same for subsequent years.

Thank you for your help, I think if I can figure this
out, it will really advance my ablilities to do more
complex queries!

-----Original Message-----
Try typing this expression into your query (Field row):
WotYear: Year(DateAdd("m", 3, DateSerial([Year], [Month], 1)))

It uses DateSerial() create a date from the month and year, adds 3 months to
bump them into the desired year name, and then gets just the year part of
the result.

By depressing the Totals button on the toolbar (upper sigma icon), you
should now be able to GROUP BY this field, and SUM the PrecipValue field to
get the total for each of these "years".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have:
TblPrecipitation
Year
Month
PrecipValue

I would like Access to calculate the water year
precipitation for several years. This is accomplished by
adding the last three months of one year to the next
seven months of the following year. I can do this
individually for each year, but I am looking for a query
design that will allow me to calculate the water year
precipitation for 10 years at a time.

I tried adding the three fields of my table to the query
design two times. For example, I added Year with the
criteria of "1980" and Month with criteria of "10 or 11
or 12" and on the second criteria line I added "1981" in
Year and "1 or 2 or 3 or 4 or 5 or 6 or 7" in month.
This works fine for the water year for 1981, but I would
like to be able to calculate the water year precip for
1981, 1982, 1983...1990 in one query. I tried adding
three fields of TblPrecip for the 1981 calculation and
adding the three fields again for the 1982 calculation.
Access will not to that. I have not figured it out.

If I am able to get the data for all 10 years in one
query. I think I will need to write a second query to
sum the values for each year.

Any suggestions, or is this just not the way Access

works?


.

 

[Allen Browne]

The calculated query creates a field named "WotYear". Since this year is 3
months ahead of the actual year, Oct, Nov and Dec of 1980 will show as 1981
in the WotYear column. Jan - Sep of 1981 will also show as 1981. Oct - Dec
of 1981 will show as 1982.

In this way, you have a field that contains a unique value for the
combination of months you want. You can sum or group by this calculated
field.

Alternatively, if you want a report that shows each month and gives a total
for the Oct-Sep year, do this *instead* of making it a GROUP BY query:

1. Create a report based on this query.

2. In report design view, open the Sorting and Grouping dialog (View menu).

3. On the first row of the dialog, choose the WotYear field.

4. In the lower pane of the dialog, set both Group Header and Group Footer
to Yes. (Access adds two new sections to your report.)

5. In the WotYear group footer, add a text box with Control Source of:
=Sum([PrecipValue])

6. In the WotYear group header, add a text box with Control Source of:
="For the year Oct " & [WotYear]-1 & " to Sep " & [WotYear]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your help! I used the syntax you suggested
in my query. I am not sure exactly how this functions,
so I have some questions on how to adapt it.

The answer table pulls up months 10, 11 and 12 for year
1980 and months 1 through 9 for 1981. I need it to pull
up just months 1 through 7 for 1981. How can I adjust
the query for this?

Secondly, I am not sure how to sum the precipitation
values for each year (1981, 1982, etc...) independently.
I do not want one large sum of all these months, rather a
sum of the 3 1980 months with the 7 1981 months, and the
same for subsequent years.

Thank you for your help, I think if I can figure this
out, it will really advance my ablilities to do more
complex queries!

-----Original Message-----
Try typing this expression into your query (Field row):
WotYear: Year(DateAdd("m", 3, DateSerial([Year], [Month], 1)))

It uses DateSerial() create a date from the month and year, adds 3 months to
bump them into the desired year name, and then gets just the year part of
the result.

By depressing the Totals button on the toolbar (upper sigma icon), you
should now be able to GROUP BY this field, and SUM the PrecipValue field to
get the total for each of these "years".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have:
TblPrecipitation
Year
Month
PrecipValue

I would like Access to calculate the water year
precipitation for several years. This is accomplished by
adding the last three months of one year to the next
seven months of the following year. I can do this
individually for each year, but I am looking for a query
design that will allow me to calculate the water year
precipitation for 10 years at a time.

I tried adding the three fields of my table to the query
design two times. For example, I added Year with the
criteria of "1980" and Month with criteria of "10 or 11
or 12" and on the second criteria line I added "1981" in
Year and "1 or 2 or 3 or 4 or 5 or 6 or 7" in month.
This works fine for the water year for 1981, but I would
like to be able to calculate the water year precip for
1981, 1982, 1983...1990 in one query. I tried adding
three fields of TblPrecip for the 1981 calculation and
adding the three fields again for the 1982 calculation.
Access will not to that. I have not figured it out.

If I am able to get the data for all 10 years in one
query. I think I will need to write a second query to
sum the values for each year.

Any suggestions, or is this just not the way Access

works?

 

[jb]

Thank for the details and suggestions!

I still have two things I am unclear on.

When I made the query, it just listed each record and
associated it with the "WotYear" in the "WotYear" field,
for example, 1981. I am not sure if I can so the sum for
each water year in a query or if I must create a report,
which you gave intstructions for.

Also, the water year contains Oct., Nov. and Dec. of
previous and only months January to July of the current
year. I am not sure how to change the syntax to exclude
August and September from the current years calculation.

Thanks for your help!

-----Original Message-----
The calculated query creates a field named "WotYear". Since this year is 3
months ahead of the actual year, Oct, Nov and Dec of 1980 will show as 1981
in the WotYear column. Jan - Sep of 1981 will also show as 1981. Oct - Dec
of 1981 will show as 1982.

In this way, you have a field that contains a unique value for the
combination of months you want. You can sum or group by this calculated
field.

Alternatively, if you want a report that shows each month and gives a total
for the Oct-Sep year, do this *instead* of making it a GROUP BY query:

1. Create a report based on this query.

2. In report design view, open the Sorting and Grouping dialog (View menu).

3. On the first row of the dialog, choose the WotYear field.

4. In the lower pane of the dialog, set both Group Header and Group Footer
to Yes. (Access adds two new sections to your report.)

5. In the WotYear group footer, add a text box with Control Source of:
=Sum([PrecipValue])

6. In the WotYear group header, add a text box with Control Source of:
="For the year Oct " & [WotYear]-1 & " to Sep " & [WotYear]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your help! I used the syntax you suggested
in my query. I am not sure exactly how this functions,
so I have some questions on how to adapt it.

The answer table pulls up months 10, 11 and 12 for year
1980 and months 1 through 9 for 1981. I need it to pull
up just months 1 through 7 for 1981. How can I adjust
the query for this?

Secondly, I am not sure how to sum the precipitation
values for each year (1981, 1982, etc...) independently.
I do not want one large sum of all these months, rather a
sum of the 3 1980 months with the 7 1981 months, and the
same for subsequent years.

Thank you for your help, I think if I can figure this
out, it will really advance my ablilities to do more
complex queries!

-----Original Message-----
Try typing this expression into your query (Field row):
WotYear: Year(DateAdd("m", 3, DateSerial([Year], [Month], 1)))

It uses DateSerial() create a date from the month and year, adds 3 months to
bump them into the desired year name, and then gets

just
the year part of

the result.

By depressing the Totals button on the toolbar (upper sigma icon), you
should now be able to GROUP BY this field, and SUM the PrecipValue field to
get the total for each of these "years".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
I have:
TblPrecipitation
Year
Month
PrecipValue

I would like Access to calculate the water year
precipitation for several years. This is

accomplished
by

adding the last three months of one year to the next
seven months of the following year. I can do this
individually for each year, but I am looking for a query
design that will allow me to calculate the water year
precipitation for 10 years at a time.

I tried adding the three fields of my table to the query
design two times. For example, I added Year with the
criteria of "1980" and Month with criteria of "10 or 11
or 12" and on the second criteria line I

added "1981"
in

Year and "1 or 2 or 3 or 4 or 5 or 6 or 7" in month.
This works fine for the water year for 1981, but I would
like to be able to calculate the water year precip for
1981, 1982, 1983...1990 in one query. I tried adding
three fields of TblPrecip for the 1981 calculation and
adding the three fields again for the 1982 calculation.
Access will not to that. I have not figured it out.

If I am able to get the data for all 10 years in one
query. I think I will need to write a second query to
sum the values for each year.

Any suggestions, or is this just not the way Access

works?

.

 

[Allen Browne]

You want to never include Aug and Sep?
Create a calcuated field in your query:
Month(Date())
and set the Criteria row under this field to:
< 8 Or > 9

You could aggregate the totals in the query by depressing the Totals button
on the toobar. GROUP BY WotYear, and SUM PrecipValue. The expression above
would need WHERE in the Totals row.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank for the details and suggestions!

I still have two things I am unclear on.

When I made the query, it just listed each record and
associated it with the "WotYear" in the "WotYear" field,
for example, 1981. I am not sure if I can so the sum for
each water year in a query or if I must create a report,
which you gave intstructions for.

Also, the water year contains Oct., Nov. and Dec. of
previous and only months January to July of the current
year. I am not sure how to change the syntax to exclude
August and September from the current years calculation.

Thanks for your help!

-----Original Message-----
The calculated query creates a field named "WotYear". Since this year is 3
months ahead of the actual year, Oct, Nov and Dec of 1980 will show as 1981
in the WotYear column. Jan - Sep of 1981 will also show as 1981. Oct - Dec
of 1981 will show as 1982.

In this way, you have a field that contains a unique value for the
combination of months you want. You can sum or group by this calculated
field.

Alternatively, if you want a report that shows each month and gives a total
for the Oct-Sep year, do this *instead* of making it a GROUP BY query:

1. Create a report based on this query.

2. In report design view, open the Sorting and Grouping dialog (View menu).

3. On the first row of the dialog, choose the WotYear field.

4. In the lower pane of the dialog, set both Group Header and Group Footer
to Yes. (Access adds two new sections to your report.)

5. In the WotYear group footer, add a text box with Control Source of:
=Sum([PrecipValue])

6. In the WotYear group header, add a text box with Control Source of:
="For the year Oct " & [WotYear]-1 & " to Sep " & [WotYear]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your help! I used the syntax you suggested
in my query. I am not sure exactly how this functions,
so I have some questions on how to adapt it.

The answer table pulls up months 10, 11 and 12 for year
1980 and months 1 through 9 for 1981. I need it to pull
up just months 1 through 7 for 1981. How can I adjust
the query for this?

Secondly, I am not sure how to sum the precipitation
values for each year (1981, 1982, etc...) independently.
I do not want one large sum of all these months, rather a
sum of the 3 1980 months with the 7 1981 months, and the
same for subsequent years.

Thank you for your help, I think if I can figure this
out, it will really advance my ablilities to do more
complex queries!
-----Original Message-----
Try typing this expression into your query (Field row):
WotYear: Year(DateAdd("m", 3, DateSerial([Year],
[Month], 1)))

It uses DateSerial() create a date from the month and
year, adds 3 months to
bump them into the desired year name, and then gets just
the year part of
the result.

By depressing the Totals button on the toolbar (upper
sigma icon), you
should now be able to GROUP BY this field, and SUM the
PrecipValue field to
get the total for each of these "years".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
message
I have:
TblPrecipitation
Year
Month
PrecipValue

I would like Access to calculate the water year
precipitation for several years. This is accomplished
by
adding the last three months of one year to the next
seven months of the following year. I can do this
individually for each year, but I am looking for a
query
design that will allow me to calculate the water year
precipitation for 10 years at a time.

I tried adding the three fields of my table to the
query
design two times. For example, I added Year with the
criteria of "1980" and Month with criteria of "10 or 11
or 12" and on the second criteria line I added "1981"
in
Year and "1 or 2 or 3 or 4 or 5 or 6 or 7" in month.
This works fine for the water year for 1981, but I
would
like to be able to calculate the water year precip for
1981, 1982, 1983...1990 in one query. I tried adding
three fields of TblPrecip for the 1981 calculation and
adding the three fields again for the 1982 calculation.
Access will not to that. I have not figured it out.

If I am able to get the data for all 10 years in one
query. I think I will need to write a second query to
sum the values for each year.

Any suggestions, or is this just not the way Access
works?

 

[jb]

Thanks!

I set the criteria for the Month field to "<8 or >9".
I also figured out that if I base another query on the
original query and sum PrecipValue and group by WotYear
(and do not inlcude Year and Month in the query at all),
the query will sum for each year!

I do not understand what you mean by include a WHERE
statement. I do not have a where statement. Is is
possible to both see the value for each month that is
being added and the resultant sum in the same query? I
have always had to design this with two queries as I have
done here.

Thanks again for helping me figure this out!

-----Original Message-----
You want to never include Aug and Sep?
Create a calcuated field in your query:
Month(Date())
and set the Criteria row under this field to:
< 8 Or > 9

You could aggregate the totals in the query by depressing the Totals button
on the toobar. GROUP BY WotYear, and SUM PrecipValue. The expression above
would need WHERE in the Totals row.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank for the details and suggestions!

I still have two things I am unclear on.

When I made the query, it just listed each record and
associated it with the "WotYear" in the "WotYear" field,
for example, 1981. I am not sure if I can so the sum for
each water year in a query or if I must create a report,
which you gave intstructions for.

Also, the water year contains Oct., Nov. and Dec. of
previous and only months January to July of the current
year. I am not sure how to change the syntax to exclude
August and September from the current years calculation.

Thanks for your help!

-----Original Message-----
The calculated query creates a field named "WotYear". Since this year is 3
months ahead of the actual year, Oct, Nov and Dec of 1980 will show as 1981
in the WotYear column. Jan - Sep of 1981 will also

show
as 1981. Oct - Dec

of 1981 will show as 1982.

In this way, you have a field that contains a unique value for the
combination of months you want. You can sum or group

by
this calculated

field.

Alternatively, if you want a report that shows each month and gives a total
for the Oct-Sep year, do this *instead* of making it a GROUP BY query:

1. Create a report based on this query.

2. In report design view, open the Sorting and

Grouping
dialog (View menu).

3. On the first row of the dialog, choose the WotYear field.

4. In the lower pane of the dialog, set both Group Header and Group Footer
to Yes. (Access adds two new sections to your report.)

5. In the WotYear group footer, add a text box with Control Source of:
=Sum([PrecipValue])

6. In the WotYear group header, add a text box with Control Source of:
="For the year Oct " & [WotYear]-1 & " to Sep " & [WotYear]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your help! I used the syntax you suggested
in my query. I am not sure exactly how this functions,
so I have some questions on how to adapt it.

The answer table pulls up months 10, 11 and 12 for year
1980 and months 1 through 9 for 1981. I need it to pull
up just months 1 through 7 for 1981. How can I adjust
the query for this?

Secondly, I am not sure how to sum the precipitation
values for each year (1981, 1982, etc...) independently.
I do not want one large sum of all these months, rather a
sum of the 3 1980 months with the 7 1981 months, and the
same for subsequent years.

Thank you for your help, I think if I can figure this
out, it will really advance my ablilities to do more
complex queries!
-----Original Message-----
Try typing this expression into your query (Field row):
WotYear: Year(DateAdd("m", 3, DateSerial ([Year],
[Month], 1)))

It uses DateSerial() create a date from the month and
year, adds 3 months to
bump them into the desired year name, and then gets just
the year part of
the result.

By depressing the Totals button on the toolbar (upper
sigma icon), you
should now be able to GROUP BY this field, and SUM the
PrecipValue field to
get the total for each of these "years".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
message
I have:
TblPrecipitation
Year
Month
PrecipValue

I would like Access to calculate the water year
precipitation for several years. This is accomplished
by
adding the last three months of one year to the next
seven months of the following year. I can do this
individually for each year, but I am looking for a
query
design that will allow me to calculate the water year
precipitation for 10 years at a time.

I tried adding the three fields of my table to the
query
design two times. For example, I added Year with the
criteria of "1980" and Month with criteria of "10 or 11
or 12" and on the second criteria line I added "1981"
in
Year and "1 or 2 or 3 or 4 or 5 or 6 or 7" in month.
This works fine for the water year for 1981, but I
would
like to be able to calculate the water year

precip
for

1981, 1982, 1983...1990 in one query. I tried adding
three fields of TblPrecip for the 1981

calculation
and

adding the three fields again for the 1982 calculation.
Access will not to that. I have not figured it out.

If I am able to get the data for all 10 years in one
query. I think I will need to write a second

query
to

sum the values for each year.

Any suggestions, or is this just not the way Access
works?

.

 

[Allen Browne]

The 2 queries is a workable solution.

When you create a Totals query (depress Totals button on toolbar), Access
adds a Total row to the grid. You can drag a field into the grid, and set
"Where" in the Total row. The aggregation is performed only on the records
that match this criteria.

Note that you can add criteria under one of the fields that has something
else (Sum or Count or ...) in the Total row, but that's different. The
criteria is applied *after* aggregation. That's the difference between a
WHERE clause and a HAVING clause of the query.

Hope that helps.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks!

I set the criteria for the Month field to "<8 or >9".
I also figured out that if I base another query on the
original query and sum PrecipValue and group by WotYear
(and do not inlcude Year and Month in the query at all),
the query will sum for each year!

I do not understand what you mean by include a WHERE
statement. I do not have a where statement. Is is
possible to both see the value for each month that is
being added and the resultant sum in the same query? I
have always had to design this with two queries as I have
done here.

Thanks again for helping me figure this out!

-----Original Message-----
You want to never include Aug and Sep?
Create a calcuated field in your query:
Month(Date())
and set the Criteria row under this field to:
< 8 Or > 9

You could aggregate the totals in the query by depressing the Totals button
on the toobar. GROUP BY WotYear, and SUM PrecipValue. The expression above
would need WHERE in the Totals row.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank for the details and suggestions!

I still have two things I am unclear on.

When I made the query, it just listed each record and
associated it with the "WotYear" in the "WotYear" field,
for example, 1981. I am not sure if I can so the sum for
each water year in a query or if I must create a report,
which you gave intstructions for.

Also, the water year contains Oct., Nov. and Dec. of
previous and only months January to July of the current
year. I am not sure how to change the syntax to exclude
August and September from the current years calculation.

Thanks for your help!
-----Original Message-----
The calculated query creates a field named "WotYear".
Since this year is 3
months ahead of the actual year, Oct, Nov and Dec of
1980 will show as 1981
in the WotYear column. Jan - Sep of 1981 will also show
as 1981. Oct - Dec
of 1981 will show as 1982.

In this way, you have a field that contains a unique
value for the
combination of months you want. You can sum or group by
this calculated
field.

Alternatively, if you want a report that shows each
month and gives a total
for the Oct-Sep year, do this *instead* of making it a
GROUP BY query:

1. Create a report based on this query.

2. In report design view, open the Sorting and Grouping
dialog (View menu).

3. On the first row of the dialog, choose the WotYear
field.

4. In the lower pane of the dialog, set both Group
Header and Group Footer
to Yes. (Access adds two new sections to your report.)

5. In the WotYear group footer, add a text box with
Control Source of:
=Sum([PrecipValue])

6. In the WotYear group header, add a text box with
Control Source of:
="For the year Oct " & [WotYear]-1 & " to Sep " &
[WotYear]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

message
Thank you for your help! I used the syntax you
suggested
in my query. I am not sure exactly how this functions,
so I have some questions on how to adapt it.

The answer table pulls up months 10, 11 and 12 for year
1980 and months 1 through 9 for 1981. I need it to
pull
up just months 1 through 7 for 1981. How can I adjust
the query for this?

Secondly, I am not sure how to sum the precipitation
values for each year (1981, 1982, etc...)
independently.
I do not want one large sum of all these months,
rather a
sum of the 3 1980 months with the 7 1981 months, and
the
same for subsequent years.

Thank you for your help, I think if I can figure this
out, it will really advance my ablilities to do more
complex queries!
-----Original Message-----
Try typing this expression into your query (Field
row):
WotYear: Year(DateAdd("m", 3, DateSerial ([Year],
[Month], 1)))

It uses DateSerial() create a date from the month and
year, adds 3 months to
bump them into the desired year name, and then gets
just
the year part of
the result.

By depressing the Totals button on the toolbar (upper
sigma icon), you
should now be able to GROUP BY this field, and SUM the
PrecipValue field to
get the total for each of these "years".

--
Allen Browne - Microsoft MVP. Perth, Western
Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot
org.
message
I have:
TblPrecipitation
Year
Month
PrecipValue

I would like Access to calculate the water year
precipitation for several years. This is
accomplished
by
adding the last three months of one year to the next
seven months of the following year. I can do this
individually for each year, but I am looking for a
query
design that will allow me to calculate the water
year
precipitation for 10 years at a time.

I tried adding the three fields of my table to the
query
design two times. For example, I added Year with
the
criteria of "1980" and Month with criteria of "10
or 11
or 12" and on the second criteria line I
added "1981"
in
Year and "1 or 2 or 3 or 4 or 5 or 6 or 7" in month.
This works fine for the water year for 1981, but I
would
like to be able to calculate the water year precip
for
1981, 1982, 1983...1990 in one query. I tried
adding
three fields of TblPrecip for the 1981 calculation
and
adding the three fields again for the 1982
calculation.
Access will not to that. I have not figured it out.

If I am able to get the data for all 10 years in one
query. I think I will need to write a second query
to
sum the values for each year.

Any suggestions, or is this just not the way Access
works?

 

[jb]

Thank you for your help and explanations!

-----Original Message-----
The 2 queries is a workable solution.

When you create a Totals query (depress Totals button on toolbar), Access
adds a Total row to the grid. You can drag a field into the grid, and set
"Where" in the Total row. The aggregation is performed only on the records
that match this criteria.

Note that you can add criteria under one of the fields that has something
else (Sum or Count or ...) in the Total row, but that's different. The
criteria is applied *after* aggregation. That's the difference between a
WHERE clause and a HAVING clause of the query.

Hope that helps.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks!

I set the criteria for the Month field to "<8 or >9".
I also figured out that if I base another query on the
original query and sum PrecipValue and group by WotYear
(and do not inlcude Year and Month in the query at all),
the query will sum for each year!

I do not understand what you mean by include a WHERE
statement. I do not have a where statement. Is is
possible to both see the value for each month that is
being added and the resultant sum in the same query? I
have always had to design this with two queries as I have
done here.

Thanks again for helping me figure this out!

-----Original Message-----
You want to never include Aug and Sep?
Create a calcuated field in your query:
Month(Date())
and set the Criteria row under this field to:
< 8 Or > 9

You could aggregate the totals in the query by depressing the Totals button
on the toobar. GROUP BY WotYear, and SUM PrecipValue. The expression above
would need WHERE in the Totals row.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Thank for the details and suggestions!

I still have two things I am unclear on.

When I made the query, it just listed each record and
associated it with the "WotYear" in the "WotYear" field,
for example, 1981. I am not sure if I can so the

sum
for

each water year in a query or if I must create a report,
which you gave intstructions for.

Also, the water year contains Oct., Nov. and Dec. of
previous and only months January to July of the current
year. I am not sure how to change the syntax to exclude
August and September from the current years calculation.

Thanks for your help!
-----Original Message-----
The calculated query creates a field named "WotYear".
Since this year is 3
months ahead of the actual year, Oct, Nov and Dec of
1980 will show as 1981
in the WotYear column. Jan - Sep of 1981 will also show
as 1981. Oct - Dec
of 1981 will show as 1982.

In this way, you have a field that contains a unique
value for the
combination of months you want. You can sum or

group
by

this calculated
field.

Alternatively, if you want a report that shows each
month and gives a total
for the Oct-Sep year, do this *instead* of making it a
GROUP BY query:

1. Create a report based on this query.

2. In report design view, open the Sorting and Grouping
dialog (View menu).

3. On the first row of the dialog, choose the WotYear
field.

4. In the lower pane of the dialog, set both Group
Header and Group Footer
to Yes. (Access adds two new sections to your report.)

5. In the WotYear group footer, add a text box with
Control Source of:
=Sum([PrecipValue])

6. In the WotYear group header, add a text box with
Control Source of:
="For the year Oct " & [WotYear]-1 & " to Sep " &
[WotYear]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

message
Thank you for your help! I used the syntax you
suggested
in my query. I am not sure exactly how this functions,
so I have some questions on how to adapt it.

The answer table pulls up months 10, 11 and 12

for
year

1980 and months 1 through 9 for 1981. I need it to
pull
up just months 1 through 7 for 1981. How can I adjust
the query for this?

Secondly, I am not sure how to sum the precipitation
values for each year (1981, 1982, etc...)
independently.
I do not want one large sum of all these months,
rather a
sum of the 3 1980 months with the 7 1981 months, and
the
same for subsequent years.

Thank you for your help, I think if I can figure this
out, it will really advance my ablilities to do more
complex queries!
-----Original Message-----
Try typing this expression into your query (Field
row):
WotYear: Year(DateAdd("m", 3, DateSerial ([Year],
[Month], 1)))

It uses DateSerial() create a date from the

month
and

year, adds 3 months to
bump them into the desired year name, and then gets
just
the year part of
the result.

By depressing the Totals button on the toolbar (upper
sigma icon), you
should now be able to GROUP BY this field, and

SUM
the

PrecipValue field to
get the total for each of these "years".

--
Allen Browne - Microsoft MVP. Perth, Western
Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot
org.
message
I have:
TblPrecipitation
Year
Month
PrecipValue

I would like Access to calculate the water year
precipitation for several years. This is
accomplished
by
adding the last three months of one year to

the
next

seven months of the following year. I can do this
individually for each year, but I am looking

for
a

query
design that will allow me to calculate the water
year
precipitation for 10 years at a time.

I tried adding the three fields of my table to the
query
design two times. For example, I added Year with
the
criteria of "1980" and Month with criteria of "10
or 11
or 12" and on the second criteria line I
added "1981"
in
Year and "1 or 2 or 3 or 4 or 5 or 6 or 7" in month.
This works fine for the water year for 1981,

but
I

would
like to be able to calculate the water year precip
for
1981, 1982, 1983...1990 in one query. I tried
adding
three fields of TblPrecip for the 1981 calculation
and
adding the three fields again for the 1982
calculation.
Access will not to that. I have not figured

it
out.

If I am able to get the data for all 10 years

in
one

query. I think I will need to write a second query
to
sum the values for each year.

Any suggestions, or is this just not the way Access
works?

.