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