Increment start number values by group wise row depending on Quantity Of Leaves in Access (1 Viewer)

[kamrulbd]

I am in stuck to make increment startno values by group wise (row) depending on quantity of leaves in Access. I have attached my work. Any help will be appreciated.

 

[CJ_London]

You haven't said what the issue is but the only issue I can see is you are not including n in your select part of the query.

 

[kamrulbd]

Thank you for quick response. I want to repeat the group wise row level value and only start no will increment depending on No of leaves quantity. I have temporary table called number. In query and report everything is ok except incrementing start_no values.

 

[CJ_London]

show an example of what you mean - and did you try the fix I suggested?

 

[kamrulbd]

I tried But couldn't solve it. Would you please send me the complete query code.

 

[arnelgp]

see Query1.

 

[kamrulbd]

Dear arnelgp, You are simply great. You solved my problem. Many Many thanks.

 

[Minty]

I have to wonder what the end goal of this is?

 

[kamrulbd]

see Query1.

I put huge data on the program like 7 digit on number table. But it takes long time to execute or some time it crashes. Is there any other way to increment startno without depending on number field. And if any alpha numeric character added then how it will increment like A1230001, A1230002, A1230003... depending on field quantity. Thank you

 

[arnelgp]

post a sample db of the table you want to work with.
also, you add Index to column 'n'.

 

[kamrulbd]

post a sample db of the table you want to work with.
also, you add Index to column 'n'.

Hello. My table doesn't execute any value if n value does not have 7 digit number. Because "start no" have 7 digit number. if I put 9999999 on n field the database size climbs to more then 100MB. I have made little changes on query. But it takes long time to execute. Also can I have A1230001, A1230002, A1230003... output. please have a look. Thank you.

 

[CJ_London]

still no idea what you are trying to achieve. Yo want a start number of 9432100 but also want A1230001. I'll ask a final time - provide an example of what you actually require

e.g.

customer....starto........noOfLeaves....require
1....................9432100...5..........................????
1....................9432100...5..........................????
1....................9432100...5..........................????
1....................9432100...5..........................????
1....................9432100...5..........................????
2....................5432110...3..........................????
2....................5432110...3..........................????
2....................5432110...3..........................????

 

[kamrulbd]

still no idea what you are trying to achieve. Yo want a start number of 9432100 but also want A1230001. I'll ask a final time - provide an example of what you actually require

e.g.

customer....starto........noOfLeaves....require
1....................9432100...5..........................????
1....................9432100...5..........................????
1....................9432100...5..........................????
1....................9432100...5..........................????
1....................9432100...5..........................????
2....................5432110...3..........................????
2....................5432110...3..........................????
2....................5432110...3..........................????

my 'start no' will increment depending on noofleaves like 9432100, 9432101,..94321004, then next row. If my 'start no' start with A then output will be like A9432100, A9432101,..A94321004. then next row. Thank you

 

[CJ_London]

its a simple request - just fill in the ????

best guess - note I have changed startno to text (because you have preceding zeros and a potential "A") and removed formatting (which you should not use in tables). See query 2

 

[gemma-the-husky]

I presume you are trying to increment a number in an existing sequence. How many characters do you have in the search sequence.

eg, Say you have 9432100, 9432101,..94321004
and you add leaves until you get to 94321009, and then add 94321010
What happens now?

You now have two sequences of numbers, one that starts - 9432100- and one that starts 9432101-
If this is really only one sequence, then you need to know what the sequence is - is it 9432100, 9432101, or even just 9432-

You could split this into two
Have a prefix 9432, and than have a sequential number incrementing from 001, to 999.

 

[kamrulbd]

its a simple request - just fill in the ????

best guess - note I have changed startno to text (because you have preceding zeros and a potential "A") and removed formatting (which you should not use in tables). See query 2

its a simple request - just fill in the ????

best guess - note I have changed startno to text (because you have preceding zeros and a potential "A") and removed formatting (which you should not use in tables). See query 2

I presume you are trying to increment a number in an existing sequence. How many characters do you have in the search sequence.

eg, Say you have 9432100, 9432101,..94321004
and you add leaves until you get to 94321009, and then add 94321010
What happens now?

You now have two sequences of numbers, one that starts - 9432100- and one that starts 9432101-
If this is really only one sequence, then you need to know what the sequence is - is it 9432100, 9432101, or even just 9432-

You could split this into two
Have a prefix 9432, and than have a sequential number incrementing from 001, to 999.

My sequence is 9432100, 9432101.. from 'start no' and count till quantity of 'noofleaves'. Total 7 digit is count. If my 'start no' is 9432105 then out put will be 9432105, 9432106, 9432107... depending on quantity of 'noofleaves'. Sometime alfanumeric value may come like CC9432105 then output will be CC9432105, CC9432106, CC9432107...

 

[CJ_London]

OK - well, no response on my suggestion and still just a description of what is required, not an example, so I'm going to drop out. Good luck with your project but we are 16 posts in still with no clear idea of what is actually required

 

[kamrulbd]

OK - well, no response on my suggestion and still just a description of what is required, not an example, so I'm going to drop out. Good luck with your project but we are 16 posts in still with no clear idea of what is actually required

Dear CJ please don't take it personally. I was working on your post. Your incremented value does not create from 'startno'. its just added a number which is not my requirement. Mr. arnelgp provide me the exact I wanted but my table is getting heavier.

 

[gemma-the-husky]

What you really need to do is a dmax for numbers starting with your sequence.

The problem is for what you are doing you really need the starting sequence to be a string
You might be able to use numbers with a bit of fiddling, but numbers are right justified, not left justified, so it's awkward.

Now, you can't say dmax (numbers starting with 9432100) because that will only return 9432100
You have to say dmax(numbers starting with eg 9432), which is why you need the numbers as strings, not numbers.

However, once you go above 10 items. or 100 items in a sequence , you will have an issue because now you will have 9432000 and 9432010. If you go above 100, you have 9432100, so you can never be sure you are using the corect prefix to find the highest number so far.

So ideally you want to split the "numbers" into two.
One part is the prefix, the second part is a sequential number.

So given a prefix of 9432, the maximum sequence might be 178, which you can find with a simple dmax.
Now you can have Alpha prefixes as well, such as CC123. The prefix can be either a string or a number, but strings are more useful, I think.

dmax(sequence, mytable, "prefix = " & "9432")

To present the data you just stitch the prefix and number together. You want to keep the sequence reference as a fixed number of digits, so

prefix & format(sequence,"000"), which works to a maximum sequence of 999. (It works for higher numbers, but generally you want a fixed length result.

So this changes 9432 and 26 to a string of 9432026 and
changes 9432 and 126 to a string of 9432126, both 7 digits long.

 

[kamrulbd]

Not all of my row will start from 9432... I am getting data sometimes 1004 rows in a table with different 7 digit number. In my attachment startno and end no difference is the value of qty.