Use query Result in the next row

Creating tables and queries

Use query Result in the next row

Postby det » Sun Jun 17, 2018 1:11 am

Hi,
I seem to remember a post on the subject, but can't find it.
My Problem: I want to use the result of a calculation in the first row input for the calculation of the second row.
I found "Lag" and "Previous" and others in other Dbase application, but none in HSQLDB.
Can someone recommend a way around it with "Join" or save the result of the first row and the put it int the second row etc..
My dbase is quite small so a complicated way around is acceptable. Thank you Let
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 77
Joined: Fri Mar 31, 2017 8:24 pm

Re: Use query Result in the next row

Postby FJCC » Sun Jun 17, 2018 3:39 am

You might be able to solve your problem with a join if the rows contain some information from which their order can be deduced. It would help to know more about the structure of your table.
I am not sure whether you want the rows of a query to depend on previous rows in the query or the contents of a table to depend on previous rows. If it is a table, this is probably not a good idea. Table rows should contain independent pieces of information.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6581
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Use query Result in the next row

Postby MrProgrammer » Sun Jun 17, 2018 5:04 am

det wrote:I found "Lag" and "Previous" and others in other Dbase application, but none in HSQLDB.
HSQLDB is a relational database where rows are unordered. Read about the term "relation" in secion 3 of the linked article. There is no concept of a "first" row.

dBase, as I understand it, was not a relational database. Instead procedural commands and functions were used to open and traverse records and manipulate field values. Its vendor labelled dBase a "relational database" although it did not meet the criteria defined for the relational model. I believe you'll find that neither OpenOffice Base nor HSQLDB supports those procedural commands from dBase.

det wrote:My Problem: I want to use the result of a calculation in the first row input for the calculation of the second row.
This may be a fundamental problem for you with no good solution other than to redesign your database concept. Perhaps if you describe what you want to accomplish a volunteer will have some ideas for you.
Last edited by MrProgrammer on Sun Jun 17, 2018 4:31 pm, edited 1 time in total.
Mr. Programmer
AOO 4.1.5 Build 9789 on Mac OS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3367
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Use query Result in the next row

Postby det » Sun Jun 17, 2018 7:06 am

1.I could store the Query Result in a Spreadsheet not in DBase Table.
2. I have quite some medical Bills each month. I pay Deductible and Copay up to a limit, the insurer pays the rest. Each time I pay the limits change.

Code: Select all   Expand viewCollapse view
Row   Bill   PrevDedLim  Ded      DedLim   ResBill  PrevCoLim   Co    CoLim
0     11.14  10.63       10.63    0.00      0.51    69.38       0.18  69.20
1     17.93   0.00        0.00    0.00     17.93    69.20       6.24  62.98


PrevDedLim in Row 1 = DedLim in Row 0
PrevCoLim in Row1 = CoLim in Row 0
Last edited by robleyd on Sun Jun 17, 2018 7:50 am, edited 1 time in total.
Reason: Added Code tags
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 77
Joined: Fri Mar 31, 2017 8:24 pm

Re: Use query Result in the next row

Postby UnklDonald418 » Mon Jun 18, 2018 12:27 am

It appears your example is based on a spreadsheet mentality, which you need to leave behind when working with databases.
When using relational database you don't normally store a value that can be calculated in a query.
I pay Deductible and Copay up to a limit, the insurer pays the rest.

That implies that Deductible Limit and Copay Limit are actually constants. However, you also say
Each time I pay the limits change.

Which contradicts your first statement. I suspect what you mean is the difference between the amount paid and the limit changes.
I uploaded a database that demonstrates something similar to what I believe you are wanting.
It has one Table, one Query and 2 Forms.
One form is to enter new bills. The second form displays the results of the query where all the calculations are performed.
In the query I set a Deductible Limit at 200.00 and the Copay Limit at 100.00. The query is a little too complex for the table design GUI, so to view or make any changes Edit in SQL View must to used.
Attachments
Demo25_MedBills.odb
(21.22 KiB) Downloaded 19 times
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 849
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Use query Result in the next row

Postby det » Mon Jun 18, 2018 4:41 am

You are absolutely right, my spreadsheet mentality gets in the way.
And thanks for your excellent example. It directs me towards a solution.
Your Forms require entries for DeductionAmt and CopayAmt.
However, these are exactly what I want to establish by a query.
I'll try to edit your SQL and let you know what happens.
Det
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 77
Joined: Fri Mar 31, 2017 8:24 pm

Re: Use query Result in the next row

Postby det » Thu Jun 28, 2018 4:44 pm

Sorry, I could not get it to work. My solution is to do the calculation in as spreadsheet and add it to the database. The problem I have to do this for every block of new entries, very inefficient!

The better solution, of course, wait for an update of HSQLDB, that contains the "Lag" and "Previous" Functions.
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 77
Joined: Fri Mar 31, 2017 8:24 pm

Re: Use query Result in the next row

Postby UnklDonald418 » Thu Jun 28, 2018 7:19 pm

The better solution, of course, wait for an update of HSQLDB, that contains the "Lag" and "Previous" Functions.

Those are not database functions, they are spreadsheet functions so your wait will likely be very long.
Your Forms require entries for DeductionAmt and CopayAmt.
However, these are exactly what I want to establish by a query.

Perhaps, we can help you if you can tell us how they are calculated.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 849
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Use query Result in the next row

Postby chrisb » Fri Jun 29, 2018 12:40 am

det,
when you have a SQL issue then you must supply the necessary relevant data/information required to enable other forum users to respond with a possible solution.
it is possible to mimic the lag function used by some databases.
1) provide a few rows of TABLE data, field names, table names.
2) describe your primary key. is it an auto increment integer?
3) i assume you have a date field. is it unique?
4) tell us what calculation you wish to perform (fieldname1 operator or expression fieldname2 etc.)
5) using the sample TABLE data show us the expected query result.
open office 4.1.5 & LibreOffice 5.4.3.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 166
Joined: Mon Jun 07, 2010 4:16 pm

Re: Use query Result in the next row

Postby eremmel » Fri Jun 29, 2018 10:03 am

HSQLDB is a relational database where rows are unordered. Read about the term "relation" in secion 3 of the linked article. There is no concept of a "first" row.

You are absolute right with this statement, but fortunate there is a way to query the data such that an ordering can be applied to the tuples / rows.
UnklDonald418 wrote:
The better solution, of course, wait for an update of HSQLDB, that contains the "Lag" and "Previous" Functions.

Those are not database functions, they are spreadsheet functions so your wait will likely be very long.
Your Forms require entries for DeductionAmt and CopayAmt.
However, these are exactly what I want to establish by a query.

Perhaps, we can help you if you can tell us how they are calculated.

I like to make a comment about Lag / Previous type of functions. These kind of functions do exists in modern databases (the commercial ones and ProgreSql) and are part of the Windowing-set of functions. Extreme powerful. All (?) other opensource based databases lack those unfortunate.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 990
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Use query Result in the next row

Postby eremmel » Fri Jun 29, 2018 12:04 pm

I added to the sample database a query that calculates the running totals per month/year.
The query "OverviewPerMonthYear" shows the same columns as you showed in your two line example.
The query MEDBILLS_PART_BY_M_Y is an help query that does the partition and contains the limits of Copay and Deduction, so you might need to change that.
When you need to fill it is not clear to me if there is a fixed ratio between the Bill and Copay and Deduction. If there is we can get it into the query and you are done. When not, you have to fill it in the form, but you like to list in the same form e.g. a subform the bills of that month, those will show your the remaining Copay and Deduction available and you and fill in the part you like to apply to your bill.
Have fun.
Attachments
Demo25_MedBills-v2.odb
Example query with running total partitioned by month/year.
(22.64 KiB) Downloaded 16 times
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 990
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Use query Result in the next row

Postby UnklDonald418 » Fri Jun 29, 2018 5:08 pm

eremmel , thank you for your correction. Those functions didn't appear in my references (apparently outdated) so I wasn't aware of them.
I did encounter odd errors when trying to open Demo25_MedBills-v2.odb
ErrorMsg.png

The first with OpenOffice 4.1.5, and the second with both LibreOffice 5.4.7.2 and 6.0.4.2
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 849
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Use query Result in the next row

Postby eremmel » Fri Jun 29, 2018 5:58 pm

When I try to open my version is gives the same errors. Looks like corrupted... Will have to figure it out again this weekend.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 990
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Use query Result in the next row

Postby Sliderule » Fri Jun 29, 2018 9:35 pm

Link below is a database just like user eremmel posted, BUT, it is an "Embedded HSQL Version 1.8.0.10" database, and, not from HSQL Version 2.4.0.

Demo25_MedBills-v3.odb
(22.23 KiB) Downloaded 14 times

I did create two VIEWS from the Queries eremmel created, so, it could run through the Base Parser.

I will leave it to other Forum Users to continue any explanations / issues / solutions for user det

Sliderule

NOTE: Per the wise comment from user eremmel below, I updated the file type of the above attachment to .odb . Sorry for my mistake. :super:
Last edited by Sliderule on Fri Jun 29, 2018 11:51 pm, edited 1 time in total.
User avatar
Sliderule
Volunteer
 
Posts: 1137
Joined: Thu Nov 29, 2007 9:46 am

Re: Use query Result in the next row

Postby eremmel » Fri Jun 29, 2018 10:11 pm

Thanks, Sliderule!

Just for others, rename the file of Sliderule by replacing '.zip' with '.odb'
I played in the past with split database and had point my class path to hsqldb.jar v2.x. So that was the cause...
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 990
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Use query Result in the next row

Postby det » Thu Jul 05, 2018 7:13 am

Thank you for your efforts. Unfortunately they do no address my original post. Deduction and Copay is exactly what I want to find, but in your answers they are Table Entries.
The limit per months is 19.45 for Deductible (I pay) and 88.83 for CoPay (I Pay). The rest is
paid by the Gov. The Copay is 34.8 % of the Residual Bill.

General Bill Data (such as Date, Medication, Period, Amount/Period etc) are collected in Table “Bills” with a running ID for each Entry. The period is shown as 1802, meaning Feb. 2018.

In order to calculate my Deductible & CoPay, I had to create the Table “CalcTable”.
As requested these tables are attached (“CalcTable” only partially).
After Column “Bills” the Calculations are:
1)PrDedRes = Previous Deductible Residual = DedRes of previous Row or Manual Entry.
2)Ded = Deductible = Bill or PrDedRes, 3)DedRes = Deductible Residual = PrDedRed – Ded, 4)PrCoRes = Previous CoPay Residual = CoRes of previous Row or Manual Entry,
5)BilRes = Bill Residual = Bill – Deductible, 6)CoCalc = CoPay Calculated = BillRes x 0.348,
7)CO = CoPay = lesser of PrCoRes or CoCalc, 8)CoRes = PrCoRes – CO,
9)GS = Private Insurer = Bill – QuePaid, 10)QuePaid = Gov. Paid = Bill – Ded – Co.

Except for Column 1 and 4, Dbase can handle the Calculations. The Calculations may distract from the real problem, namely column 1 and 4. The “CalcTable” was created in Calc, would it be possible to create it in Dbase? I know my calc mentality gets in the way, but is there a better way? Det
Attachments
CalcTableOO.ods
(33.42 KiB) Downloaded 13 times
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 77
Joined: Fri Mar 31, 2017 8:24 pm

Re: Use query Result in the next row

Postby UnklDonald418 » Tue Jul 10, 2018 10:06 pm

As time allowed, I've continued looking at your problem but I'm not sure I totally understand your spreadsheet.
Using 19.45 as the beginning monthly deductible and a Copay rate of 34.8%, let's look at a simple example to confirm how your spreadsheet works.
For a given Month
Bill 1 – Bill Amt = 10.00 Since that is less than 19.45 Deductible Amt = 10.00. Copay = 0.00. Insurance = 0.00
Bill 2 – Bill Amt = 20.00 the remaining Deductible Amt = 9.45, Copay = 3.67 (34.8% of 10.55 ), Insurance = 6.88
Bill 3 – Bill Amt = 30.00 since this was already paid Deductible Amt = 0.00, Copay = 10.44 (34.8% of 30.00), Insurance = 19.56
Is this is correct?
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 849
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Use query Result in the next row

Postby det » Wed Jul 11, 2018 5:28 am

I'm sorry my table is a bit confusing as it starts with the values of the previous year.
The Starting Values for Deductible and Copay for each month are in green. The attached revised Calc Sheet should make things clearer. Thank you. Det
Attachments
CalcTableOO1.ods
(29.59 KiB) Downloaded 8 times
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 77
Joined: Fri Mar 31, 2017 8:24 pm

Re: Use query Result in the next row

Postby eremmel » Wed Jul 11, 2018 10:22 am

In my sample database I made a query with running totals over month/year. I did that on fields Ded and Copay. But you can apply the same query pattern and calculate a running total on calculated Ded and calculated Copay and Bill. Based on those totals you can reconstruct the individual Ded and Copay per Bill (at least it is my intuition).
I had a short look at your explanation of your calculation, but looked to me too complicated because you stayed with to your calc-model.

As fas as I understand what you want to calculate (In my home country we have another system):
Bill : Amount is given.
Ded: The amount of the Bill you have to pay with max amount you have to pay per month applied 100% per bill
Copay: The percentage that is returned per bill for the amount above MaxDed with a limit per month and a max by obamacare(?).
Insurer: The remaining amount (Bill - Ded - Copay)
(Please correct this if this is wrong)

When you succeed in making a Calc sheet with these 4 columns and the running totals of those per month (use running totals up to the Bill you are processing not including) you can change that into a query and let the database to the work.

It is a nice challenge but it take some time (and I need to find time and to understand). The drawback of giving you a solution that you did not 'discovered' yourself is that you stay depended on this forum for each and every change.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 990
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Use query Result in the next row

Postby det » Wed Jul 11, 2018 4:45 pm

Let me clarify: The column Bill should read Price. Maybe that led to the confusion.
My situation: The Statement from the pharmacy gives me the Price of the medication and the amount I have to pay. I want to check the if the amount I have to pay is correct. The rules for my payment have been stated earlier: up to 19.45/Month for Deductible and up to 69.35/Month for Copay.
My purpose for the Data Base is twofold:
1. Record the Purchase of the medication
2. Check the correctness of the amount I have to pay
Hopefully things are a lot clearer now. Det
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 77
Joined: Fri Mar 31, 2017 8:24 pm

Re: Use query Result in the next row

Postby eremmel » Thu Jul 12, 2018 3:11 pm

You have the tab 'Bills' in your sheet CalcTableOO1.ods. Does this sheet map one to one on your database data model?
I observe the following:
- A medication can have up to two follow up subscriptions in different periods. Does all need to be considerd in the calculations?
- Some periods have value 0 what does that mean? I guess no refund allowed?

The attached sheet CalcTableOO2.ods contains the calculation based on running totals. I think is possible with SQL based on running totals per Prd (up to, not including the current Bill line). This are the steps taken:
- added to your sheet an new sheet in which I added all the bills in a normalized way (moved follow up subscriptions to the first Prd and Bill column.
- sorted the list based on Prd, ID
- Added running total on Bill (Sum Bill) based on Bill
- Added running total on Ded (Sum Ded) based on Bill, Sum Bill and Max Ded
- Added running total on Copay (normalized at 100% iso 34.8%) based on Sum Bill, Sum Ded, Max Copay.
- With the above three running totals and Bill we can calculate Ded, Copay and Insurence.

Please give feedback on your database table structure and confirm that the calculation is right.

I might then write the query that will give the same results as in the Calc sheet. ( if someone else want to give it a try, that is fine to me as well. It is a nice exercise for a SQL course.
Attachments
CalcTableOO2.ods
(35.08 KiB) Downloaded 6 times
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 990
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Use query Result in the next row

Postby UnklDonald418 » Fri Jul 13, 2018 6:26 am

I uploaded
Demo25_MedBills-v4.odb
(23.53 KiB) Downloaded 11 times

see if that is on the right track.

It has one table, MedBills2 with data copied from your spreadsheet as well as some other test data.
There are a series of queries, Query1_A through Query1_F that make use of the ability of one query to use another query as its data source. I tried to keep each query as simple as possible so that hopefully you will be able to understand all the steps needed to generate the data you need. Query1_FinalDet pulls it all together to display values for Bill Amt, Deductible, CoPay, and Insurance Share.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 849
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Use query Result in the next row

Postby eremmel » Fri Jul 13, 2018 6:13 pm

Dear Unkl,

Nice and clear solution. One remark though. In your SKEY calculation is I think that you should move one ')':
( YEAR( "Date" ) * 100 ) + MONTH( "Date" ) ) * 10000 + ID
It does not harm because the period is anyway YYYYMM, but when you order on SKEY result can look weird.

In fact you do not need SKEY and could have used ID due to the period is kept equal.

An other tip: Add to Query1_A the constants:
..., 19.45 AS "MAXDED", 69.38 AS "MAXCOPAY", 0.348 AS "PCTCOPAY"
So there is a central place for maintaining those constants and easy to change when the values change.

Even better is to populate e.g. a table with PERIOD, MAXDED, MAXCOPAY, PCTCOPAY so the calculation stays historical correct when new rates are issued.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 990
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Use query Result in the next row

Postby UnklDonald418 » Sat Jul 14, 2018 12:44 am

eremmel thanks for the advice.
Originally, I used ID in the Running Totals (Differences?) but when I added 3 test records with dates from 2017 they appeared at the bottom of the list not the top. It also occurred to me that if you are entering new rows in the table, say for the month of July, and there in your stack of invoices is one you missed from perhaps 2/28/2018, without SKEY that entry also won't appear in the correct place when the Query1_FinalDet is run. Of course even with SKEY it won't appear in the correct place without an ORDER BY "SKEY" clause. Somehow that failed to make it into the upload.
I found another problem so I incorporated the recommendation to define the constants in Query1_A and fixed the known issues.
Demo25_MedBills-v5.odb
(23.87 KiB) Downloaded 7 times

Hopefully, no more surprises.
Just for fun I included Query2_FinalDet to see what it might look like with everything rolled up in a single query. That would be a challenge to decipher!
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 849
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Use query Result in the next row

Postby chrisb » Sat Jul 14, 2018 1:55 am

det,
i've uploaded an attachment with 2 queries.
q1.Setup: selects bill data & creates 2 running sums. created a view from this query which is the data source for the second query.
q2.Output: calculates & displays the field values in the image of your spread sheet.
i have built this database to reflect your spreadsheet.
the original IDs & periods are used. this restricts development but allows you to verify the output.
obviously in its current form input is difficult because the IDs are both manually input & filtered by Period.
if you confirm that it meets your needs then i may find the time to enhance its usability.
it also has the ability to use variable values for "DedLimit" & "CopayLimit".
a database is definitely the correct tool for this project. a spreadsheet will be corrupted sooner or later no matter how carefull the user.
i would also like to say that i found this project very interesting/unusual.

i did download the attachment uploaded by UnklDonald418.
the output is as it should be but because it does not contain the field "Period" it's impossible for me to easily verify the accuracy.
you would need to confirm the accuracy before it could be used with confidence.
Attachments
det_Uploadl1.odb
(16.58 KiB) Downloaded 11 times
open office 4.1.5 & LibreOffice 5.4.3.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 166
Joined: Mon Jun 07, 2010 4:16 pm

Re: Use query Result in the next row

Postby det » Sat Jul 14, 2018 4:08 am

To keep things simple and focused I left out some details. I have shown the Pharmacy Statement in
the attached CalcTableOO3 in the Normalized Sheet. If I buy more than the Doctor prescribed Pills, I can still have the excess pills covered by the next period. That is why there are 2 or sometimes 3 Bills on one Statement. All the answers are in this Statement. My purpose is to verify what I have to pay.
The Bill Table (DB) and the Calc Table are not linked or compatible, because I re-arranged the the Bill Table by Period requiring more rows.
The Normalized Sheet still uses results of the previous row, how can this be handled by a query?
I added two columns to help to get the right answer. I am still trying to find out if or how previous row results are used in Demo25_MedBills-v5.odb. The results do not match the official Values on the Statement of the Pharmacy. What is a running Total? Just the Sum in a query?
Hopefully misconceptions are removed. Det
Attachments
CalcTableOO3.ods
(47.96 KiB) Downloaded 8 times
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 77
Joined: Fri Mar 31, 2017 8:24 pm

Re: Use query Result in the next row

Postby det » Sat Jul 14, 2018 5:35 am

Hello Uncl
I'm impressed! I started on Base about 2 months and didn't get much beyond simple SQL.
Thanks for taking the time do represent my calc table in a query. So there must be a way to store the result of a calculation and use it in new calculations of other queries. It will take me some time to understand the SQL and why not all results agree with the Pharmacy results listed in the Normalizing sheet of CalcTableOO3. Thanks again Det
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 77
Joined: Fri Mar 31, 2017 8:24 pm

Re: Use query Result in the next row

Postby chrisb » Sat Jul 14, 2018 11:56 pm

det,
i'm sorry that my effort does not match your expectations.
in my first post i did ask you to show us what you have along with your expected results.
you uploaded a spreadsheet without explanatory notes which failed to shed a coherent insight into your exact issue/objective.
a second spreadsheet was then uploaded which revealed the basic mechanics behind the project to which i responded with my uploaded attachment.
it would save everyones time & effort if you could answer any questions asked by those of us who are trying to help after all it's you who would be the beneficiary.
open office 4.1.5 & LibreOffice 5.4.3.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 166
Joined: Mon Jun 07, 2010 4:16 pm

Re: Use query Result in the next row

Postby UnklDonald418 » Sun Jul 15, 2018 1:15 am

In one of those middle of the night AHA! moments, it finally occurred to me how to get the output sorted into the proper order without the SKEY field. Sometimes it's hard to see the forest with all those trees blocking the view.
I made a couple of other tweaks that will hopefully help you get closer to your goal.
Demo25_MedBills-v6.odb
(23.33 KiB) Downloaded 7 times

Perhaps a little overview will also help you understand how this works.
Query1_A defines preliminary values that will be needed later on. If something changes like "MAXDED" or "PCTCOPAY", you only need to make one change here.
Query1_B is where a Running Total is calculated, which is the sum of the current Bill amount plus all previous Bill amounts. That result is subtracted from "MAXDED" (19.45) and assigned the name "RemDed".
Query1_C uses "RemDed" in a CASE WHEN statement to determine the actual deductible amount "DedAmt".
Query1_D calculates a preliminary CoPay amount "CCP"
Query1_E calculates "RTCP" which is a running total of the "CCP" field. In previous versions I included the value for the current record in that total, but in this version it only sums the values for the previous rows. I think that will resolve one issue.
Query1_F uses a CASE WHEN statement to determine the actual "CoPay" amount.
Query1_FinalDet does the final calculations for the values to be displayed on the form Display_FinalDet.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.5 & LibreOffice 6.0.4.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 849
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Use query Result in the next row

Postby det » Sun Jul 15, 2018 2:35 am

Dear chrisb,
I'm sorry you felt I did not value your contribution. I immediately sent tables, calculations for each
column and 3 versions of a spreadsheet with all explanations. Please let me know what you are missing and I gladly supply. Det
Right now I will study Demo25_MedBills-v6.odb to understand what running Totals are.
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 77
Joined: Fri Mar 31, 2017 8:24 pm

Next

Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests