Creating sequencenumbers in query

[flaviooooo]

Hi,

I have a table containing Code-Cylinders. Each code has several cylinders, with some kind of sequencenumber.

Now it can happen that some codes have a duplicate sequencenumber, if for example a cylinder as used as backup.
It can also happen that a cylinder was removed, and a gap is in the sequence.

It looks a little like this:

DRUKCODE DRUKVORD CYL
A 1 xx
A 2 yy
A 3 zz
B 1 xy
B 2 yx
B 3 zx
B 3 xz
C 1 yxz
C 2 xyz
C 4 zyx

Now I wold like to add another field in a query, which gives me the real sequencenumber.

This would be like this:

DRUKCODE DRUKVORD CYL SEQ
A 1 xx 1
A 2 yy 2
A 3 zz 3
B 1 xy 1
B 2 yx 2
B 3 zx 3
B 3 xz 4
C 1 yxz 1
C 2 xyz 2
C 4 zyx 3

For this I have constructed this query, but it fails when a duplicate sequence is found:

SELECT DRUKCYL.DRUKCODE, DRUKCYL.DRUKVORD, DRUKCYL.CYLNUM, (SELECT Count([DRUKVORD]) AS seq FROM drukcyl AS d2 WHERE (((d2.DRUKVORD)<=[DRUKCYL].[DRUKVORD]) and  ((d2.DRUKCODE)=DRUKCYL.DRUKCODE))) AS SEQFROM DRUKCYL;

Does anyone have an idea on how to solve this?

 

[PHV]

A starting point:
SELECT A.DRUKCODE, A.DRUKVORD, A.CYLNUM, Count(*) AS SEQFROM
FROM drukcyl AS A INNER JOIN drukcyl AS B ON A.DRUKCODE = B.DRUKCODE
WHERE A.DRUKVORD & A.CYLNUM >= B.DRUKVORD & B.CYLNUM
GROUP BY A.DRUKCODE, A.DRUKVORD, A.CYLNUM

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[flaviooooo]

Hey PH,

thanks for the help.

This query seems to do the job, but fails when it gets to cylinders with numbers that contain 2 digits. 10 would come before 2 etc...

To try and solve this, I used an if-statement, adding a "0" to the beginning if the ordernr contains 1 digit, and it gives the right results now.

Unfortunately, the query runs mighty slow now...
Is there a more suitable way to deal with this?

Kind regards

 

[flaviooooo]

Hey,

to make things a bit more complicate, my boss asked me to do this as well:

when a cylinder ordernr appears several times, it should be duplicated as well.

Hopefully this example will make it clear:

DRUKCODE DRUKVORD CYL SEQ
A 1 xx 1
A 2 yy 2
A 3 zz 3
B 1 xy 1
B 2 yx 2
B 3 zx 3
B 3 xz 3
B 4 zx 4
B 5 xz 5
B 5 zx 5
B 6 xz 6

I hope this is possible??

Kidnd regards
Fabian