Pattern Query

Hi John,

As per my posting earlier, my [Yr] & [Month] field is not a proper
datetime field but it keeps my year and month of my monthend data set.

I would normally compare two monthend set of data for my analysis. The set
data are from my 2 table , Tbl-SKS (Current Month) and Tbl-PreviousMonth
(Month before current month)

Since my table are from same structure i managed to union all the data in
one query and i assume i can do the monthly variance analysis .

My problem is how to do the tagging as follow :

Assume this month (Mar 2010)

Tbl-SKS ([Yr]=2010, [Month]=02) to tag as "CurrentMonth"
Tbl-PreviousMonth ([Yr]=2010, [Month]=01) to tag as "PreviousMonth"

subsequently next month (Apr 2010) my tag will be as follow

Tbl-SKS ([Yr]=2010, [Month]=03) to tag as "CurrentMonth"
Tbl-PreviousMonth ([Yr]=2010, [Month]=02) to tag as "PreviousMonth"

Is there a way to do the tagging automaticly without having to change the
parameter in the [Yr] & [Month] field.

Thanks









"John W. Vinson" wrote:

On Thu, 4 Mar 2010 22:54:01 -0800, zyus
wrote:

Tried with this one but still with missing [,) error

Ok, let's count parentheses. The way I do that is to read through the string,
adding 1 for each left paren and subtracting 1 for each right; that way you
can find out when you end up with +1 instead of the correct 0:

SELECT Acno
FROM [Tmain]
WHERE Arrmth in (2,3) 0
AND ([Yr]=Year(Date()) AND [Month]=Month(Date()) THERE it is. +1 ,
should be 0. Add one more close paren on this line.

Sorry for the typo.

OR [Yr]=Year(DateAdd("m", -1, Date()) AND [Month]=Month(DateAdd("m", -1,
Date())
OR [Yr]=Year(DateAdd("m", -2, Date()) AND [Month] = Month(DateAdd("m", -2,
Date()))


Checking all of them, and correcting the paren nesting errors (the AND
operators should have been inside parens) it should be

SELECT Acno
FROM [Tmain]
WHERE Arrmth in (2,3)
AND ([Yr]=Year(Date()) AND [Month]=Month(Date()))
OR ([Yr]=Year(DateAdd("m", -1, Date())) AND [Month]=Month(DateAdd("m", -1,
Date())))
OR ([Yr]=Year(DateAdd("m", -2, Date())) AND [Month] = Month(DateAdd("m", -2,
Date())))

Check my work, it's a lot easier when the computer is there to give you a
clear and unambiguous error message (the one you still haven't posted).
--

John W. Vinson [MVP]
.

On Mon, 8 Mar 2010 19:54:01 -0800, zyus
wrote:

Hi John,

As per my posting earlier, my [Yr] & [Month] field is not a proper
datetime field but it keeps my year and month of my monthend data set.

I would normally compare two monthend set of data for my analysis. The set
data are from my 2 table , Tbl-SKS (Current Month) and Tbl-PreviousMonth
(Month before current month)

That's decent spreadsheet design. It's *very bad* relational table design.

You're storing facts - time-volatile facts at that! - in table names.

A much much better design would be *ONE BIG TABLE* with a Date/Time field.

Since my table are from same structure i managed to union all the data in
one query and i assume i can do the monthly variance analysis .

UNION queries are useful but you *don't need one* if you were to have all your
data in one table. It's very, very easy to extract this month, or the previous
month, or April 2008 from your table; it's also easy to compare any month to
any other month using a Self Join.

My problem is how to do the tagging as follow :

Assume this month (Mar 2010)

Tbl-SKS ([Yr]=2010, [Month]=02) to tag as "CurrentMonth"
Tbl-PreviousMonth ([Yr]=2010, [Month]=01) to tag as "PreviousMonth"

SELECT whatever fields FROM [tbl-SKS] WHERE Yr = Year(Date()) AND [Month] =
Format(Date(), "mm")

to get current month;

SELECT whatever fields FROM [tbl-SKS] WHERE Yr = Year(DateAdd("m", -1,
Date()) AND Month = Format(DateAdd("m", -1, Date()), "mm")

for the previous month.

subsequently next month (Apr 2010) my tag will be as follow

Tbl-SKS ([Yr]=2010, [Month]=03) to tag as "CurrentMonth"
Tbl-PreviousMonth ([Yr]=2010, [Month]=02) to tag as "PreviousMonth"

Is there a way to do the tagging automaticly without having to change the
parameter in the [Yr] & [Month] field.

Yes. A table redesign would make it a heck of a lot easier though.
--

John W. Vinson [MVP]

"zyus" wrote in message
...
I have this sample of field & data

YrMth Acno Arrmth
2009-12 A 2
2009-12 B 1
2009-12 C 2
2010-1 A 3
2010-1 B 2
2010-1 C 1
2010-2 A 2
2010-2 B 3
2010-3 C 2

My aim is to extract acno which in the past 3 months (dec09 till feb10)
the arrmth is between 2 to 3.

In my above example acno A meet the criteria.

The above data is in my Tbl-SK and more than 1mil of records.

Thanks

lmml!;nn

"zyus" a écrit dans le message de groupe de
discussion : ...
Hi John,

As per my posting earlier, my [Yr] & [Month] field is not a proper
datetime field but it keeps my year and month of my monthend data set.

I would normally compare two monthend set of data for my analysis. The set
data are from my 2 table , Tbl-SKS (Current Month) and Tbl-PreviousMonth
(Month before current month)

Since my table are from same structure i managed to union all the data in
one query and i assume i can do the monthly variance analysis .

My problem is how to do the tagging as follow :

Assume this month (Mar 2010)

Tbl-SKS ([Yr]=2010, [Month]=02) to tag as "CurrentMonth"
Tbl-PreviousMonth ([Yr]=2010, [Month]=01) to tag as "PreviousMonth"

subsequently next month (Apr 2010) my tag will be as follow

Tbl-SKS ([Yr]=2010, [Month]=03) to tag as "CurrentMonth"
Tbl-PreviousMonth ([Yr]=2010, [Month]=02) to tag as "PreviousMonth"

Is there a way to do the tagging automaticly without having to change the
parameter in the [Yr] & [Month] field.

Thanks









"John W. Vinson" wrote:

On Thu, 4 Mar 2010 22:54:01 -0800, zyus
wrote:

Tried with this one but still with missing [,) error

Ok, let's count parentheses. The way I do that is to read through the
string,
adding 1 for each left paren and subtracting 1 for each right; that way
you
can find out when you end up with +1 instead of the correct 0:

SELECT Acno
FROM [Tmain]
WHERE Arrmth in (2,3) 0
AND ([Yr]=Year(Date()) AND [Month]=Month(Date()) THERE it is. +1 ,
should be 0. Add one more close paren on this line.

Sorry for the typo.

OR [Yr]=Year(DateAdd("m", -1, Date()) AND [Month]=Month(DateAdd("m", -1,
Date())
OR [Yr]=Year(DateAdd("m", -2, Date()) AND [Month] =
Month(DateAdd("m", -2,
Date()))


Checking all of them, and correcting the paren nesting errors (the AND
operators should have been inside parens) it should be

SELECT Acno
FROM [Tmain]
WHERE Arrmth in (2,3)
AND ([Yr]=Year(Date()) AND [Month]=Month(Date()))
OR ([Yr]=Year(DateAdd("m", -1, Date())) AND
[Month]=Month(DateAdd("m", -1,
Date())))
OR ([Yr]=Year(DateAdd("m", -2, Date())) AND [Month] =
Month(DateAdd("m", -2,
Date())))

Check my work, it's a lot easier when the computer is there to give you a
clear and unambiguous error message (the one you still haven't posted).
--

John W. Vinson [MVP]
.