need to show current year and prior year totals

  • Thread starter Thread starter Lorina
  • Start date Start date
L

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!
 
Put this in the criteria for the date field.
=DateAdd("yyyy", -1, CDate("1/1/" & Year(Date)))
Click to expand...

There could be a problem if that field contains dates in future years.
 
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).
 
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.
 
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;
 
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?

KARL DEWEY said:
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.


Lorina said:
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).
Click to expand...
Click to expand...
 
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.


Lorina said:
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?

KARL DEWEY said:
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.


Lorina said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top