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!