need to show current year and prior year totals

need to show current year and prior year totals

Post by TG9yaW5 » Sun, 21 Feb 2010 04:06:01


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!
 
 
 

need to show current year and prior year totals

Post by SmVycnkgV2 » Sun, 21 Feb 2010 04:54:03

Put this in the criteria for the date field.


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.

 
 
 

need to show current year and prior year totals

Post by TG9yaW5 » Sun, 21 Feb 2010 06:10:01

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).
 
 
 

need to show current year and prior year totals

Post by SmVycnkgV2 » Sun, 21 Feb 2010 06:51:01

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
 
 
 

need to show current year and prior year totals

Post by S0FSTCBERV » Sun, 21 Feb 2010 07:40:01

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.
 
 
 

need to show current year and prior year totals

Post by TG9yaW5 » Wed, 24 Feb 2010 01:38:01

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?
 
 
 

need to show current year and prior year totals

Post by S0FSTCBERV » Wed, 24 Feb 2010 04:36:12

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.
 
 
 

need to show current year and prior year totals

Post by KenSherida » Wed, 24 Feb 2010 09:47:30

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



--
Message posted via http://www.yqcomputer.com/
 
 
 

need to show current year and prior year totals

Post by KenSherida » Wed, 24 Feb 2010 09:51:30

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

--
Message posted via AccessMonster.com
http://www.yqcomputer.com/