Pattern Query

[zyus]

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

 

[KARL DEWEY]

Your YrMth is not in a usable format. If it is not derived from a DateTime
field but is a text field it needs to be formatted.
qryAcnoArrmth --
SELECT Acno
FROM [Tbl-SK]
WHERE (Arrmth Not Between 2 AND 3) AND (Left([YrMth], 4) & Right("0" &
Abs(Right([YrMth], 2)), 2) Between Format(Date(), "yyyymm") AND
Format(DateAdd("m", -2, Date()), "yyyymm"))
GROUP BY Acno;

SELECT Acno
FROM [Tbl-SK] LEFT JOIN qryAcnoArrmth ON [Tbl-SK].Acno = qryAcnoArrmth.Acno
WHERE qryAcnoArrmth.Acno Is Null
ORDER BY Acno;

 

[John W. Vinson]

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

SELECT Acno
FROM [Tbl-SK]
WHERE Arrmth in (2,3)
AND YrMth IN(Format(DateSerial(Year(Date()), Month(Date()), 1), "yyyy-m"),
Format(DateSerial(Year(Date()), Month(Date())-1, 1), "yyyy-m").
Format(DateSerial(Year(Date()), Month(Date())-2, 1), "yyyy-m"))

should work... and (I hope) the functions in the WHERE should only be called
once rather than being recalculated with every row. If not, you could
construct the SQL with literal values for the past three months as needed.

 

[Stefan Hoffmann]

hi John,

should work... and (I hope) the functions in the WHERE should only be called
once rather than being recalculated with every row. If not, you could
construct the SQL with literal values for the past three months as needed.

Yup, this is the case. Simple test:

The table RunningNumbers contains the numbers 1-100 in the field ID.

SELECT *
FROM RunningNumbers
WHERE GetRunningNumber(Now()) IN (ID)

vs.

SELECT *
FROM RunningNumbers
WHERE GetRunningNumber(Now(), [ID]) IN (ID)

The functions is defined as:

Public Function GetRunningNumber( _
Optional ADummy As Date, _
Optional ALong As Long, _
Optional AResetNumber As Boolean = False _
) As Long

Static currentNumber As Long

If AResetNumber Then
currentNumber = -1
End If

currentNumber = currentNumber + 1

GetRunningNumber = currentNumber

End Function

Use the third parameter to reset the static value between your tests in
the immediate window:

?GetRunningNumber(,,True)


mfG
--> stefan <--

 

[zyus]

Thanks for your response guy..

Try to adopt your solution but failed due to my actual field for YrMth in my
tbl-sk is as per below format. YrMth is derived from my query where i combine
yr&"-"&month.

Yr Month
2009 12
2010 1
2010 2

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

SELECT Acno
FROM [Tbl-SK]
WHERE Arrmth in (2,3)
AND YrMth IN(Format(DateSerial(Year(Date()), Month(Date()), 1), "yyyy-m"),
Format(DateSerial(Year(Date()), Month(Date())-1, 1), "yyyy-m").
Format(DateSerial(Year(Date()), Month(Date())-2, 1), "yyyy-m"))

should work... and (I hope) the functions in the WHERE should only be called
once rather than being recalculated with every row. If not, you could
construct the SQL with literal values for the past three months as needed.

 

[John W. Vinson]

Thanks for your response guy..

Try to adopt your solution but failed due to my actual field for YrMth in my
tbl-sk is as per below format. YrMth is derived from my query where i combine
yr&"-"&month.

Sorry; I was answering the question as you posted it, instead of intuiting a
different table structure. I'll try to get my crystal ball fixed.

Try

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

 

[zyus]

Dear John,
Try this query but error due to missing [,)....etc

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()))

Thanks for your response guy..

Try to adopt your solution but failed due to my actual field for YrMth in my
tbl-sk is as per below format. YrMth is derived from my query where i combine
yr&"-"&month.

Sorry; I was answering the question as you posted it, instead of intuiting a
different table structure. I'll try to get my crystal ball fixed.

Try

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

 

[John W. Vinson]

Dear John,
Try this query but error due to missing [,)....etc

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()))

I'm sorry, Zyus, but I have no clue what you meant. Was there and error
message? Is your field in fact named Month or Mth? If the latter the query
should use Mth = instead of Month =; if it's actually named Month, that's a
reserved word and in this context is almost certain to cause confusion with
the Month() function. You could try using [Month] = Month(DateAdd.... so the
brackets will tell the program that you mean the fieldname not the function.

 

[zyus]

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

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()))

Dear John,
Try this query but error due to missing [,)....etc

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()))

I'm sorry, Zyus, but I have no clue what you meant. Was there and error
message? Is your field in fact named Month or Mth? If the latter the query
should use Mth = instead of Month =; if it's actually named Month, that's a
reserved word and in this context is almost certain to cause confusion with
the Month() function. You could try using [Month] = Month(DateAdd.... so the
brackets will tell the program that you mean the fieldname not the function.
--

John W. Vinson [MVP]

.

 

[John W. Vinson]

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).

 

[zyus]

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

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]

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.

 

[De Jager]

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

 

[joelgeraldine]

lmml!;nn

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

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).