[Solved] Use query Result in the next row

Creating tables and queries

Re: Use query Result in the next row

Postby UnklDonald418 » Mon Jul 16, 2018 12:21 am

In case you haven't already found it, I noticed a problem with Query1_E caused by the change I made between Demo25_MedBills-v5.odb and Demo25_MedBills-v6.odb. It causes “RTCP” to have a Null value for the first record of each PERIOD
To resolve that issue, Query1_E should be.
Code: Select all   Expand viewCollapse view
SELECT "ID", "Medication", "Date", "TotQty", "Bill", "DedAmt", "CCP", "PERIOD",
    ( SELECT SUM( "CCP" )
        FROM "Query1_D" AS "C" WHERE "C"."PERIOD" = "Query1_D"."PERIOD" AND "C"."ID" <= "Query1_D"."ID" ) AS "RTCP", "MAXCOPAY"
FROM "Query1_D"

I apparently have issues with my version control because Query1_F that is in Demo25_MedBills-v6.odb was an old version. I hope this will work better.
Code: Select all   Expand viewCollapse view
SELECT "ID", "Medication", "Date", "TotQty", "Bill", "DedAmt", "CCP", "PERIOD", "RTCP",
  CASE WHEN "MAXCOPAY" >= "RTCP"  THEN "CCP"
  WHEN "CCP" > "MAXCOPAY"   THEN "MAXCOPAY" - ("RTCP" - "CCP") 
  ELSE 0.00   END
AS "CoPay"
FROM "Query1_E"
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1179
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Use query Result in the next row

Postby chrisb » Mon Jul 16, 2018 1:44 am

det, checkout the attachment, let me know what you think.

1). the table "BillData" contains all rows from you Calc sheet 'NormalBillsOverview' with "Prd">=1802 except row 40 which is faulty.
2). at this time 16 July 2017 when you execute the query 'q1.Output' any bills with a period greater than '1807' will not be shown.
why? because i have legislated for multiple 'DedLimit', 'CopayLimit'. if either of these values change in the future the database will handle it.
this should not prove to be an issue because you cannot have a bill for august 2018 before august 2018.
if they exist as they do then you can view them via the form.
3). using the aforementioned data there are 2 discrepancies displayed between the spreadsheet & query both within the field "Copay".
"BillID"=6 "Period"=1803: spread=0.00 db=4.41. spread shows incorrect value db shows correct value.
"BillID"=43 "Period"=1807: spread=65.61 db=65.62. spread shows correct value db shows incorrect value. this a rounding error.
4). i have not encompassed "Periods" with a value of '0000' nor insurance.
the purpose of this forum is to help/guide/advise users with base related issues without actually doing their work for them.
5). all of your requirements were in fact included in my previous upload but you lack the knowledge/ability to recognise it.
this attachment is in fact a simplified version of my original uploaded attachment with minor alterations.
 Edit: 16-July-2018 04:20 sorry uploaded wrong attachment now sorted 
Attachments
Det_Upload3.odb
(20.56 KiB) Downloaded 55 times
open office 4.1.6 & LibreOffice 6.1.5.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 183
Joined: Mon Jun 07, 2010 4:16 pm

Re: Use query Result in the next row

Postby det » Mon Jul 16, 2018 9:55 pm

Dear chrisby,
I owe you an apology. Only now I realize how much effort you put into writing the SQL and I thank you
a lot for it.
As mentioned earlier I am a beginner and wrestle to understand the SQL.
The Demo25_MedBills-v6.odb looked simpler to me and I started there, where I still struggle to understand whether "SELECT SUM("Bill") FROM....WHERE...." is the key to access the results of
of the previous row.
The full SLQ is:
SELECT "ID", "Medication", "MAXDED" - ( SELECT SUM( "Bill" ) FROM "Query1_A" AS "V" WHERE "V"."SKEY" <= "Query1_A"."SKEY" AND "V"."PERIOD" = "Query1_A"."PERIOD" ) "RemDed", "MAXDED", "MAXCOPAY", "PCTCOPAY" FROM "Query1_A"

Now I realize that it only handles Period 1 of a bill but not period 2 or 3. My situation is explained in CalcTableOO3.

At first look at det_Uploadl1.odb q1Setup is total Chinese to me.
Would you be kind enough to explain it in more detail or where I can find the syntax?
Where are b,r,d,c, b1,b2,r1,r2,current_date defined? I guess "on" is part of "join".
Even if the SQL work, I cannot use it unless I understand it. How can I make changes or fixes?
I would be very grateful for you help. Det
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Use query Result in the next row

Postby det » Mon Jul 16, 2018 10:01 pm

Thanks chrisb, I'll have a look. Yes there were some mistakes, hopefully I found them all. Det
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Use query Result in the next row

Postby eremmel » Mon Jul 16, 2018 10:17 pm

The following query normalizes your "Bills" data. Is does so by 'merging' the result of three queries together.
Code: Select all   Expand viewCollapse view
-- To go from your Bills table to a normalized table you have to
-- take the following query and make it a view in your database.
-- Note that any solution that is based on Date will not work it
-- has to take Prd as partition.
SELECT "ID","MEDICATION", "Data", "TotQty", "Prd1" as "Prd", "Bill1" as "Bill"
FROM  "Bills"
WHERE "Prd1" > 0
UNION ALL
SELECT "ID","MEDICATION", "Data", "TotQty", "Prd2", "Bill2""
FROM "Prd1"
WHERE "Prd2" IS NOT NULL
UNION ALL
SELECT "ID","MEDICATION", "Data", "TotQty", "Prd3", "Bill3"
FROM "Prd1"
WHERE "Prd3" IS NOT NULL
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Use query Result in the next row

Postby eremmel » Mon Jul 16, 2018 10:25 pm

det wrote:where I still struggle to understand whether "SELECT SUM("Bill") FROM....WHERE...." is the key to access the results of
of the previous row.
The full SQL is:
SELECT "ID", "Medication", "MAXDED" - ( SELECT SUM( "Bill" ) FROM "Query1_A" AS "V" WHERE "V"."SKEY" <= "Query1_A"."SKEY" AND "V"."PERIOD" = "Query1_A"."PERIOD" ) "RemDed", "MAXDED", "MAXCOPAY", "PCTCOPAY" FROM "Query1_A"

You really have to run each query order by ID and look to the differences. There are some 'smart' tricks played in the calculations. Just reset your mind in respect to this calculation and forget about your calc approach at all your "Calc Table".
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Use query Result in the next row

Postby det » Tue Jul 17, 2018 12:02 am

Yes, but I don't understand the smart tricks, would you be able to explain? I'm just a novice. It seems there are some tricks with views? or tables? I don't find any literature about. Det
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Use query Result in the next row

Postby UnklDonald418 » Tue Jul 17, 2018 12:22 am

where I still struggle to understand whether "SELECT SUM("Bill") FROM....WHERE...." is the key to access the results of
of the previous row.

Since SUM includes the Bill amount for the current row plus all the previous rows, simply subtract the current Bill from the SUM to get the SUM from the previous row.

Since Bill 2 and Bill 3 appear in a different PERIOD the easy solution would be just to Post them with a different Date. But that would result in losing their connection to Bill1.
Another approach would be to add a second date field, something like ApplyToDate, to MedBills2 and use that to generate PERIOD. Then when needed, the rows could still be related by the original Date,
When you post Bill1 you would enter the same date for both Date and ApplyToDate.
When you post Bill2 and beyond, Date would be the same as Bill1 but ApplyToDate would have a different date. Similar to what you are doing in Prd2 and Prd3 on your spreadsheet.
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1179
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Use query Result in the next row

Postby chrisb » Tue Jul 17, 2018 3:27 am

det,
because this forum is Base related i cannot respond to general questions about SQL.
there are some excellent online SQL tutorials/forums. just google 'sql running sum' & your prayers will be answered.

by asking the question about running sums you caused me to look at my code & lo & behold i have failed to update my running sum sub-query as the table "tBillDate" has evolved.
below is the updated code:
Code: Select all   Expand viewCollapse view
select b1.ID, sum(b2."Cost") "RunCost"
from "tBillData" b1
join "tBillData" b2 on b2."Period"=b1."Period" and b2.ID<=b1.ID
group by b1.ID
if you copy & paste this code here>>>'Queries>Create Query in SQL View' then hit 'F5'(execute) you will see the data it generates. don't confuse "ID" with "BillID".
if you run the query "q1.Setup" we show the field "RunCost" ("ID" is hidden as it has no visual value).
now on each row deduct the value of "Cost" from "RunCost" & you have the value of "RunCost" as shown in the previous row. just as UnklDonald418 has already said.

b1 & b2 are both aliases for the table "tBillData" this simplifies input & aids readability.
in the case of the above code because we are referencing a single table it's essential to assign an alias to at least one table.
it could be coded like this:
Code: Select all   Expand viewCollapse view
select "tBillData"."ID", sum(b."Cost") AS "RunCost"
from "tBillData" AS "tBillData"
join "tBillData" AS b on b."Period"="tBillData"."Period" and b."ID"<="tBillData"."ID"
group by "tBillData"."ID"
P.S. when user eremmel has something to say it's always wise to take note.
open office 4.1.6 & LibreOffice 6.1.5.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 183
Joined: Mon Jun 07, 2010 4:16 pm

Re: Use query Result in the next row

Postby det » Wed Jul 18, 2018 7:23 am

chrisb,
I have made the corrections to the normalized table. See sheet "Bible" in attached CalcTableOO4.
The MaxDed and MaxCoPay can change for each period. For 1802 the MaxDed is 10.63. That is why the first 1802 rows are wrong.
New limits will be published for 1807 and they will change again for 1907.
Thanks for the explanations. Now I can start to understand the use of short aliases in a query.
My understanding of the whole Process:
1.Enter data from the pharmacy statement with running ID
by a Form for Med, Date, up to 3 Costs and Periods (I prefer blocks over table)
2.Normalize the data by period and add another ID1 (as per emmerel)
3.Make Tables relating MaxDed and MaxCoPay for each Period.
4.Make the calculations for each period using ID1
5.The calculations are made either in separate Queries or bundled into one Query
5.The calculations are mostly based on Running Sums and joining tables.
Could you please confirm, if yes I'll be on my way to finish.
And by the way the access to the previous row is by the running sum as stated earlier:
Since SUM includes the Bill amount for the current row plus all the previous rows, simply subtract the current Bill from the SUM to get the SUM from the previous row.
In a generalized form (I'm working on it) it could qualify as a response/solution to my original Post. Det
Attachments
CalcTableOO4.ods
(55.18 KiB) Downloaded 56 times
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: Use query Result in the next row

Postby chrisb » Thu Jul 19, 2018 3:26 am

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. 
Attachments
Det_Upload4.odb
(20.51 KiB) Downloaded 59 times
open office 4.1.6 & LibreOffice 6.1.5.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 183
Joined: Mon Jun 07, 2010 4:16 pm

Re: Use query Result in the next row

Postby det » Thu Jul 19, 2018 7:41 pm

Hello chrisb, eremmel and UnclDonald,
The answer to my original Post could have simple been: “Use (Sum – Result)” and I would have struggled on.
Instead I got a fully coded functional Data Base written by professionals tailored to my very situation! Wow! And even get Maintenance. In recognition for this work I would like to make a donation. Does anybody know how? I searched “Donation”, but got nowhere.
All that is left for me to do is to understand the code and iron out my mistakes (reload the data with proper ID, add some Tables etc.)
However, as chrisb quoted it’s a long & difficult journey to learn SQL, which I am prepared to take. How can I learn without going through the frustrations of finding that typo or a plan ending in a dead end? That is why I start, with all the knowledge gained from you people, to play around with my own Database.
To answer chris’s question to item 1. When you create a form you have 4 options: Columnar Labels Left, Columnar Labels Top, Data Sheet, Blocks Labels Above. Thank’s for the latest upload Det_Upload4. It still does not reflect the changes of the “Bible”. Maybe it is the wrong upload. I can make those changes. No problem, I have to learn!
I thank all contributors for their time and amazing effort!
I think it’s appropriate to mark this Post as solved, everybody agrees?.
Det
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: [Solved] Use query Result in the next row

Postby eremmel » Thu Jul 19, 2018 11:10 pm

Hi Det,

It was a nice effort to help you out, it made me think about how to map 'SQL Windowing function' to SQL like HSQLDB supports it. The underling problem was interesting enough to trigger three people to help you out in detail. The most valuable thing for me is that someone is put on the track to learn SQL and Base and willing to put the effort into this to stand on his own feet. You have seen different approaches to get to the same result most of that is more or less a tast. There is more to say about query performance, but that depends on the amount of data your gonna collect (I guess the less the better in this case). When that will be an issue come back.

Note the difference in SQL between UnklDonald418 and chrib. Unkl used 'simple' SQL statements that will stay inside Base (Base does the substitution of each reference: FROM query-name), but the query format is very bad (one line). Chrisb used the 'direct SQL' mode (Base sends its data directly to the database), but the format of SQL is maintained (inclusive comments), that makes it easy to write readable complex SQL. In the latter case you can only use views in the database to refer to sub results (so no queries in Base).

Have fun!
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: [Solved] Use query Result in the next row

Postby det » Thu Jul 19, 2018 11:56 pm

eremmel,
Thanks for the comments. I have a few questions:
What does “how to map 'SQL Windowing function' to SQL like HSQLDB supports it.” mean?
Do you mean “fast” or “test” for “tast”?
What is the difference between a View and a Query?
Do you mean that views are handled faster than Queries and Queries should be avoided?
Are aliases only used in views?
Det
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: [Solved] Use query Result in the next row

Postby chrisb » Fri Jul 20, 2018 3:36 am

det,
now i understand what you meant by blocks.
i don't use any of the wizards in Base. the form wizard is only suitable when your requirements are very basic.
when i need a form i hit 'Create Form in Design View' & soldier on from there.
in form design there is a toolbar icon called 'More Controls' which when hit opens a pop up menu giving access to more controls including the table grid.
in regard of your project you need table grids.
when using the form 'fBills' if you hit a Period then all the bill records for that period are instantly available in order of choice for view/edit/deletion.
if you don't use a table grid then only the current record will be available at any given time.

you are correct when you say "It still does not reflect the changes of the “Bible”". those changes are visual (the value of the field "Date" & the deletion of period '1808'. i am only concerned with the mechanics & accuracy of calculations.

anyway i have now deleted all data from the table "tBills" & replaced it with the data from "Bible" (86 rows). i did not insert row 25 as "BillID" is missing.
i've added a simple report using the report builder extension (this is the best way to view/examine your data).
i created a view "v1.Output" from the query "q1.Output". this view is the data source for the report.
Built In Functions>> https://wiki.openoffice.org/wiki/Built- ... Procedures
HSQL Syntax>> http://www.hsqldb.org/doc/1.8/guide/gui ... ax-chapter
Report Builder>> https://extensions.openoffice.org/en/pr ... rt-builder
i've uploaded 'Det_Upload5.odb'
Attachments
Det_Upload5.odb
(69.82 KiB) Downloaded 58 times
open office 4.1.6 & LibreOffice 6.1.5.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 183
Joined: Mon Jun 07, 2010 4:16 pm

Re: [Solved] Use query Result in the next row

Postby eremmel » Fri Jul 20, 2018 11:02 am

det wrote:eremmel,
Thanks for the comments. I have a few questions:
What does “how to map 'SQL Windowing function' to SQL like HSQLDB supports it.” mean?
I mean with map work-around. E.g when you have a system that lacks multiply like 'N x 3', but has addition, you can map that to 'N + N + N'.
Do you mean “fast” or “test” for “tast”?

I ment tast as personal preference.
What is the difference between a View and a Query?
Do you mean that views are handled faster than Queries and Queries should be avoided?

A view is part of the database definition and a query exists outside the database in an application like Base. In the world of IT the best answer to questions that compare things is: 'It depends', also in this case. But I do not think that Queries should be avoided. Within Base queries can act like client-side views, but the drawback it the missing preservation of layout; an important thing for humans (I never use the query design (GUI) tool, too clumsy for me).
Are aliases only used in views?

An 'alias' in SQL is just a name that you give to an field, expression, table or derived table so you can uniquely identify the object (often used as a short cut).



Note that Base is a front-end, but for prototyping it supports an 'embedded' HSQLDB v1.8 database. This is to some extend an instabile combination and you should make a backup first before you open a Base document and start working on it, esp. when it contains real data. (The statement is maybe too bold, but be careful!)
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: [Solved] Use query Result in the next row

Postby det » Fri Jul 20, 2018 5:28 pm

chrisb,
Thanks for the update. I googled the SELF JOIN and understand now where the a,b,c,r are coming from (although there is no JOIN in the SQL). Your references are also helpful. Thank you.
I have already noted the highly convenient method of Data Entry in fBills and agree with you that my idea was bad.
I’m shifting the goal post again, forgive me. I will do the coding once I understand your code to gain experience and return the mods. I quickly tried to add another Query, but get a long error message, which I don’t understand. I’ll keep trying.

1. The Pharmacy Statement may have up to 3 Period Costs per Medication.
2. They need to be added up to get the Total Cost and the Total Insurer Portion.
3. The difference is then split between a Private Insurer (“PrIns”) and myself.
4. The fBills needs another Entry for “Ipaid” right next to “Qty”
5. “Ipaid” and “Qty” apply to the Total Cost, not the individual Cost.

Mathematically: Private Ins. = Total Cost – Total Insurer – Ipaid

eremmel,
Thanks for the clarifications, haven't yet understood all of them. What does tast mean?
Det
OpenOffice 4.1.5 on Mac Sierra 10.13.1
det
 
Posts: 86
Joined: Fri Mar 31, 2017 8:24 pm

Re: [Solved] Use query Result in the next row

Postby UnklDonald418 » Fri Jul 20, 2018 6:13 pm

Demo25_MedBillsvX was meant to be a learning tool, attempting to bridge the gap between spreadsheet and database by building the needed data set step by step and demonstrating how a running total could be used in place of a previous row function.
The version by chrisb is definitely more efficient and the better solution.
I avoided the use of cryptic aliases because while they make perfect sense to the original writer and are easier to type, as you noted they can be confusing to someone else reading the code.
As eremmel noted when using an Embedded database you need to make frequent backups. Base is notorious for a "data loss problem", where seemingly without warning, all the tables disappear from the database and often cannot be recovered. In addition to regular backups, there is a way to minimize the problem by moving to a Split database. In that format, table data is stored in uncompressed files outside the .obd file. As a bonus, you also gain the ability to upgrade to a more recent version of HSQLDB with more functions available. The main disadvantage is the loss of the ability to edit existing tables using the table design GUI. Not a huge problem, but you do need to learn to edit tables with SQL.
There are a couple of detailed discussions on the topic in the Base Tutorials forum, including
[Wizard] Create a new 'split' HSQL 2.x database

I do have one question for chrisb. When I open your queries in the SQL view they are nicely formatted with separate lines, indenting and white space around logical groups. When I save a query with similar formatting the next time I open it the formatting is gone. I'm curious how you are able to save your queries without losing the formatting?
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1179
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] Use query Result in the next row

Postby chrisb » Fri Jul 20, 2018 8:40 pm

@UnklDonald418 just hit the 'Run SQL command directly' (direct mode) icon before saving your query. this disables the base parser.
a query with the parser disabled can not access the data of any other query but can access the data of tables & views.
a query with the parser enabled can access the data of any other query, table or view.

det, we've hit the buffers.
i've been working on the basis of one "Period" per bill.
in a nutshell you need to start more or less from scratch.
i think it's best to leave the current db as it is & build a new one.
the only good news is that the existing calculations & sub-queries could be copied & pasted with minimum pain.
assuming that the data contained in the table "tBillData" is relevant then you could simply drag & drop it into the new db.
success or failure is dependent on the sound foundations of thorough prior planning.

it's easy enough to have multiple periods per bill which entails:-
1). add the table "tBills" with two fields "ID" integer auto increment primary key & a descriptor of some kind which enables visual identification.
2). add additional required fields to the table "tBillData".
NOTE: every record in the table "tBillData" needs to contain a foreign key which references "tBills.ID" (the value which identifies the bill to which it pertains).
3). redesign the form 'fBills'.
4). rewrite the queries.
open office 4.1.6 & LibreOffice 6.1.5.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 183
Joined: Mon Jun 07, 2010 4:16 pm

Re: [Solved] Use query Result in the next row

Postby eremmel » Fri Jul 20, 2018 9:23 pm

Ded, I'm not a native speaker and sometimes it results in misunderstandings (most of time my language checker saves me, but not this time): tast -> taste :oops:

Note that the data model of chrisb is nicely at a workable 'normal form', where your initial table (with three periods) was not (search google for "Database normalization wiki"), Database design and application building has a steep learning curve (but remember in this area you can earn a salary to maintain your hole family).

I think that a separate parameter table for Max-Ded, Max-Copay and Pct-Copay per period might be a little overkill. One parameter table with all three fields might be sufficient, but changing one parameter result in duplication of the other two for the next period. A trade off between normalization and maintaining multiple tables and forms.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Previous

Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest