Sum Query

[Guest]

I have a table that has Site, Part, Acutual sales, Normalized sales, Month,
Year layed out in this format. I'm using the sum function to get my last 12
months of sales. In the query I; group by site and part, sum both sales, and
where month is 11 or 12 where year is 2005 and where months are 1 or 2 or
....10 where year is 2006.
This works just fine as is. What I would also like to do is get the 2nd 12
months of sales. Is this possible and if so how? I tried 2 more lines like
above after, where month is 11 or 12 and where year is 2005, and where months
1 or 2...0r 10 and where year is 2004 but the totals did not equal what they
should have.
Any solutions?

 

[kingston via AccessMonster.com]

Create a calculated field that returns the date of the record based on the
Month and Year fields: [Month] & "/01/" & [Year]. Then use the criteria:
Between "#" & Month(Date()-365) & "/01/" & Year(Date()-365) & "#" and Date().
Adjust the closing date for the criteria to suit your needs.

 

[Guest]

So I would have 2 queries. The 1st with your calculation and the 2nd pulling
the data I need?

Create a calculated field that returns the date of the record based on the
Month and Year fields: [Month] & "/01/" & [Year]. Then use the criteria:
Between "#" & Month(Date()-365) & "/01/" & Year(Date()-365) & "#" and Date().
Adjust the closing date for the criteria to suit your needs.

I have a table that has Site, Part, Acutual sales, Normalized sales, Month,
Year layed out in this format. I'm using the sum function to get my last 12
months of sales. In the query I; group by site and part, sum both sales, and
where month is 11 or 12 where year is 2005 and where months are 1 or 2 or
...10 where year is 2006.
This works just fine as is. What I would also like to do is get the 2nd 12
months of sales. Is this possible and if so how? I tried 2 more lines like
above after, where month is 11 or 12 and where year is 2005, and where months
1 or 2...0r 10 and where year is 2004 but the totals did not equal what they
should have.
Any solutions?

 

[kingston via AccessMonster.com]

You can do this in one query. Just add another field like this (use your own
field names):

RecDate: [Month] & "/01/" & [Year]
with the criteria Between...

This will filter all of the records you want. You don't even need to display
this additional field.

So I would have 2 queries. The 1st with your calculation and the 2nd pulling
the data I need?

Create a calculated field that returns the date of the record based on the
Month and Year fields: [Month] & "/01/" & [Year]. Then use the criteria:

[quoted text clipped - 12 lines]

 

[Guest]

With this I can pull both a total for the last 12 months of sales and the
prior 12 months to? Months 1-12 and 13-24 is what I need totaled all in the
same query is posible.

You can do this in one query. Just add another field like this (use your own
field names):

RecDate: [Month] & "/01/" & [Year]
with the criteria Between...

This will filter all of the records you want. You don't even need to display
this additional field.

So I would have 2 queries. The 1st with your calculation and the 2nd pulling
the data I need?

Create a calculated field that returns the date of the record based on the
Month and Year fields: [Month] & "/01/" & [Year]. Then use the criteria:

[quoted text clipped - 12 lines]

should have.
Any solutions?

 

[kingston via AccessMonster.com]

If I understand you correctly, just change the criteria for the calculated
field:

Past 12 months
Between "#" & Month(Date()-365) & "/01/" & Year(Date()-365) & "#" and Date()

Past 13-24 months
Between "#" & Month(Date()-730) & "/01/" & Year(Date()-730) & "#" and "#" &
Month(Date()-366) & "/01/" & Year(Date()-366) & "#"

Just change the start and end dates as required if you want the past 24
months. Otherwise, total the results in a third query or create a control in
a form or report that sums the two results.

With this I can pull both a total for the last 12 months of sales and the
prior 12 months to? Months 1-12 and 13-24 is what I need totaled all in the
same query is posible.

You can do this in one query. Just add another field like this (use your own
field names):

[quoted text clipped - 13 lines]

 

[Guest]

I need to have both the 1st 12 months of total sales and the 2nd 12 months of
total sales. These 2 numbers are used in a different query. If I have to I
can build 2 queries and I think that might be the easiest.

If I understand you correctly, just change the criteria for the calculated
field:

Past 12 months
Between "#" & Month(Date()-365) & "/01/" & Year(Date()-365) & "#" and Date()

Past 13-24 months
Between "#" & Month(Date()-730) & "/01/" & Year(Date()-730) & "#" and "#" &
Month(Date()-366) & "/01/" & Year(Date()-366) & "#"

Just change the start and end dates as required if you want the past 24
months. Otherwise, total the results in a third query or create a control in
a form or report that sums the two results.

With this I can pull both a total for the last 12 months of sales and the
prior 12 months to? Months 1-12 and 13-24 is what I need totaled all in the
same query is posible.

You can do this in one query. Just add another field like this (use your own
field names):

[quoted text clipped - 13 lines]

should have.
Any solutions?