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




Help in Sales Analysis  Northwind template
Anybody Please help... Sorry again, in Northwind template 2007. I found
something wrong in sales Analysis, but I dont know how to solve. Queries of sales analysis show below MonthOfQuarter: (Month([Order Date]) Mod 3), in data sheet view oct = 1, Nov = 2, Dec = 0 and sales of Dec did not show up. I guess Dec should be 3, if im not wrong. What do you think and how to solve the problem. The sales of dec month did not show up...it shows no sales or 0 in quarter sales report summary. Thank you.  Message posted via http://www.accessmonster.com 
Ads 
#2




Help in Sales Analysis  Northwind template
If you are going to use MOD to number the months from 1 to 3, then try the
following expression. ((Month([Order Date])1) MOD 3) + 1 MOD 3 will return 0, 1, or 2 and you want 1, 2, and 3 so you need to add 1 to the result of the Mod Operation. You also want 1,4,7 and 10 to return zero when using MOD so you need to subtract 1 before using MOD so you get 0 for months 1,4,7,10 1 for months 2,5,8,11 2 for months 3,6,9,12 Then you add 1 to the result to get 1,2, or 3. This may or may not fix the fact that sales for December do not show up. I suggest you post the SQL of the query you are using. John Spencer Access MVP 20022005, 20072009 The Hilltop Institute University of Maryland Baltimore County Coco111 via AccessMonster.com wrote: Anybody Please help... Sorry again, in Northwind template 2007. I found something wrong in sales Analysis, but I dont know how to solve. Queries of sales analysis show below MonthOfQuarter: (Month([Order Date]) Mod 3), in data sheet view oct = 1, Nov = 2, Dec = 0 and sales of Dec did not show up. I guess Dec should be 3, if im not wrong. What do you think and how to solve the problem. The sales of dec month did not show up...it shows no sales or 0 in quarter sales report summary. Thank you. 
#3




Help in Sales Analysis  Northwind template
Hi John,
I change expression ((Month([Order Date])1) MOD 3) + 1 as suggested.... It works....Absolute right, you are great...fast response and help me solve problem...i can see the sales of dec in report now.. But...now, im in Q4, if in Jan'10 Q1 what I need to do next or do nothing... But im not very clear about your below advice You also want 1,4,7 and 10 to return zero when using MOD so you need to subtract 1 before using MOD so you get 0 for months 1,4,7,10 1 for months 2,5,8,11 2 for months 3,6,9,12 Then you add 1 to the result to get 1,2, or 3. Sorry and thanks....Coco111 John Spencer wrote: If you are going to use MOD to number the months from 1 to 3, then try the following expression. ((Month([Order Date])1) MOD 3) + 1 MOD 3 will return 0, 1, or 2 and you want 1, 2, and 3 so you need to add 1 to the result of the Mod Operation. You also want 1,4,7 and 10 to return zero when using MOD so you need to subtract 1 before using MOD so you get 0 for months 1,4,7,10 1 for months 2,5,8,11 2 for months 3,6,9,12 Then you add 1 to the result to get 1,2, or 3. This may or may not fix the fact that sales for December do not show up. I suggest you post the SQL of the query you are using. John Spencer Access MVP 20022005, 20072009 The Hilltop Institute University of Maryland Baltimore County Anybody Please help... Sorry again, in Northwind template 2007. I found something wrong in sales Analysis, but I dont know how to solve. [quoted text clipped  6 lines] dec month did not show up...it shows no sales or 0 in quarter sales report summary. Thank you.  Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200912/1 
#4




Help in Sales Analysis  Northwind template
Do nothing. The "advice" was simply an explanation of how the expression
worked to give you month number of the quarter John Spencer Access MVP 20022005, 20072009 The Hilltop Institute University of Maryland Baltimore County Coco111 via AccessMonster.com wrote: Hi John, I change expression ((Month([Order Date])1) MOD 3) + 1 as suggested.... It works....Absolute right, you are great...fast response and help me solve problem...i can see the sales of dec in report now.. But...now, im in Q4, if in Jan'10 Q1 what I need to do next or do nothing... But im not very clear about your below advice You also want 1,4,7 and 10 to return zero when using MOD so you need to subtract 1 before using MOD so you get 0 for months 1,4,7,10 1 for months 2,5,8,11 2 for months 3,6,9,12 Then you add 1 to the result to get 1,2, or 3. Sorry and thanks....Coco111 John Spencer wrote: If you are going to use MOD to number the months from 1 to 3, then try the following expression. ((Month([Order Date])1) MOD 3) + 1 MOD 3 will return 0, 1, or 2 and you want 1, 2, and 3 so you need to add 1 to the result of the Mod Operation. You also want 1,4,7 and 10 to return zero when using MOD so you need to subtract 1 before using MOD so you get 0 for months 1,4,7,10 1 for months 2,5,8,11 2 for months 3,6,9,12 Then you add 1 to the result to get 1,2, or 3. This may or may not fix the fact that sales for December do not show up. I suggest you post the SQL of the query you are using. John Spencer Access MVP 20022005, 20072009 The Hilltop Institute University of Maryland Baltimore County Anybody Please help... Sorry again, in Northwind template 2007. I found something wrong in sales Analysis, but I dont know how to solve. [quoted text clipped  6 lines] dec month did not show up...it shows no sales or 0 in quarter sales report summary. Thank you. 
#5




Help in Sales Analysis  Northwind template
Noted, and many thanks....
John Spencer wrote: Do nothing. The "advice" was simply an explanation of how the expression worked to give you month number of the quarter John Spencer Access MVP 20022005, 20072009 The Hilltop Institute University of Maryland Baltimore County Hi John, [quoted text clipped  45 lines] dec month did not show up...it shows no sales or 0 in quarter sales report summary. Thank you.  Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200912/1 
#6




This fix
John,
I've seen your response to this query in numerous forums. Thanks for the easy fix....EXCEPT I'm a VB novice. Where exactly do I find the expression to replace with your expression: ((Month([Order Date])1) MOD 3) + 1 Thank you! Tim John Spencer wrote: If you are going to use MOD to number the months from 1 to 3, then try 16Dec09 If you are going to use MOD to number the months from 1 to 3, then try the following expression. ((Month([Order Date])1) MOD 3) + 1 MOD 3 will return 0, 1, or 2 and you want 1, 2, and 3 so you need to add 1 to the result of the Mod Operation. You also want 1,4,7 and 10 to return zero when using MOD so you need to subtract 1 before using MOD so you get 0 for months 1,4,7,10 1 for months 2,5,8,11 2 for months 3,6,9,12 Then you add 1 to the result to get 1,2, or 3. This may or may not fix the fact that sales for December do not show up. I suggest you post the SQL of the query you are using. John Spencer Access MVP 20022005, 20072009 The Hilltop Institute University of Maryland Baltimore County Coco111 via AccessMonster.com wrote: Previous Posts In This Thread: On Wednesday, December 16, 2009 9:01 AM Coco111 via AccessMonster.com wrote: Help in Sales Analysis  Northwind template Anybody Please help... Sorry again, in Northwind template 2007. I found something wrong in sales Analysis, but I dont know how to solve. Queries of sales analysis show below MonthOfQuarter: (Month([Order Date]) Mod 3), in data sheet view oct = 1, Nov = 2, Dec = 0 and sales of Dec did not show up. I guess Dec should be 3, if im not wrong. What do you think and how to solve the problem. The sales of dec month did not show up...it shows no sales or 0 in quarter sales report summary. Thank you.  Message posted via http://www.accessmonster.com On Wednesday, December 16, 2009 9:15 AM John Spencer wrote: If you are going to use MOD to number the months from 1 to 3, then try If you are going to use MOD to number the months from 1 to 3, then try the following expression. ((Month([Order Date])1) MOD 3) + 1 MOD 3 will return 0, 1, or 2 and you want 1, 2, and 3 so you need to add 1 to the result of the Mod Operation. You also want 1,4,7 and 10 to return zero when using MOD so you need to subtract 1 before using MOD so you get 0 for months 1,4,7,10 1 for months 2,5,8,11 2 for months 3,6,9,12 Then you add 1 to the result to get 1,2, or 3. This may or may not fix the fact that sales for December do not show up. I suggest you post the SQL of the query you are using. John Spencer Access MVP 20022005, 20072009 The Hilltop Institute University of Maryland Baltimore County Coco111 via AccessMonster.com wrote: On Wednesday, December 16, 2009 10:15 AM Coco111 via AccessMonster.com wrote: Hi John,I change expression ((Month([Order Date])1) MOD 3) + 1 as suggested... Hi John, I change expression ((Month([Order Date])1) MOD 3) + 1 as suggested.... It works....Absolute right, you are great...fast response and help me solve problem...i can see the sales of dec in report now.. But...now, im in Q4, if in Jan'10 Q1 what I need to do next or do nothing... But im not very clear about your below advice You also want 1,4,7 and 10 to return zero when using MOD so you need to subtract 1 before using MOD so you get 0 for months 1,4,7,10 1 for months 2,5,8,11 2 for months 3,6,9,12 Then you add 1 to the result to get 1,2, or 3. Sorry and thanks....Coco111 John Spencer wrote:  Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200912/1 On Wednesday, December 16, 2009 11:37 AM John Spencer wrote: Do nothing. Do nothing. The "advice" was simply an explanation of how the expression worked to give you month number of the quarter John Spencer Access MVP 20022005, 20072009 The Hilltop Institute University of Maryland Baltimore County Coco111 via AccessMonster.com wrote: On Wednesday, December 16, 2009 8:00 PM Coco111 via AccessMonster.com wrote: Noted, and many thanks.... Noted, and many thanks.... John Spencer wrote:  Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200912/1 Submitted via EggHeadCafe  Software Developer Portal of Choice C# And The Little Iterator That Could http://www.eggheadcafe.com/tutorials...eiterato.aspx 
#7




This fix
It depends on where you want to generate the month of the quarter.
In a query, you might have a calculated field to generate the month of the quarter. Field: MonthQuarter: ((Month([SomeDateField])1) MOD 3) + 1 You could even use it in a more complex expression to Get quarter followed by month. March would be Q21 (for first month of quarter 2) Field: QM: "Q" & Format([SomeDateField],"q") & "" & ((Month([SomeDateField])1) MOD 3) + 1 John Spencer Access MVP 20022005, 20072010 The Hilltop Institute University of Maryland Baltimore County Tim Prescott wrote: John, I've seen your response to this query in numerous forums. Thanks for the easy fix....EXCEPT I'm a VB novice. Where exactly do I find the expression to replace with your expression: ((Month([Order Date])1) MOD 3) + 1 Thank you! Tim John Spencer wrote: If you are going to use MOD to number the months from 1 to 3, then try 16Dec09 If you are going to use MOD to number the months from 1 to 3, then try the following expression. ((Month([Order Date])1) MOD 3) + 1 MOD 3 will return 0, 1, or 2 and you want 1, 2, and 3 so you need to add 1 to the result of the Mod Operation. You also want 1,4,7 and 10 to return zero when using MOD so you need to subtract 1 before using MOD so you get 0 for months 1,4,7,10 1 for months 2,5,8,11 2 for months 3,6,9,12 Then you add 1 to the result to get 1,2, or 3. This may or may not fix the fact that sales for December do not show up. I suggest you post the SQL of the query you are using. John Spencer Access MVP 20022005, 20072009 The Hilltop Institute University of Maryland Baltimore County Coco111 via AccessMonster.com wrote: Previous Posts In This Thread: On Wednesday, December 16, 2009 9:01 AM Coco111 via AccessMonster.com wrote: Help in Sales Analysis  Northwind template Anybody Please help... Sorry again, in Northwind template 2007. I found something wrong in sales Analysis, but I dont know how to solve. Queries of sales analysis show below MonthOfQuarter: (Month([Order Date]) Mod 3), in data sheet view oct = 1, Nov = 2, Dec = 0 and sales of Dec did not show up. I guess Dec should be 3, if im not wrong. What do you think and how to solve the problem. The sales of dec month did not show up...it shows no sales or 0 in quarter sales report summary. Thank you.  Message posted via http://www.accessmonster.com On Wednesday, December 16, 2009 9:15 AM John Spencer wrote: If you are going to use MOD to number the months from 1 to 3, then try If you are going to use MOD to number the months from 1 to 3, then try the following expression. ((Month([Order Date])1) MOD 3) + 1 MOD 3 will return 0, 1, or 2 and you want 1, 2, and 3 so you need to add 1 to the result of the Mod Operation. You also want 1,4,7 and 10 to return zero when using MOD so you need to subtract 1 before using MOD so you get 0 for months 1,4,7,10 1 for months 2,5,8,11 2 for months 3,6,9,12 Then you add 1 to the result to get 1,2, or 3. This may or may not fix the fact that sales for December do not show up. I suggest you post the SQL of the query you are using. John Spencer Access MVP 20022005, 20072009 The Hilltop Institute University of Maryland Baltimore County Coco111 via AccessMonster.com wrote: On Wednesday, December 16, 2009 10:15 AM Coco111 via AccessMonster.com wrote: Hi John,I change expression ((Month([Order Date])1) MOD 3) + 1 as suggested... Hi John, I change expression ((Month([Order Date])1) MOD 3) + 1 as suggested.... It works....Absolute right, you are great...fast response and help me solve problem...i can see the sales of dec in report now.. But...now, im in Q4, if in Jan'10 Q1 what I need to do next or do nothing... But im not very clear about your below advice You also want 1,4,7 and 10 to return zero when using MOD so you need to subtract 1 before using MOD so you get 0 for months 1,4,7,10 1 for months 2,5,8,11 2 for months 3,6,9,12 Then you add 1 to the result to get 1,2, or 3. Sorry and thanks....Coco111 John Spencer wrote:  Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200912/1 On Wednesday, December 16, 2009 11:37 AM John Spencer wrote: Do nothing. Do nothing. The "advice" was simply an explanation of how the expression worked to give you month number of the quarter John Spencer Access MVP 20022005, 20072009 The Hilltop Institute University of Maryland Baltimore County Coco111 via AccessMonster.com wrote: On Wednesday, December 16, 2009 8:00 PM Coco111 via AccessMonster.com wrote: Noted, and many thanks.... Noted, and many thanks.... John Spencer wrote:  Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200912/1 Submitted via EggHeadCafe  Software Developer Portal of Choice C# And The Little Iterator That Could http://www.eggheadcafe.com/tutorials...eiterato.aspx 
Thread Tools  
Display Modes  

