i did download 2 attachments in relation to this topic 'Demo25_MedBills.odb' & 'Demo25_MedBills-v4.odb' both uploaded by user UnklDonald418.
to avoid confusion any/all comments within this post are made strictly with reference to my own uploaded attachment 'Det_Upload3.odb'.
OK lets go through your list.
1). nothing needs to change here. simply enter bill data as per form 'fBills'. i don't understand blocks over table.
2). nothing needs to change here. data is normalised by "Period" & the table 'tBillData' has the field "ID" which is an auto incrementing integer primary key.
3). these tables already exist 'tCopayLimit' & 'tDedLimit' & are available for edit via the form 'fBills'.
4). nothing needs to change here.
5a). the coding, number of queries are at the discretion of the developer.
i guess that provided the results are accurate it makes little difference but it's much easier to follow the pattern with a smaller number of queries.
5b). i thought that we were finished but you are constantly updating your requirements.
i noticed that the values in the field "Date" in your latest spreadsheet have been updated. fortunately my db does not use these values but a change of this kind could be catastrophic.
the success of a database is determined in the design/planning stage before a single keystroke is made.
when you keep moving the goalposts it results in errors (redundant/invalid code) & sometimes we even have to start again from scratch.
the title of the topic is 'Use query Result in the next row' & i think that we have demonstrated one method that is relative & satisfies that criteria namely(running sum-cost).
it has to be said that you came to the forum with nothing but should now have a fairly clear path to follow thanks to the efforts of all those who contributed.
it has been said many times but getting to grips with Base/SQL is a long & difficult journey which many begin but few succeed in completing.
----------------------------------------------------
i have downloaded the attachment 'CalcTable004.ods'
using the form 'fBills' i deleted the 2 rows you marked 'remove' & corrected 1 row marked 'wrong'.
i did not add the row marked 'Added' because the 'BillID' is missing. NOTE: 'BillID' must be unique per period. if you want to insert it where "Period"=1803 & "BillID"=1 then you will have to renumber all "BillID"'s for that period.
now your telling us that "CopayLimit" & "DedLimit" will change & that "DedLimit"= 10.63 when "Period"= 1802. your spreadsheet does not reflect this.
i did forsee this & as previously stated have allowed for it.
again using the form 'fBills' i have changed "tDedLimit.Amount" for "Period"= 1801 (10.63) and added an entry for "Period"= 1807 (19.45). you can view the values of "DedLimit", "CopayLimit" by running the query "q1.Setup".
to test against your spreadsheet you can easily reset the "DedLimit.Amount" for "Period"= 1801 back to 19.45.
you should as user eremmel has said create a table to allow for changes in Copay rate which at present = 0.348.
i purposely did not include this but have demonstrated the method required with both "DedLimit" & "CopayLimit". you will not learn anything if somebody else does it for you.
i have cleaned up the code & corrected a fault with the form 'fBills'("BillID" was wrongly linked to "ID") that's what can happen when we keep chopping & changing.
also corrected a fault in the way "tDedLimit" & "tCopayLimit" are joined to "tBillData".
download the attachment 'Det_Upload4.odb'
remember as i am not using this database i will not be aware of errors/faults unless alerted.
Edit: 19 July 2018 14:07. i don't think that the attachment 'Det_Upload4.odb' contains the updates to the table 'tBillData' as set out in the spreadsheet 'CalcTable004.ods'.
use the form 'fBills' to update as necessary. sorry for the confusion i work with a split db & forgot to carry over the updates. |
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10