Delete Rows Base on Sequential Values And Add Quantities of Deleted Row to the Row That is Kept

[ProgrammerWannaBe]

Hello,


I have a database that I am putting together for a Ranch that grows trees. They have a planting plan for what rootstocks of those trees end up being planted and where and how many in each planting row. The plan tracks different treatments (things that are done to the rootstocks like soaking, etc.) that are done to the rootstocks so this plan may have the same rootstock in the same row but is signified as different by the planting order on the rootstock. This plan ends up being transferred to another table where the rootstock treatments don't matter, so as long as two planting rows with everything being equal (Ranch-Block-Row-subRow-Rootstock) and the planting order is sequential (1,2,..), I only need to keep the first row in the sequence (and add all totals from the deleted rows to the first row) but if there is a different rootstock in the third position and goes back to the same one for the first and second position in the fourth position, then the fourth position needs to be kept and not deleted. I have attached the image of what I am trying to get across. In the table here, I need a query that would delete the second row because the only difference between it and the first row is the planting order and it is touching (in sequence) with the first row AND the totals (not shown) from the second row should be added to the first. The third row would be kept and the fourth row would also be kept (even though it is the same as 1 and 2 but it is NOT in sequence).

Thanks in advance for any help you can provide.

[June7]

Implementing process in relational database that involves moving and/or deleting records and saving calculated data is bound to have issues. The worst of which is the data gets so messed up there is no way to determine what is correct.

Ideally, raw data is entered and queries manipulate data with filtering, sorting, calculating - the raw data is always available to support the output.

[ProgrammerWannaBe]

So not sure what I would do then, what is the recommendation?

[June7]

The recommendation is to build a relational database. Define the db entities and their relationships. Then build tables accordingly. Then build queries, forms, reports that allow entry and manipulation of data. Use code where needed to manage the processes. Unfortunately, I don't understand the rules you describe for this particular data manipulation.

[ProgrammerWannaBe]

Sorry if it was unclear. I do understand that you want to have relationships set up and create the database that way. Unfortunately, there is an odd situation here so, to try to make this less confusing: The trees are grown in two phases. In phase 1, a tree is planted that will end up just being the bottom of the final tree (the rootstock tree). In phase 2, there is a graft of another type of tree onto this first tree and the top of the first tree is trimmed off to allow the 2nd tree to grow. This essentially creates two trees in one-one is the roots, the other is the trunk, branches, leaves, and fruit. There is a table that tracks the rootstock trees and locations. There are instances where one planted row in the field can hold multiple rootstock tree types (type 1, 2, 3, ...). Each time there is a change in the rootstock type within the same planted row, there is another row in the Access table and the planting order is increased by one. In my example (screenshot above; ignoring the "subRow" for now because that is used in the second table to be discussed), you will see that there are 4 rootstock trees (first trees) planted. You will also notice that there are 3 of the same (Hansen 536) and 1 different (Lovell). The reason that the first two database rows are different (even though they look as if they have the same data) is that there is some quality between the two (like the source-where the trees came from) that makes it important enough to distinguish them in separate database rows for tracking for the rootstock tree table. The fourth rootstock tree could be exactly the same as either of the first two, but because there is a totally different type planted between it and the first two, it must be distinguished. Now comes the fun part. This table is imported into the budding table so that when the second tree is grafted onto the first tree, there is a record of doing so. The issue is that in this budding table, the differences between the first two database rows (Hansen 536) no longer matter, they need to be combined into one row with a combined quantity because as far as the grafters are concerned, it is the same rootstock tree. The grafters would then name that Row and subRow as 1 and A respectively (Row/subRow 1/A). The original third database row (Lovell) would need to become Row/subRow 1/B and because the original fourth database row (Hansen 536) is physically separated from the first 2 Hansen 536 that got combined, it needs to be its own database row (Row/subRow 1C). Now, the only way to tell whether or not to combine rootstock trees in the same field planting row when doing this import to the budding table, is by the planting order. If the roostock trees are sequential by planting order AND they are of the same type, they should be combined, but if they are not (as in the example where Lovell, is introduced part way through the row and then goes back to the original rootstock tree type), then they should not be combined. I hope that makes some sense. Thank you for your help thus far.

[June7]

That's way too much for me to assimilate.

It is a balancing act between normalization and ease of data entry/output.

If you understand data normalization then decide how and where to apply it. You can certainly choose to ignore and manage your data as you see fit. Just be aware of the consequences if raw data is not maintained as, well, 'raw'.

What you want is likely not possible with just queries and will require a lot of code.