I have done some internet research that offeres subqueries as a
solution for my problem, but the efficiency is not good.
I have a table with UNIT_NUMBER, YEAR, PERIOD_PROFILE.
I want a query, preferably using a user function, to sequentially
count the records with a given unit number and to start the count over
when the unit number changes. This number will help as I need to use a
dateadd() function that will utilize the count number.
Any help would be great. Here is the hopefuly result
UNIT_NUMBER
0301 1
0301 2
0301 3
0328 1
0328 2
0328 3
|
|
0
|
|
|
|
Reply
|
 Jdbash
|
6/5/2007 7:15:18 PM |
|
You are using MS SQL Server? you may use a query, or, with 2005, the RANK
syntax.
I assume there is a primary key somewhere in the table? I call it pk:
-- works with Jet or with MS SQL Server
SELECT a.unit_number, COUNT(*) As rankScope
FROM myTable AS a INNER JOIN myTable As b
ON a.unit_number = b.unit_number AND a.pk >= b.pk
GROUP BY a.unit_number
should to the job. It could turn to be much faster with the RANK syntax,
where the idea is to get the MIN of the computed RANK, over a GROUP of
unit_number. You don't need to involve the primary key using the
Row_number( ) rather than the Rank( ) :
-- untested -- Requires MS SQL Server 2005 --
WITH temp (rn, unit_number)
AS
(SELECT Row_Number( ) OVER(ORDER BY unit_number ) As rn, unit_number
FROM myTable)
SELECT a.unit_number, 1+a.rn-MIN(b.rn)
FROM temp As a INNER JOIN temp As b ON a.unit_number=b.unit_number
GROUP BY a.unit_number, a.rn
ORDER BY a.unit_number, 1+a.rn-MIN(b.rn)
Hoping it may help,
Vanderghast, Access MVP
"Jdbash" <jdbash@gmail.com> wrote in message
news:1181070918.090325.88590@q66g2000hsg.googlegroups.com...
>I have done some internet research that offeres subqueries as a
> solution for my problem, but the efficiency is not good.
>
> I have a table with UNIT_NUMBER, YEAR, PERIOD_PROFILE.
>
> I want a query, preferably using a user function, to sequentially
> count the records with a given unit number and to start the count over
> when the unit number changes. This number will help as I need to use a
> dateadd() function that will utilize the count number.
>
> Any help would be great. Here is the hopefuly result
>
> UNIT_NUMBER
>
> 0301 1
> 0301 2
> 0301 3
> 0328 1
> 0328 2
> 0328 3
>
|
|
|
0
|
|
|
|
Reply
|
Michel
|
6/5/2007 8:21:41 PM
|
|
On Jun 5, 4:21 pm, "Michel Walsh"
<vanderghastArobaseMsnDot...@nospam.com> wrote:
> You are using MS SQL Server? you may use a query, or, with 2005, the RANK
> syntax.
>
> I assume there is a primary key somewhere in the table? I call it pk:
>
> -- works with Jet or with MS SQL Server
> SELECT a.unit_number, COUNT(*) As rankScope
> FROM myTable AS a INNER JOIN myTable As b
> ON a.unit_number = b.unit_number AND a.pk >= b.pk
> GROUP BY a.unit_number
>
> should to the job. It could turn to be much faster with the RANK syntax,
> where the idea is to get the MIN of the computed RANK, over a GROUP of
> unit_number. You don't need to involve the primary key using the
> Row_number( ) rather than the Rank( ) :
>
> -- untested -- Requires MS SQL Server 2005 --
> WITH temp (rn, unit_number)
> AS
> (SELECT Row_Number( ) OVER(ORDER BY unit_number ) As rn, unit_number
> FROM myTable)
> SELECT a.unit_number, 1+a.rn-MIN(b.rn)
> FROM temp As a INNER JOIN temp As b ON a.unit_number=b.unit_number
> GROUP BY a.unit_number, a.rn
> ORDER BY a.unit_number, 1+a.rn-MIN(b.rn)
>
> Hoping it may help,
> Vanderghast, Access MVP
>
> "Jdbash" <jdb...@gmail.com> wrote in message
>
> news:1181070918.090325.88590@q66g2000hsg.googlegroups.com...
>
>
>
> >I have done some internet research that offeres subqueries as a
> > solution for my problem, but the efficiency is not good.
>
> > I have a table with UNIT_NUMBER, YEAR, PERIOD_PROFILE.
>
> > I want a query, preferably using a user function, to sequentially
> > count the records with a given unit number and to start the count over
> > when the unit number changes. This number will help as I need to use a
> > dateadd() function that will utilize the count number.
>
> > Any help would be great. Here is the hopefuly result
>
> > UNIT_NUMBER
>
> > 0301 1
> > 0301 2
> > 0301 3
> > 0328 1
> > 0328 2
> > 0328 3- Hide quoted text -
>
> - Show quoted text -
Mike,
Thanks for your reply and I didnt have luck with your statement, but I
will try again. The end goal of this exercise is to create a year_end
and year_start based on the lease_start and I will walk you through my
problem. this may prove to be a valuable post to others.
I would like to give you some background. There are two tables:
Tbl_Lease and Tbl_Lease_Detail.
Tbl_Lease has fields Lease_ID and Unit_Number both of which Lease_ID
is a autonumber and pk. Another field is the Lease_start_date.
Tbl_Lease_detail has fields Unit_Number,Year, Period_profile, Rent
The first query I have is a join between tbl_Lease and
tbl_Lease_Detail and I will only require fields needed in this convo
Here is a snippet of that joins results
Lease_ID Unit Lease_start Year Period
Period_profile
337 0310 4/1/1994 1 1 12 STANDARD- 12 MONTH YEAR
337 0310 4/1/1994 1 2 12 STANDARD- 12 MONTH YEAR
337 0310 4/1/1994 1 3 12 STANDARD- 12 MONTH YEAR
337 0310 4/1/1994 1 4 12 STANDARD- 12 MONTH YEAR
......
337 0310 4/1/1994 2 12
12 STANDARD- 12 MONTH YEAR
>From there I need to join the data to get a period_num. I could do
this by creating a table as follows to be used in another join
Year Period Period_Num
1 1 1
1 2 2
1 3 3
1 4 4
1 5 5
1 6 6
1 7 7
1 8 8
1 9 9
1 10 10
1 11 11
1 12 12
2 1 13
2 2 14
2 3 15
2 4 16
2 5 17
2 6 18
2 7 19
2 8 20
2 9 21
2 10 22
2 11 23
2 12 24
I could join the previous two queries to get the following
Lease_ID Unit Lease_start Year Period
Period_profile
Period_Num
337 0310 4/1/1994 1 1 12 STANDARD- 12 MONTH YEAR 1
337 0310 4/1/1994 1 2 12 STANDARD- 12 MONTH
YEAR 2
337 0310 4/1/1994 1 3 12 STANDARD- 12 MONTH YEAR 3
337 0310 4/1/1994 1 4 12 STANDARD- 12 MONTH YEAR 4
......
337 0310 4/1/1994 2 12
12 STANDARD- 12 MONTH YEAR 24
>From here I can use the DateAdd() Function to determine Date ranges
Lease_ID Unit Lease_start Year Period
Period_profile
Period_Num Period Start
337 0310 4/1/1994 1 1 12 STANDARD- 12 MONTH YEAR 1
DateAdd("m",(Period_num-1), Lease_start) 4/1/1994
337 0310 4/1/1994 1 2 12 STANDARD- 12 MONTH
YEAR 2 DateAdd("m",(Period_num-1), Lease_start)
5/1/1994
337 0310 4/1/1994 1 3 12 STANDARD- 12 MONTH YEAR 3
DateAdd("m",(Period_num-1), Lease_start) 6/1/1994
337 0310 4/1/1994 1 4 12 STANDARD- 12 MONTH YEAR 4
DateAdd("m",(Period_num-1), Lease_start) 7/1/1994
......
337 0310 4/1/1994 2 12
12 STANDARD- 12 MONTH YEAR 24 DateAdd("m",
(Period_num-1), Lease_start) 3/1/1996
The PROBLEM arises when my year is a "partial year". When it is a
partial year, I cant use a lookup table to determine the Period_Num
Lease_ID Unit Lease_start Year Period
Period_profile
Period_Num Period Start
337 0319 4/1/1994 1 1 5 STANDARD- 5 MONTH YEAR 1
DateAdd("m",(Period_num-1), Lease_start) 4/1/1994
337 0319 4/1/1994 1 2 5 STANDARD- 5 MONTH
YEAR 2 DateAdd("m",(Period_num-1), Lease_start)
5/1/1994
337 0319 4/1/1994 1 3 5 STANDARD- 5 MONTH YEAR 3
DateAdd("m",(Period_num-1), Lease_start) 6/1/1994
337 0319 4/1/1994 1 4 5 STANDARD- 5 MONTH YEAR 4
DateAdd("m",(Period_num-1), Lease_start) 7/1/1994
......
337 0310 4/1/1994 2 1
12 STANDARD- 5 MONTH YEAR 13 DateAdd("m",
(Period_num-1), Lease_start) 4/1/1995
You can see I am missing periods 5-12. The correct method would show
as follows:
Lease_ID Unit Lease_start Year Period
Period_profile
Period_Num Period Start
337 0319 4/1/1994 1 1 5 STANDARD- 5 MONTH YEAR 1
DateAdd("m",(Period_num-1), Lease_start) 4/1/1994
337 0319 4/1/1994 1 2 5 STANDARD- 5 MONTH
YEAR 2 DateAdd("m",(Period_num-1), Lease_start)
5/1/1994
337 0319 4/1/1994 1 3 5 STANDARD- 5 MONTH YEAR 3
DateAdd("m",(Period_num-1), Lease_start) 6/1/1994
337 0319 4/1/1994 1 4 5 STANDARD- 5 MONTH YEAR 4
DateAdd("m",(Period_num-1), Lease_start) 7/1/1994
......
337 0310 4/1/1994 2 1
12 STANDARD- 5 MONTH YEAR 5 DateAdd("m",
(Period_num-1), Lease_start) 8/1/1994
Keep in mind that in this table I have 400 units with ~ 240 to 360
periods each. When designed, partial-years werent known about.
I need something that sequentially counts the records and is provided
as a result in the query so that my Date_add will work.
thanks in advance for any help.;
|
|
|
0
|
|
|
|
Reply
|
Jdbash
|
6/5/2007 9:19:31 PM
|
|
The newsreader make it very hard to follow. I will try to come back asap.
Vanderghast, Access MVP
"Jdbash" <jdbash@gmail.com> wrote in message
news:1181078371.882706.30830@p47g2000hsd.googlegroups.com...
> On Jun 5, 4:21 pm, "Michel Walsh"
> <vanderghastArobaseMsnDot...@nospam.com> wrote:
>> You are using MS SQL Server? you may use a query, or, with 2005, the RANK
>> syntax.
>>
>> I assume there is a primary key somewhere in the table? I call it pk:
>>
>> -- works with Jet or with MS SQL Server
>> SELECT a.unit_number, COUNT(*) As rankScope
>> FROM myTable AS a INNER JOIN myTable As b
>> ON a.unit_number = b.unit_number AND a.pk >= b.pk
>> GROUP BY a.unit_number
>>
>> should to the job. It could turn to be much faster with the RANK syntax,
>> where the idea is to get the MIN of the computed RANK, over a GROUP of
>> unit_number. You don't need to involve the primary key using the
>> Row_number( ) rather than the Rank( ) :
>>
>> -- untested -- Requires MS SQL Server 2005 --
>> WITH temp (rn, unit_number)
>> AS
>> (SELECT Row_Number( ) OVER(ORDER BY unit_number ) As rn, unit_number
>> FROM myTable)
>> SELECT a.unit_number, 1+a.rn-MIN(b.rn)
>> FROM temp As a INNER JOIN temp As b ON a.unit_number=b.unit_number
>> GROUP BY a.unit_number, a.rn
>> ORDER BY a.unit_number, 1+a.rn-MIN(b.rn)
>>
>> Hoping it may help,
>> Vanderghast, Access MVP
>>
>> "Jdbash" <jdb...@gmail.com> wrote in message
>>
>> news:1181070918.090325.88590@q66g2000hsg.googlegroups.com...
>>
>>
>>
>> >I have done some internet research that offeres subqueries as a
>> > solution for my problem, but the efficiency is not good.
>>
>> > I have a table with UNIT_NUMBER, YEAR, PERIOD_PROFILE.
>>
>> > I want a query, preferably using a user function, to sequentially
>> > count the records with a given unit number and to start the count over
>> > when the unit number changes. This number will help as I need to use a
>> > dateadd() function that will utilize the count number.
>>
>> > Any help would be great. Here is the hopefuly result
>>
>> > UNIT_NUMBER
>>
>> > 0301 1
>> > 0301 2
>> > 0301 3
>> > 0328 1
>> > 0328 2
>> > 0328 3- Hide quoted text -
>>
>> - Show quoted text -
>
> Mike,
>
> Thanks for your reply and I didnt have luck with your statement, but I
> will try again. The end goal of this exercise is to create a year_end
> and year_start based on the lease_start and I will walk you through my
> problem. this may prove to be a valuable post to others.
>
> I would like to give you some background. There are two tables:
> Tbl_Lease and Tbl_Lease_Detail.
>
> Tbl_Lease has fields Lease_ID and Unit_Number both of which Lease_ID
> is a autonumber and pk. Another field is the Lease_start_date.
>
> Tbl_Lease_detail has fields Unit_Number,Year, Period_profile, Rent
>
> The first query I have is a join between tbl_Lease and
> tbl_Lease_Detail and I will only require fields needed in this convo
>
> Here is a snippet of that joins results
>
> Lease_ID Unit Lease_start Year Period
> Period_profile
>
> 337 0310 4/1/1994 1 1 12 STANDARD- 12 MONTH YEAR
> 337 0310 4/1/1994 1 2 12 STANDARD- 12 MONTH YEAR
> 337 0310 4/1/1994 1 3 12 STANDARD- 12 MONTH YEAR
> 337 0310 4/1/1994 1 4 12 STANDARD- 12 MONTH YEAR
> .....
> 337 0310 4/1/1994 2 12
> 12 STANDARD- 12 MONTH YEAR
>
>
>
>
>>From there I need to join the data to get a period_num. I could do
> this by creating a table as follows to be used in another join
>
> Year Period Period_Num
> 1 1 1
> 1 2 2
> 1 3 3
> 1 4 4
> 1 5 5
> 1 6 6
> 1 7 7
> 1 8 8
> 1 9 9
> 1 10 10
> 1 11 11
> 1 12 12
> 2 1 13
> 2 2 14
> 2 3 15
> 2 4 16
> 2 5 17
> 2 6 18
> 2 7 19
> 2 8 20
> 2 9 21
> 2 10 22
> 2 11 23
> 2 12 24
>
>
> I could join the previous two queries to get the following
>
>
> Lease_ID Unit Lease_start Year Period
> Period_profile
> Period_Num
>
> 337 0310 4/1/1994 1 1 12 STANDARD- 12 MONTH YEAR 1
> 337 0310 4/1/1994 1 2 12 STANDARD- 12 MONTH
> YEAR 2
> 337 0310 4/1/1994 1 3 12 STANDARD- 12 MONTH YEAR 3
> 337 0310 4/1/1994 1 4 12 STANDARD- 12 MONTH YEAR 4
> .....
> 337 0310 4/1/1994 2 12
> 12 STANDARD- 12 MONTH YEAR 24
>
>
>
>
>>From here I can use the DateAdd() Function to determine Date ranges
>
>
> Lease_ID Unit Lease_start Year Period
> Period_profile
> Period_Num Period Start
>
> 337 0310 4/1/1994 1 1 12 STANDARD- 12 MONTH YEAR 1
> DateAdd("m",(Period_num-1), Lease_start) 4/1/1994
> 337 0310 4/1/1994 1 2 12 STANDARD- 12 MONTH
> YEAR 2 DateAdd("m",(Period_num-1), Lease_start)
> 5/1/1994
> 337 0310 4/1/1994 1 3 12 STANDARD- 12 MONTH YEAR 3
> DateAdd("m",(Period_num-1), Lease_start) 6/1/1994
> 337 0310 4/1/1994 1 4 12 STANDARD- 12 MONTH YEAR 4
> DateAdd("m",(Period_num-1), Lease_start) 7/1/1994
> .....
> 337 0310 4/1/1994 2 12
> 12 STANDARD- 12 MONTH YEAR 24 DateAdd("m",
> (Period_num-1), Lease_start) 3/1/1996
>
>
>
> The PROBLEM arises when my year is a "partial year". When it is a
> partial year, I cant use a lookup table to determine the Period_Num
>
> Lease_ID Unit Lease_start Year Period
> Period_profile
> Period_Num Period Start
>
> 337 0319 4/1/1994 1 1 5 STANDARD- 5 MONTH YEAR 1
> DateAdd("m",(Period_num-1), Lease_start) 4/1/1994
> 337 0319 4/1/1994 1 2 5 STANDARD- 5 MONTH
> YEAR 2 DateAdd("m",(Period_num-1), Lease_start)
> 5/1/1994
> 337 0319 4/1/1994 1 3 5 STANDARD- 5 MONTH YEAR 3
> DateAdd("m",(Period_num-1), Lease_start) 6/1/1994
> 337 0319 4/1/1994 1 4 5 STANDARD- 5 MONTH YEAR 4
> DateAdd("m",(Period_num-1), Lease_start) 7/1/1994
> .....
>
> 337 0310 4/1/1994 2 1
> 12 STANDARD- 5 MONTH YEAR 13 DateAdd("m",
> (Period_num-1), Lease_start) 4/1/1995
>
> You can see I am missing periods 5-12. The correct method would show
> as follows:
>
> Lease_ID Unit Lease_start Year Period
> Period_profile
> Period_Num Period Start
>
> 337 0319 4/1/1994 1 1 5 STANDARD- 5 MONTH YEAR 1
> DateAdd("m",(Period_num-1), Lease_start) 4/1/1994
> 337 0319 4/1/1994 1 2 5 STANDARD- 5 MONTH
> YEAR 2 DateAdd("m",(Period_num-1), Lease_start)
> 5/1/1994
> 337 0319 4/1/1994 1 3 5 STANDARD- 5 MONTH YEAR 3
> DateAdd("m",(Period_num-1), Lease_start) 6/1/1994
> 337 0319 4/1/1994 1 4 5 STANDARD- 5 MONTH YEAR 4
> DateAdd("m",(Period_num-1), Lease_start) 7/1/1994
> .....
>
> 337 0310 4/1/1994 2 1
> 12 STANDARD- 5 MONTH YEAR 5 DateAdd("m",
> (Period_num-1), Lease_start) 8/1/1994
>
>
>
> Keep in mind that in this table I have 400 units with ~ 240 to 360
> periods each. When designed, partial-years werent known about.
> I need something that sequentially counts the records and is provided
> as a result in the query so that my Date_add will work.
>
> thanks in advance for any help.;
>
|
|
|
0
|
|
|
|
Reply
|
Michel
|
6/6/2007 1:36:31 PM
|
|
I must say that I have a hard time to see the connection with the initial
problem... :-)
Vanderghast, Access MVP
"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
news:uzQbH$DqHHA.3892@TK2MSFTNGP05.phx.gbl...
> The newsreader make it very hard to follow. I will try to come back asap.
>
>
> Vanderghast, Access MVP
>
>
> "Jdbash" <jdbash@gmail.com> wrote in message
> news:1181078371.882706.30830@p47g2000hsd.googlegroups.com...
>> On Jun 5, 4:21 pm, "Michel Walsh"
>> <vanderghastArobaseMsnDot...@nospam.com> wrote:
>>> You are using MS SQL Server? you may use a query, or, with 2005, the
>>> RANK
>>> syntax.
>>>
>>> I assume there is a primary key somewhere in the table? I call it pk:
>>>
>>> -- works with Jet or with MS SQL Server
>>> SELECT a.unit_number, COUNT(*) As rankScope
>>> FROM myTable AS a INNER JOIN myTable As b
>>> ON a.unit_number = b.unit_number AND a.pk >= b.pk
>>> GROUP BY a.unit_number
>>>
>>> should to the job. It could turn to be much faster with the RANK syntax,
>>> where the idea is to get the MIN of the computed RANK, over a GROUP of
>>> unit_number. You don't need to involve the primary key using the
>>> Row_number( ) rather than the Rank( ) :
>>>
>>> -- untested -- Requires MS SQL Server 2005 --
>>> WITH temp (rn, unit_number)
>>> AS
>>> (SELECT Row_Number( ) OVER(ORDER BY unit_number ) As rn, unit_number
>>> FROM myTable)
>>> SELECT a.unit_number, 1+a.rn-MIN(b.rn)
>>> FROM temp As a INNER JOIN temp As b ON a.unit_number=b.unit_number
>>> GROUP BY a.unit_number, a.rn
>>> ORDER BY a.unit_number, 1+a.rn-MIN(b.rn)
>>>
>>> Hoping it may help,
>>> Vanderghast, Access MVP
>>>
>>> "Jdbash" <jdb...@gmail.com> wrote in message
>>>
>>> news:1181070918.090325.88590@q66g2000hsg.googlegroups.com...
>>>
>>>
>>>
>>> >I have done some internet research that offeres subqueries as a
>>> > solution for my problem, but the efficiency is not good.
>>>
>>> > I have a table with UNIT_NUMBER, YEAR, PERIOD_PROFILE.
>>>
>>> > I want a query, preferably using a user function, to sequentially
>>> > count the records with a given unit number and to start the count over
>>> > when the unit number changes. This number will help as I need to use a
>>> > dateadd() function that will utilize the count number.
>>>
>>> > Any help would be great. Here is the hopefuly result
>>>
>>> > UNIT_NUMBER
>>>
>>> > 0301 1
>>> > 0301 2
>>> > 0301 3
>>> > 0328 1
>>> > 0328 2
>>> > 0328 3- Hide quoted text -
>>>
>>> - Show quoted text -
>>
>> Mike,
>>
>> Thanks for your reply and I didnt have luck with your statement, but I
>> will try again. The end goal of this exercise is to create a year_end
>> and year_start based on the lease_start and I will walk you through my
>> problem. this may prove to be a valuable post to others.
>>
>> I would like to give you some background. There are two tables:
>> Tbl_Lease and Tbl_Lease_Detail.
>>
>> Tbl_Lease has fields Lease_ID and Unit_Number both of which Lease_ID
>> is a autonumber and pk. Another field is the Lease_start_date.
>>
>> Tbl_Lease_detail has fields Unit_Number,Year, Period_profile, Rent
>>
>> The first query I have is a join between tbl_Lease and
>> tbl_Lease_Detail and I will only require fields needed in this convo
>>
>> Here is a snippet of that joins results
>>
>> Lease_ID Unit Lease_start Year Period
>> Period_profile
>>
>> 337 0310 4/1/1994 1 1 12 STANDARD- 12 MONTH YEAR
>> 337 0310 4/1/1994 1 2 12 STANDARD- 12 MONTH YEAR
>> 337 0310 4/1/1994 1 3 12 STANDARD- 12 MONTH YEAR
>> 337 0310 4/1/1994 1 4 12 STANDARD- 12 MONTH YEAR
>> .....
>> 337 0310 4/1/1994 2 12
>> 12 STANDARD- 12 MONTH YEAR
>>
>>
>>
>>
>>>From there I need to join the data to get a period_num. I could do
>> this by creating a table as follows to be used in another join
>>
>> Year Period Period_Num
>> 1 1 1
>> 1 2 2
>> 1 3 3
>> 1 4 4
>> 1 5 5
>> 1 6 6
>> 1 7 7
>> 1 8 8
>> 1 9 9
>> 1 10 10
>> 1 11 11
>> 1 12 12
>> 2 1 13
>> 2 2 14
>> 2 3 15
>> 2 4 16
>> 2 5 17
>> 2 6 18
>> 2 7 19
>> 2 8 20
>> 2 9 21
>> 2 10 22
>> 2 11 23
>> 2 12 24
>>
>>
>> I could join the previous two queries to get the following
>>
>>
>> Lease_ID Unit Lease_start Year Period
>> Period_profile
>> Period_Num
>>
>> 337 0310 4/1/1994 1 1 12 STANDARD- 12 MONTH YEAR 1
>> 337 0310 4/1/1994 1 2 12 STANDARD- 12 MONTH
>> YEAR 2
>> 337 0310 4/1/1994 1 3 12 STANDARD- 12 MONTH YEAR 3
>> 337 0310 4/1/1994 1 4 12 STANDARD- 12 MONTH YEAR 4
>> .....
>> 337 0310 4/1/1994 2 12
>> 12 STANDARD- 12 MONTH YEAR 24
>>
>>
>>
>>
>>>From here I can use the DateAdd() Function to determine Date ranges
>>
>>
>> Lease_ID Unit Lease_start Year Period
>> Period_profile
>> Period_Num Period Start
>>
>> 337 0310 4/1/1994 1 1 12 STANDARD- 12 MONTH YEAR 1
>> DateAdd("m",(Period_num-1), Lease_start) 4/1/1994
>> 337 0310 4/1/1994 1 2 12 STANDARD- 12 MONTH
>> YEAR 2 DateAdd("m",(Period_num-1), Lease_start)
>> 5/1/1994
>> 337 0310 4/1/1994 1 3 12 STANDARD- 12 MONTH YEAR 3
>> DateAdd("m",(Period_num-1), Lease_start) 6/1/1994
>> 337 0310 4/1/1994 1 4 12 STANDARD- 12 MONTH YEAR 4
>> DateAdd("m",(Period_num-1), Lease_start) 7/1/1994
>> .....
>> 337 0310 4/1/1994 2 12
>> 12 STANDARD- 12 MONTH YEAR 24 DateAdd("m",
>> (Period_num-1), Lease_start) 3/1/1996
>>
>>
>>
>> The PROBLEM arises when my year is a "partial year". When it is a
>> partial year, I cant use a lookup table to determine the Period_Num
>>
>> Lease_ID Unit Lease_start Year Period
>> Period_profile
>> Period_Num Period Start
>>
>> 337 0319 4/1/1994 1 1 5 STANDARD- 5 MONTH YEAR 1
>> DateAdd("m",(Period_num-1), Lease_start) 4/1/1994
>> 337 0319 4/1/1994 1 2 5 STANDARD- 5 MONTH
>> YEAR 2 DateAdd("m",(Period_num-1), Lease_start)
>> 5/1/1994
>> 337 0319 4/1/1994 1 3 5 STANDARD- 5 MONTH YEAR 3
>> DateAdd("m",(Period_num-1), Lease_start) 6/1/1994
>> 337 0319 4/1/1994 1 4 5 STANDARD- 5 MONTH YEAR 4
>> DateAdd("m",(Period_num-1), Lease_start) 7/1/1994
>> .....
>>
>> 337 0310 4/1/1994 2 1
>> 12 STANDARD- 5 MONTH YEAR 13 DateAdd("m",
>> (Period_num-1), Lease_start) 4/1/1995
>>
>> You can see I am missing periods 5-12. The correct method would show
>> as follows:
>>
>> Lease_ID Unit Lease_start Year Period
>> Period_profile
>> Period_Num Period Start
>>
>> 337 0319 4/1/1994 1 1 5 STANDARD- 5 MONTH YEAR 1
>> DateAdd("m",(Period_num-1), Lease_start) 4/1/1994
>> 337 0319 4/1/1994 1 2 5 STANDARD- 5 MONTH
>> YEAR 2 DateAdd("m",(Period_num-1), Lease_start)
>> 5/1/1994
>> 337 0319 4/1/1994 1 3 5 STANDARD- 5 MONTH YEAR 3
>> DateAdd("m",(Period_num-1), Lease_start) 6/1/1994
>> 337 0319 4/1/1994 1 4 5 STANDARD- 5 MONTH YEAR 4
>> DateAdd("m",(Period_num-1), Lease_start) 7/1/1994
>> .....
>>
>> 337 0310 4/1/1994 2 1
>> 12 STANDARD- 5 MONTH YEAR 5 DateAdd("m",
>> (Period_num-1), Lease_start) 8/1/1994
>>
>>
>>
>> Keep in mind that in this table I have 400 units with ~ 240 to 360
>> periods each. When designed, partial-years werent known about.
>> I need something that sequentially counts the records and is provided
>> as a result in the query so that my Date_add will work.
>>
>> thanks in advance for any help.;
>>
>
>
|
|
|
0
|
|
|
|
Reply
|
Michel
|
6/6/2007 4:59:54 PM
|
|
|
4 Replies
524 Views
(page loaded in 0.066 seconds)
Similiar Articles: countif object - microsoft.public.excel.worksheet.functions ...... way to use a count function for objects? I have a series of textboxes in rows and columns that will be moved around freely. I need to be able to have a running count ... Field with running count of records - microsoft.public.access ...RUNNING COUNT Function - microsoft.public.access.queries ... Tbl_Lease_detail has fields Unit_Number,Year, Period_profile, Rent ... When to use the function (instead of on ... how to add a running word count - microsoft.public.word ...RUNNING COUNT Function - microsoft.public.access.queries ... can I add a link in Publisher to display, "join our email ... RUNNING COUNT Function - microsoft.public.access ... Running total of counts open/closed - microsoft.public.access ...Public Function FirstOfMonth(InputDate As Variant) ' Return the first day of the ... Cursor to Solve the Running Total Problem ... Update" Running Total and Running Count. How to add a "running count" to cells with matching data ...I want to copy column A (below) into Column B and add a running count. As long as ... Excel Count Functions -- Count Excel Cells... to count Excel text, numbers, blank cells. Need RANK Function for Text - microsoft.public.excel.worksheet ...RUNNING COUNT Function - microsoft.public.access.queries ... This number will help as I need to use a dateadd() function ... 0328 3- Hide quoted text ... Count number of checked boxes on a form - microsoft.public.access ...RUNNING COUNT Function - microsoft.public.access.queries ... Count number of checked ... that will count how many check boxes on your form are ... how to add a running ... Table lookup function in Access - microsoft.public.access ...When it is a partial year, I cant use a lookup table to ... RUNNING COUNT Function - microsoft.public.access.queries ... Pivot Table Tutorial List; With ... Counting cells with particular month and year - microsoft.public ...RUNNING COUNT Function - microsoft.public.access.queries ... 337 0310 4/1/1994 1 1 12 STANDARD- 12 MONTH YEAR 337 0310 4/1/1994 1 2 12 ... public.excel.programming ... can I add a link in Publisher to display, "join our email ...I am creating a fairly simply website and need to add a button for visitors to join our email list. Is this possible in Publisher? ... RUNNING COUNT Function DataBase - DataBase Discussion List Tuesday ...I have done some internet research that offeres subqueries as a solution for my problem, but the efficiency is not good. I have a table with UNIT_NUMBER, count & running-count - Interview Questions Open Database ...Cognos - count & running-count what is the difference between count and running-count functions?. 1 Answers are available for this question. How to Do a Running Count on an Excel Spreadsheet | eHow.comExcel 2010 spreadsheets are made up of numerous cells where you can input information. You can then use one of the numerous mathematical functions available in the ... Running Count Function - Wrox Programmer ForumsI have done some internet research that offeres subqueries as a solution for my problem, but the efficiency is not good. I have a table with running-count function - C O G N O i S e - the COGNOS community ...Hi All, Is there any alternative to running-count function Reportnet, i am trying to implement alternate row colors to a report. we are getting running-count(filed ... 7/24/2012 3:36:20 AM
|
Search |
Post Question |
Groups |
Stream |
About |
Register
16 followers. 