Tagging
in [General]
|
Prev: A problem occurred while xfile was communicating with the OLE serv
Next: Can A Tickbox Backcolor Change & If So How Would It Be Done??
From: zyus on 12 Apr 2010 23:52 My current query sql SELECT Qunionall.YR, Qunionall.[MONTH], IIf([yr]=Year(Date()) And [Month]=Month(Date()), "Latest Mth", IIf([Yr]=Year(DateAdd("m",-1,Date())) And [month]=Month(DateAdd("m",-1,Date())),"Previous Month",Null)) AS Tag FROM Qunionall; my sample query result as follow YR MONTH Tag 2010 2 2010 3 Previous Month "John W. Vinson" wrote: > On Mon, 12 Apr 2010 17:37:01 -0700, zyus <zyus(a)discussions.microsoft.com> > wrote: > > >Thanks for your response. > > > >Tried the query and successfully tagged "previous month" but not the "latest > >month" which is still remained null. > > Hrm. Please post your current query and some sample data including records > from April 2010. If the previous works, I don't see why the current month > shouldn't - it's a simpler query! > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 13 Apr 2010 00:58 On Mon, 12 Apr 2010 20:52:01 -0700, zyus <zyus(a)discussions.microsoft.com> wrote: >My current query sql >SELECT Qunionall.YR, Qunionall.[MONTH], >IIf([yr]=Year(Date()) And [Month]=Month(Date()), "Latest Mth", >IIf([Yr]=Year(DateAdd("m",-1,Date())) And >[month]=Month(DateAdd("m",-1,Date())),"Previous Month",Null)) AS Tag >FROM Qunionall; > >my sample query result as follow > >YR MONTH Tag >2010 2 >2010 3 Previous Month Well, the current month is April (4). If this is the data in your table, the results appear to be correct; March (3) is the previous month, February (2) is the month before that. Am I misunderstanding the intended result? -- John W. Vinson [MVP]
From: zyus on 13 Apr 2010 02:44 Generally you are right. Maybe my explaination a bit confusing. In my case, 2010-2 would be "Previous Month" and 2010-3 would be "Latest Month" disregard of any current month when i run the query (current-April). Hope my explaination suffice "John W. Vinson" wrote: > On Mon, 12 Apr 2010 20:52:01 -0700, zyus <zyus(a)discussions.microsoft.com> > wrote: > > >My current query sql > >SELECT Qunionall.YR, Qunionall.[MONTH], > >IIf([yr]=Year(Date()) And [Month]=Month(Date()), "Latest Mth", > >IIf([Yr]=Year(DateAdd("m",-1,Date())) And > >[month]=Month(DateAdd("m",-1,Date())),"Previous Month",Null)) AS Tag > >FROM Qunionall; > > > >my sample query result as follow > > > >YR MONTH Tag > >2010 2 > >2010 3 Previous Month > > Well, the current month is April (4). If this is the data in your table, the > results appear to be correct; March (3) is the previous month, February (2) is > the month before that. Am I misunderstanding the intended result? > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 13 Apr 2010 12:53 On Mon, 12 Apr 2010 23:44:01 -0700, zyus <zyus(a)discussions.microsoft.com> wrote: >Generally you are right. Maybe my explaination a bit confusing. In my case, >2010-2 would be "Previous Month" and 2010-3 would be "Latest Month" disregard >of any current month when i run the query (current-April). > >Hope my explaination suffice Ok, let me see if I understand. If the most recent two records in the table are January 2010 and October 2009, you want the January 2010 record to be "Latest Month" and the October 2009 record to be "Previous Month"? Today's date plays no role in the calculation whatsoever? If there is no record for a month, that month should just be ignored? Will there ever be two records with the same year and month? If so, could the "latest month" and "previous month" be two records with the same date? -- John W. Vinson [MVP]
From: zyus on 13 Apr 2010 20:45
Normally my two months data set will be a concurrent month...eg Jan10 and Feb10 or Feb10 and Mar10. There will never be Jan10 and Mar10. Normally there will be in the same year and month except early of the year where my data set will be Dec09 and Jan10. (Dec09-Previous Month, Jan10-Latest Month) Yep, today's date play no role in the above calculation FYI i use access for analysis and reporting purpose only by comparing two month end data set that i imported from other system. "John W. Vinson" wrote: > On Mon, 12 Apr 2010 23:44:01 -0700, zyus <zyus(a)discussions.microsoft.com> > wrote: > > >Generally you are right. Maybe my explaination a bit confusing. In my case, > >2010-2 would be "Previous Month" and 2010-3 would be "Latest Month" disregard > >of any current month when i run the query (current-April). > > > >Hope my explaination suffice > > Ok, let me see if I understand. > > If the most recent two records in the table are January 2010 and October 2009, > you want the January 2010 record to be "Latest Month" and the October 2009 > record to be "Previous Month"? Today's date plays no role in the calculation > whatsoever? If there is no record for a month, that month should just be > ignored? Will there ever be two records with the same year and month? If so, > could the "latest month" and "previous month" be two records with the same > date? > -- > > John W. Vinson [MVP] > . > |