Sequential number

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

I need to create a sequential numbers like this: 1, 1, 1, 2, 2, 2,
...............1, 1, 1, 2, 2, 2, 3. Can someone advise me how to accomplish
it.

Thanks,

Ray
 
Hi Ray,

can you be a bit more specific so that I can understand what you are
trying to achieve.

Regards

Alex
 
Alex,

I need to create cyclic numbers for each period of 4 weeks on a field.
There are 4 records to contain week number but the period field on the
remains the same for those 4 records. Thus, 4 1s, 4 2s and reach the 4th 13
then return to period 1. The pattern the period field is

1
1
1
1
2
2
2
2
3
3
3
3
|
13
13
13
13
1
1
1
1
2
2
2
2

Thanks,

Ray
 
Perhaps this, which will take an ordinary incrementing value and return
the cylclical pattern you want:

Public Function CyclicNumber(ByVal N As Long) As Long
CyclicNumber = Int(((N - 1) Mod 52#) / 4) + 1
End Function
 
John,

Thanks for your code. Would you mind telling me how to use it to get the
required values.

Thanks,

Ray
 
On the basis of the information you've provided so far it's not possible
to give detailed instructions. As you've no doubt discovered, the
expression in the code I gave you requires an incrementing number, e.g.

Input 1 2 3 4 5 6 7 8 9 10 11 12 13
Output 1 1 1 1 2 2 2 2 3 3 3 3 4

One way to use it would be in a calculated field in a query. In place of
the N in the function version, you'd use a subquery that generates a
sequential number. Here's an example (in SQL view) from my test
database:

SELECT
A.ID,
A.Patient,
A.[Montant demande],
(
SELECT COUNT(ID)
FROM [Balance Patient fournitures] AS B
WHERE B.ID < A.ID
) AS RC,
Int(((
SELECT COUNT(ID)
FROM [Balance Patient fournitures] AS C
WHERE C.ID < A.ID
) Mod 52)/4)+1 AS Cyclic
FROM [Balance patient fournitures] AS A
ORDER BY A.ID;

The table is [Balance patient fournitures]. Its primary key is [ID] (and
the query must be sorted in ascending order on the primary key).
[Patient] and [Montant demande] are two fields in the table.

The first expression in parentheses is a subquery that generates a
sequential number starting at 0 for the first record returned by the
query. It's just there so you can see it without it being wrapped in the
cyclic numbering expression.

The latter starts at "Int(" and continues through to "Cyclic". You can
see how the subquery simply replaces "N" in the VBA function version in
my last post. (There's a small difference: the function assumed that it
would be fed with a sequential number starting at 1, this one takes a
number starting at 0.

I suggest you experiment in your database, building queries and
subqueries until you get the result you need.





John,

Thanks for your code. Would you mind telling me how to use it to get the
required values.

Thanks,

Ray
Click to expand...
 
Back
Top