Dear friends,
I would like to make a database for myself to keep up to date what I have on stock.
I have 2 tables for it
1.) Summarized data of movement
2.) Details of items
in Summarized table I have Movement ID, and summarized data, notes, brut value net value, date etc. …
In Details table I have the Movement ID, row ID, item price, number of pcs, notes, etc. …
When I make a new movement I need a Movement ID what can be generated by auto increment value in summarized table. It is not a problem because when I create a new record it automatically generates the next value, and it can be linked with the Details table.
In Summarized table the Movement ID is the primary key and since each movement is 1 row the autoincrement is ok for me.
In Details table I have a first and second key and they are together the key to find the movement details.
For example:
1 movement has 1,2,3 rows so in details table we have 1-1, 1-2, 1-3,
2 movement has only 1 row so in details table we have 2-1 and thats all
3 movement has 1,2,3,4,5 rows so we have 3-1, 3-2, 3-4, 3-5 in details table
and so on…
My problem is the Details table Row ID. 
I have a form with a subform where I can make a new movement, and I can add the rows for that movement.
It works when I manually set the row number, but it would be nice to set the “row id” to previous “row id+1”
and write it into next record Row id cell.
How can I generate an incremented value for each new movement which is set to 1 when a new movement begins?
I don’t think second autoincrement would be a good idea because I need to start from 1 each time.
The database is in mariadb so resetting can cause problems thanks to rihgts. I mean the root has rights for
everything, but the user may not have rights to resetting or flushing, or trunc or I don’t know…
I am thinking about it for a few days but I can’t find a solution to do it.
please if anybody can help I would be very greatful to guide me how to start.
Thanks in advance