Tagging

[zyus]

In my table i will have two months set of data. my text field [yr] and [mth]
will determine the year & month. Sample of data is as follow

[yr] [mth] [acno] [amt]
2010 01 123 100.00
2010 02 321 200.00
2010 02 123 150.00

What i want to achieve here is how to tag the latest month as "Latest Mth"
and the month before latest month as "Previous Month".

My new query/rpt will be

[yr] [mth] [acno] [amt] [tag]
2010 01 123 100.00 "Previous Month"
2010 02 321 200.00 "Latest Mth"
2010 02 123 150.00 "Latest Mth"

Please note that next month i will have different set of month data where
2010-02 & 2010-03.

Thanks

 

[Tom van Stiphout]

On Fri, 9 Apr 2010 02:43:02 -0700, zyus

Yr and Mth should be numeric fields. Then later use the Format
function if you want leading zeros etc.
That said, to do the tagging the general concept would be that you
would need to know what the latest month is: that's done with a Totals
query with a Max function. Probably easiest way would be to convert
your fields to a real date using the DateSerial function (using 1 as
the Day number). Records with this date would get the "Latest Month"
tag.
Once you have that, you can go back one month with the DateAdd
function. Records with this date would get the "Previous Month" tag.

-Tom.
Microsoft Access MVP

 

[John W. Vinson]

In my table i will have two months set of data. my text field [yr] and [mth]
will determine the year & month. Sample of data is as follow

[yr] [mth] [acno] [amt]
2010 01 123 100.00
2010 02 321 200.00
2010 02 123 150.00

What i want to achieve here is how to tag the latest month as "Latest Mth"
and the month before latest month as "Previous Month".

My new query/rpt will be

[yr] [mth] [acno] [amt] [tag]
2010 01 123 100.00 "Previous Month"
2010 02 321 200.00 "Latest Mth"
2010 02 123 150.00 "Latest Mth"

Please note that next month i will have different set of month data where
2010-02 & 2010-03.

Thanks

Well, as Tom suggests, this might be a lot easier with a date/time field; but
you could use a calculated field such as:

Tag: IIF([yr] = Year(Date()) AND Mth = Format(Date(), "mm"), "Latest Mth",
IIF([Yr] = Format(DateAdd("m", -1, Date()), "yyyy") AND [mth] =
Format(DateAdd("m", -1, Date()), "mm"), "Previous Month", Null))

 

[pink]

In my table i will have two months set of data. my text field [yr] and
[mth]
will determine the year & month. Sample of data is as follow

[yr] [mth] [acno] [amt]
2010 01 123 100.00
2010 02 321 200.00
2010 02 123 150.00

What i want to achieve here is how to tag the latest month as "Latest Mth"
and the month before latest month as "Previous Month".

My new query/rpt will be

[yr] [mth] [acno] [amt] [tag]
2010 01 123 100.00 "Previous Month"
2010 02 321 200.00 "Latest Mth"
2010 02 123 150.00 "Latest Mth"

Please note that next month i will have different set of month data where
2010-02 & 2010-03.

Thanks

 

[zyus]

Hi John,

When i run the query, the tag field produced null. Appreciate if your
advise. My actual mth field is month and my month text records are without
any leading zero (eg 1 and not 01)

SELECT Qunionall.YR, Qunionall.MONTH, IIf([Qunionall].[yr]=Year(Date()) And
[Qunionall].[Month]=Format(Date(),"mm"),"Latest
Mth",IIf([Qunionall].[Yr]=Format(DateAdd("m",-1,Date()),"yyyy") And
[Qunionall].[month]=Format(DateAdd("m",-1,Date()),"mm"),"Previous
Month",Null)) AS Tag
FROM Qunionall;

In my table i will have two months set of data. my text field [yr] and [mth]
will determine the year & month. Sample of data is as follow

[yr] [mth] [acno] [amt]
2010 01 123 100.00
2010 02 321 200.00
2010 02 123 150.00

What i want to achieve here is how to tag the latest month as "Latest Mth"
and the month before latest month as "Previous Month".

My new query/rpt will be

[yr] [mth] [acno] [amt] [tag]
2010 01 123 100.00 "Previous Month"
2010 02 321 200.00 "Latest Mth"
2010 02 123 150.00 "Latest Mth"

Please note that next month i will have different set of month data where
2010-02 & 2010-03.

Thanks

Well, as Tom suggests, this might be a lot easier with a date/time field; but
you could use a calculated field such as:

Tag: IIF([yr] = Year(Date()) AND Mth = Format(Date(), "mm"), "Latest Mth",
IIF([Yr] = Format(DateAdd("m", -1, Date()), "yyyy") AND [mth] =
Format(DateAdd("m", -1, Date()), "mm"), "Previous Month", Null))

 

[John W. Vinson]

When i run the query, the tag field produced null. Appreciate if your
advise. My actual mth field is month and my month text records are without
any leading zero (eg 1 and not 01)

You realize that Month (and Year) are reserved words and will cause Access to
get confused?

Is there some particular reason that you posted incorrect fieldnames and data
contents when we requested your data structure, and then complained when we
used them and they didn't work?

Sigh...

Ok, ASSUMING - because you haven't said - that the fields are Number fields
named Month and YR, and that you're lucky enough that the reserved words don't
cause errors (a risky assumption since I'll need to use the builtin Month
function in the query), try

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;

 

[zyus]

Dear John

Sorry my mistake on the field names & data contents. Purely overlooked.
"To complain" not at all as i know i pay nothing to you.

Please bear with me on my lack of english writing skill, access knowledge
and short of jargon that normally used by computer expert as i consider
myself still new to access.

As i really appreciated your response sometimes i feel that i'm also hit by
a tinge of sarcasm.

When i run the query, the tag field produced null. Appreciate if your
advise. My actual mth field is month and my month text records are without
any leading zero (eg 1 and not 01)

You realize that Month (and Year) are reserved words and will cause Access to
get confused?

Is there some particular reason that you posted incorrect fieldnames and data
contents when we requested your data structure, and then complained when we
used them and they didn't work?

Sigh...

Ok, ASSUMING - because you haven't said - that the fields are Number fields
named Month and YR, and that you're lucky enough that the reserved words don't
cause errors (a risky assumption since I'll need to use the builtin Month
function in the query), try

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;

 

[John W. Vinson]

Dear John

Sorry my mistake on the field names & data contents. Purely overlooked.
"To complain" not at all as i know i pay nothing to you.

Please bear with me on my lack of english writing skill, access knowledge
and short of jargon that normally used by computer expert as i consider
myself still new to access.

As i really appreciated your response sometimes i feel that i'm also hit by
a tinge of sarcasm.

I apologize, Zyus. There was far more than a trace of sarcasm; it was
inappropriate and I should not have been posting at all when I was in such a
mood.

I hope your query is now working; if not, please post back and I'll try to
help.

 

[zyus]

Thanks for your response.

Tried the query and successfully tagged "previous month" but not the "latest
month" which is still remained null.

 

[John W. Vinson]

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!

 

[zyus]

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]

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?

 

[zyus]

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

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]

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?

 

[zyus]

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]

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;

 

[zyus]

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

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]


.

 

[John W. Vinson]

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];