If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
need to show current year and prior year totals
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! |
#2
|
|||
|
|||
need to show current year and prior year totals
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. "Lorina" wrote: 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! |
#3
|
|||
|
|||
need to show current year and prior year totals
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" wrote: 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. "Lorina" wrote: 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! |
#4
|
|||
|
|||
need to show current year and prior year totals
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. "Lorina" wrote: 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" wrote: 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. "Lorina" wrote: 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! |
#5
|
|||
|
|||
need to show current year and prior year totals
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" wrote: 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" wrote: 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. "Lorina" wrote: 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! |
#6
|
|||
|
|||
need to show current year and prior year totals
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" wrote: 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" wrote: 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" wrote: 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. "Lorina" wrote: 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! |
#7
|
|||
|
|||
need to show current year and prior year totals
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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. "Lorina" wrote: 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! |
#8
|
|||
|
|||
need to show current year and prior year totals
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 Lorina wrote: 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] Thanks! -- Message posted via http://www.accessmonster.com |
#9
|
|||
|
|||
need to show current year and prior year totals
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.accessmonster.com/Uwe/For...eries/201002/1 |
Thread Tools | |
Display Modes | |
|
|