need to show current year and prior year totals

[Lorina]

I need to show current year and prior year sales by customer by part.

what is the best way to get that data (will create a report). Do I create a
query pulling for current year and then one for prior? how do I combine
them? Is it best done in the report?

Thanks!

 

[Jerry Whittle]

Put this in the criteria for the date field.

=DateAdd("yyyy", -1, CDate("1/1/" & Year(Date)))

There could be a problem if that field contains dates in future years.

 

[Lorina]

Maybe I was not clear in explaining. I need to show current year in one
field and prior year total in another field (so the reader can compare them).

 

[Jerry Whittle]

Check into crosstab queries in Help. First create a query to gather up the
needed data. You may want to group by a field using the Year function. Then
use this query as the record source for the crosstab query.

 

[KARL DEWEY]

Try this --
SELECT Customer, Part, Sum(IIF(Year([SaleDate]) = Year(Date()), [Sales],
0)) AS [Current_Year_Sales], Sum(IIF(Year([SaleDate]) = Year(Date())-1,
[Sales], 0)) AS [Last_Year_Sales]
FROM YourTable
GROUP BY Customer, Part;

 

[Lorina]

Thanks karl. before I try this I wanted to ask this quesiton. If they had
purchased items in both period will it return 2 rows?

My ultimate output would be:
Customer Name part Jan 09 Rev Jan 10 Rev

Is that possible to do?

Try this --
SELECT Customer, Part, Sum(IIF(Year([SaleDate]) = Year(Date()), [Sales],
0)) AS [Current_Year_Sales], Sum(IIF(Year([SaleDate]) = Year(Date())-1,
[Sales], 0)) AS [Last_Year_Sales]
FROM YourTable
GROUP BY Customer, Part;

--
Build a little, test a little.

Maybe I was not clear in explaining. I need to show current year in one
field and prior year total in another field (so the reader can compare them).

 

[KARL DEWEY]

If you examine what I posted you will see that is exactly what it will do,
one row per customer and part combination.
--
Build a little, test a little.

Thanks karl. before I try this I wanted to ask this quesiton. If they had
purchased items in both period will it return 2 rows?

My ultimate output would be:
Customer Name part Jan 09 Rev Jan 10 Rev

Is that possible to do?

Try this --
SELECT Customer, Part, Sum(IIF(Year([SaleDate]) = Year(Date()), [Sales],
0)) AS [Current_Year_Sales], Sum(IIF(Year([SaleDate]) = Year(Date())-1,
[Sales], 0)) AS [Last_Year_Sales]
FROM YourTable
GROUP BY Customer, Part;

--
Build a little, test a little.

Maybe I was not clear in explaining. I need to show current year in one
field and prior year total in another field (so the reader can compare them).


:

Put this in the criteria for the date field.

=DateAdd("yyyy", -1, CDate("1/1/" & Year(Date)))

There could be a problem if that field contains dates in future years.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I need to show current year and prior year sales by customer by part.

what is the best way to get that data (will create a report). Do I create a
query pulling for current year and then one for prior? how do I combine
them? Is it best done in the report?

Thanks!

 

[KenSheridan via AccessMonster.com]

I notice that you've shown the columns as Jan 09 and Jan 10. Does that mean
you want the sales per month rather than for the total calendar year? If so
and you want the sales for one month per year only you can restrict the
results by means of a parameter so that the user can enter the month, as a
number form 1 to 12, at runtime.

SELECT Customer, Part,
SUM(IIF(YEAR([SaleDate]) = YEAR(Date()), [Sales], 0)) AS [Current_Year_Sales],

SUM(IIF(YEAR([SaleDate]) = YEAR(Date())-1, [Sales], 0)) AS [Last_Year_Sales]
FROM YourTable
GROUP BY Customer, Part
WHERE MONTH([SaleDate]) = [Enter Month:];

In the report, instead of using labels as the column headings for the two
monthly sales columns use text boxes with ControlSource properties of:

=Format(DateSerial(Year(Date())-1,[Enter Month:],1),"mmm yy")

and:

=Format(DateSerial(Year(Date()),[Enter Month:],1),"mmm yy")

The headings will then show whatever month was entered as the parameter.

If you want all 12 months of the year as separate columns in the same report
then you'd need to extend the criterion for each IIF function call:

SELECT Customer, Part,
SUM(IIF(YEAR([SaleDate]) = YEAR(Date()) AND MONTH([SaleDate]) = 1,
[Sales], 0)) AS [Current_Year_Sales_Jan],
SUM(IIF(YEAR([SaleDate]) = YEAR(Date())-1 AND MONTH([SaleDate]) = 1,
[Sales], 0)) AS [Last_Year_Sales_Jan],
<and so on to>
SUM(IIF(YEAR([SaleDate]) = YEAR(Date()) AND MONTH([SaleDate]) = 12,
[Sales], 0)) AS [Current_Year_Sales_Dec],
SUM(IIF(YEAR([SaleDate]) = YEAR(Date())-1 AND MONTH([SaleDate]) = 12,
[Sales], 0)) AS [Last_Year_Sales_Dec],
SUM(IIF(YEAR([SaleDate]) = YEAR(Date()), [Sales], 0))
AS [Current_Year_Sales_Total],
SUM(IIF(YEAR([SaleDate]) = YEAR(Date())-1, [Sales], 0))
AS [Last_Year_Sales_Total]
FROM YourTable
GROUP BY Customer, Part;

Similarly for the column headings for the monthly sales in the report use
text boxes with ControlSource properties of:

=Format(DateSerial(Year(Date())-1,1,1),"mmm yy")

and:

=Format(DateSerial(Year(Date()),1,1),"mmm yy")

and so on to:

=Format(DateSerial(Year(Date())-1,12,1),"mmm yy")

and:

=Format(DateSerial(Year(Date()),12,1),"mmm yy")

Ken Sheridan
Stafford, England

Thanks karl. before I try this I wanted to ask this quesiton. If they had
purchased items in both period will it return 2 rows?

My ultimate output would be:
Customer Name part Jan 09 Rev Jan 10 Rev

Is that possible to do?

Try this --
SELECT Customer, Part, Sum(IIF(Year([SaleDate]) = Year(Date()), [Sales],

[quoted text clipped - 20 lines]

 

[KenSheridan via AccessMonster.com]

Correction: the WHERE clause comes before the GROUP BY clause:

SELECT Customer, Part,
SUM(IIF(YEAR([SaleDate]) = YEAR(Date()), [Sales], 0)) AS [Current_Year_Sales],

SUM(IIF(YEAR([SaleDate]) = YEAR(Date())-1, [Sales], 0)) AS [Last_Year_Sales]
FROM YourTable
WHERE MONTH([SaleDate]) = [Enter Month:]
GROUP BY Customer, Part;

In the report, instead of using labels as the column headings for the two
monthly

Ken Sheridan
Stafford, England