sequence number

  • Thread starter Thread starter skc
  • Start date Start date
S

skc

I have a field in my table called SeqNumber.

What I want this field to do is to increment itself by 1
if another record with the same reference number is
imported.

So:

RefNo SeqNo
2222 1
3333 1
2222 2
etc...

How do I do this?

skc
 
One of the rules of relational databases is that rows are unordered.

So if you want to know which was the first, second etc entry of that
particular value,
this is a quite tricky and you can not do it simply in Aceess as you do not
have a trigger for new, updated and deleted records.

The table is not the where you want to store that value as it can easily
become bad data.

You can easily get the number of duplicate values ie the count with a query:
SELECT RefNo, Count(RefNo) AS CountOfRefNo
FROM tblRefNos
GROUP BY RefNo;

but the only way I can think of achieving what you want is by itterating
through the recordset each time you want to display the data. something
like:
(using DAO)
stSQL="SELECT RefNo, Count(RefNo) AS CountOfRefNo " & _
"FROM tblRefNos GROUP BY RefNo;"
set rs1=db.openrecordset(stSQL, dbopensnaphot)
set rs2=db.openrecordset("tblRefNos", dbopendynaset)
with rs1
if not .eof then
.movefirst
do while not .eof
lnSeqNo=1
rs2.findfirst("RefNo = " & .RefNo)
if not rs2.nomatch then
do
rs2.edit
rs2!SeqNo=lnSeqNo
rs2.findnext("RefNo = " & .RefNo)
if rs2nomatch then
exit do
end if
lnSeqNo=lnSeqNo+1
loop
endif
.movenext
loop
end if
end with
set rs1=nothing
 
Back
Top