Chain Calculation in Queries

Creating tables and queries

Chain Calculation in Queries

Postby ijulie » Sun Jan 13, 2008 7:28 pm

Hi Newbie here,
Somehow when I used access I could subtract my meter readings to get total usage then take that total and multiply it by the rate field and take that total and multiply it times the tax. In my table I had fields for the 2 meter readings and the rate.
How did I get the tax total without using a table field just the query result.

I guess I'm just getting old ;) Thanks
ijulie
 
Posts: 7
Joined: Wed Jan 09, 2008 4:30 pm

Re: Chain Calculation in Queries

Postby DrewJensen » Sun Jan 13, 2008 7:50 pm

Well with the query engine in Base at the moment that is a bit more of a PITA then it should be:

It would be nice if we could do something like:

SELECT usage, rate, tax, usage * rate AS charge, charge * txRate AS totalbill FROM table

But you can not, because you can not use a column alias ( usage * rate AS charge - the alias is charge ) within another section of the select clause.

However you can use the fact that Base supports Query-in-Query. In other words we need 2 queries where one uses the other.

Create query one
SELECT usage, rate, tax, usage * rate AS charge FROM table

Save it as query1

Create query two

SELECT *, charge * txRate AS totalbill FROM query1

OK - those queries aren't quite Base legal, no quoting of column names - but I assume you would do this in the query GUI designer of course.

HTH

Drew
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Chain Calculation in Queries

Postby ijulie » Sun Jan 13, 2008 9:10 pm

Thanks Drew I'll try that if you know I have space numbers as my key field do I have to put in the same rates and taxes in all my spaces to get them all? or can I link one charge to all the spaces? I only have 200 so it wouldn't be hard to do it.

Thanks again,Julie
ijulie
 
Posts: 7
Joined: Wed Jan 09, 2008 4:30 pm

Re: Chain Calculation in Queries (SOLVED)

Postby ijulie » Sun Jan 13, 2008 11:19 pm

Thanks
ijulie
 
Posts: 7
Joined: Wed Jan 09, 2008 4:30 pm

Re: Chain Calculation in Queries

Postby DrewJensen » Mon Jan 14, 2008 6:57 pm

Hi, did you answer second question for yourself then?
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest