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: John W. Vinson on 13 Apr 2010 22:03 On Tue, 13 Apr 2010 17:45:01 -0700, zyus <zyus(a)discussions.microsoft.com> wrote: >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. Ok, let's try this: the record with the maximum date, and the record prior to that. It'll be simpler if you add a calculated field to QUnionAll by typing YearMonth: [Yr] & "-" & Format([Month], "00") You can then use SELECT Qunionall.YR, Qunionall.[MONTH], IIf(YearMonth = DMax("[YearMonth], "QUnionAll"), "Latest Mth", IIf([YearMonth]=DMax("[YearMonth]", "QUnionAll", "[YearMonth] < '" & [YearMonth] & "'"), "Previous Month", Null) FROM Qunionall; -- John W. Vinson [MVP]
From: zyus on 14 Apr 2010 00:01 Need your advise on how to put YearMonth: [Yr] & "-" & Format([Month], "00") in my Qunionall query as per below sql SELECT [Tbl-PreviousMONTH].* FROM [Tbl-PreviousMONTH]; UNION ALL SELECT [Tbl-sks].* FROM [Tbl-sks]; Thanks "John W. Vinson" wrote: > On Tue, 13 Apr 2010 17:45:01 -0700, zyus <zyus(a)discussions.microsoft.com> > wrote: > > >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. > > Ok, let's try this: the record with the maximum date, and the record prior to > that. It'll be simpler if you add a calculated field to QUnionAll by typing > > YearMonth: [Yr] & "-" & Format([Month], "00") > > You can then use > > SELECT Qunionall.YR, Qunionall.[MONTH], > IIf(YearMonth = DMax("[YearMonth], "QUnionAll"), "Latest Mth", > IIf([YearMonth]=DMax("[YearMonth]", "QUnionAll", "[YearMonth] < '" & > [YearMonth] & "'"), "Previous Month", Null) > FROM Qunionall; > > -- > > John W. Vinson [MVP] > > > . >
From: John W. Vinson on 14 Apr 2010 01:04
On Tue, 13 Apr 2010 21:01:01 -0700, zyus <zyus(a)discussions.microsoft.com> wrote: >Need your advise on how to put YearMonth: [Yr] & "-" & Format([Month], "00") >in my Qunionall query as per below sql > >SELECT [Tbl-PreviousMONTH].* >FROM [Tbl-PreviousMONTH]; >UNION ALL SELECT [Tbl-sks].* >FROM [Tbl-sks]; Pretty easy: SELECT [Tbl-PreviousMONTH].*, [Yr] & "-" & Format([Month], "00") AS YearMonth FROM [Tbl-PreviousMONTH]; UNION ALL SELECT [Tbl-sks].*, [Yr] & "-" & Format([Month], "00") AS YearMonth FROM [Tbl-sks]; -- John W. Vinson [MVP] |