use fields more than one time?

  • Thread starter Thread starter jb
  • Start date Start date
J

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?
 
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".
 
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
Click to expand...
works?


.
Click to expand...
 
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.

jb said:
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
Click to expand...
Click to expand...
works?
Click to expand...
 
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
works?
Click to expand...


.
Click to expand...
 
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.

jb said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
precip
for
1981, 1982, 1983...1990 in one query. I tried adding
three fields of TblPrecip for the 1981
Click to expand...
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
Click to expand...
query
to
sum the values for each year.

Any suggestions, or is this just not the way Access
works?
Click to expand...
Click to expand...


.
Click to expand...
 
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.

jb said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
the
next
seven months of the following year. I can do this
individually for each year, but I am looking
Click to expand...
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,
Click to expand...
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
Click to expand...
it
out.
If I am able to get the data for all 10 years
Click to expand...
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?
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top