serial number - dynamic

[יריב החביב]

Hello,

How do i create the field 'cm' ?

CM zihooy newfrom newuntilx helkiyoot
1 2648 01/12/1998 30/06/2006 1
2 2648 01/07/2006 31/12/2006 0.25
3 2648 01/01/2007 31/12/2099 0.5
1 4941 01/04/1987 31/01/2007 0.5
2 4941 01/02/2007 31/12/2099 0.25
1 6817 01/02/2002 31/10/2005 1
2 6817 01/11/2005 31/03/2006 0.5
3 6817 01/04/2006 30/04/2006 0.6333
4 6817 01/05/2006 31/07/2007 1
5 6817 01/08/2007 31/12/2099 0.5



THANK YOU

 

[Wayne-I-M]

Me.CM = Nz(DMax("[CM]", "TableName", "[zihooy] = " & Me.zihooy)) + 1


Note set the default of CM to 0 or it will fail as null + 1 = null

 

[Ken Sheridan]

Don't create a field for this in the table; compute the cm values in a query.
That way they will always be correctly sequential regardless of any
additions, deletions or edits of rows in the table. T compute the value
count the number of rows where the zihooy value is the same as that for the
current row and the newfrom value is equal to or earlier than that in the
current row:

SELECT
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.zihooy = T1.zihooy
AND T2.newfrom <= T1.newfrom)
AS cm, zihooy, newfrom, newuntilx, helkiyoot
FROM YourTable AS T1
ORDER BY zihooy, newfrom;

Note how the two instances of the table are distinguished by giving them
aliases T1 and T2.

If two rows in the table have the same zihooy and newfrom values they'll
return the same cm value. You could avoid this by correlating the subquery
with the outer query on the newuntilx column also:

SELECT
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.zihooy = T1.zihooy
AND T2.newfrom <= T1.newfrom
AND T2.newuntilx <= T1.newuntilx)
AS cm, zihooy, newfrom, newuntilx, helkiyoot
FROM YourTable AS T1
ORDER BY zihooy, newfrom, newuntilx;

Unless there are any rows where all three columns have the same values of
course, in which case a solution would be to introduce an autonumber column
to the table, myID say, and correlate on that also:

SELECT
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.zihooy = T1.zihooy
AND T2.newfrom <= T1.newfrom
AND T2.newuntilx <= T1.newuntilx
AND T2.myID <= T1.myID)
AS cm, zihooy, newfrom, newuntilx, helkiyoot
FROM YourTable AS T1
ORDER BY zihooy, newfrom, newuntilx, myID;

Ken Sheridan
Stafford, England

 

[Fernando]

Hello,

How do i create the field 'cm' ?

CM zihooy newfrom newuntilx helkiyoot
1 2648 01/12/1998 30/06/2006 1
2 2648 01/07/2006 31/12/2006 0.25
3 2648 01/01/2007 31/12/2099 0.5
1 4941 01/04/1987 31/01/2007 0.5
2 4941 01/02/2007 31/12/2099 0.25
1 6817 01/02/2002 31/10/2005 1
2 6817 01/11/2005 31/03/2006 0.5
3 6817 01/04/2006 30/04/2006 0.6333
4 6817 01/05/2006 31/07/2007 1
5 6817 01/08/2007 31/12/2099 0.5



THANK YOU