A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help in Sales Analysis - Northwind template



 
 
Thread Tools Display Modes
  #1  
Old December 16th, 2009, 03:01 PM posted to microsoft.public.access.gettingstarted
Coco111 via AccessMonster.com
external usenet poster
 
Posts: 32
Default 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

  #2  
Old December 16th, 2009, 03:15 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default 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 2002-2005, 2007-2009
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  
Old December 16th, 2009, 04:15 PM posted to microsoft.public.access.gettingstarted
Coco111 via AccessMonster.com
external usenet poster
 
Posts: 32
Default 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 2002-2005, 2007-2009
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  
Old December 16th, 2009, 05:37 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default 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 2002-2005, 2007-2009
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 2002-2005, 2007-2009
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  
Old December 17th, 2009, 02:00 AM posted to microsoft.public.access.gettingstarted
Coco111 via AccessMonster.com
external usenet poster
 
Posts: 32
Default 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 2002-2005, 2007-2009
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  
Old April 13th, 2010, 01:53 PM posted to microsoft.public.access.gettingstarted
Tim Prescott
external usenet poster
 
Posts: 1
Default 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
16-Dec-09

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 2002-2005, 2007-2009
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 2002-2005, 2007-2009
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 2002-2005, 2007-2009
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...e-iterato.aspx
  #7  
Old April 13th, 2010, 05:02 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default 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 Q2-1 (for first month of quarter 2)

Field: QM: "Q" & Format([SomeDateField],"q") & "-" &
((Month([SomeDateField])-1) MOD 3) + 1

John Spencer
Access MVP 2002-2005, 2007-2010
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
16-Dec-09

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 2002-2005, 2007-2009
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 2002-2005, 2007-2009
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 2002-2005, 2007-2009
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...e-iterato.aspx

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:54 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.