From: John W. Vinson on
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
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
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]