 |
|
| Computers Forum Index » Computer - Databases - Paradox » QBE question - try again... |
|
Page 1 of 1 |
|
| Author |
Message |
| RickM... |
 Posted: Fri Mar 06, 2009 11:36 pm |
|
|
|
Guest
|
Geez this thing LOL! posted before I was ready.
Hope some knowledgeable soul can tell me how to do this. I have a table
with 3 cols: an ID number and 2 quantities. I can sum up each of the quantities
easily by checking the ID column and putting "calc sum" in the other 2 cols.
But I also need to add a 3rd column which is the sum total of the other
2 calc sum columns.
In SQL it looks something like this:
select ID_num, sum(q1), sum(q2), (sum(q1) + sum(q2)) as total_sum
from table1
group by ID_num
but how do I do this with a QBE? It's imperative that i do this with QBE
because this will be contained in a script with a whole bunch of other QBEs
and they use tilde variables to work together systematically, and I haven't
had any luck using tilde variables in in SQL queries in .ssl scripts.
I tried this - it provided an answer but it was the wrong one.
ID_num | q1 | q2 |
check | _1, calc sum | _2, calc sum, calc _1 + _2 as total_sum |
Any other suggestions? Thanks!
-Rick M. |
|
|
| Back to top |
|
|
|
| Liz McGuire... |
| Posted: Sat Mar 07, 2009 12:38 am |
|
|
|
Guest
|
select ID_num, sum(q1), sum(q2), (sum(q1) + sum(q2)) as total_sum
from table1
where ID_num = ~sID
or:
where ID_num = '~stID'
....depending on whether it's a string or not and whether your variable
includes the single quotes or not.
As for the QBE, I'm fairly sure you'll need two of them - since they're
in a script, that shouldn't be such a big deal.
Liz
RickM wrote:
Quote: Geez this thing LOL! posted before I was ready.
Hope some knowledgeable soul can tell me how to do this. I have a table
with 3 cols: an ID number and 2 quantities. I can sum up each of the quantities
easily by checking the ID column and putting "calc sum" in the other 2 cols.
But I also need to add a 3rd column which is the sum total of the other
2 calc sum columns.
In SQL it looks something like this:
select ID_num, sum(q1), sum(q2), (sum(q1) + sum(q2)) as total_sum
from table1
group by ID_num
but how do I do this with a QBE? It's imperative that i do this with QBE
because this will be contained in a script with a whole bunch of other QBEs
and they use tilde variables to work together systematically, and I haven't
had any luck using tilde variables in in SQL queries in .ssl scripts.
I tried this - it provided an answer but it was the wrong one.
ID_num | q1 | q2 |
check | _1, calc sum | _2, calc sum, calc _1 + _2 as total_sum |
Any other suggestions? Thanks!
-Rick M. |
|
|
| Back to top |
|
|
|
| RickM... |
| Posted: Sat Mar 07, 2009 4:39 am |
|
|
|
Guest
|
Hello Liz, thanks so much for the help! I didn't think about doing it as
2 separate queries - that's a good idea, I'll try that next - thanks!
The tilde vars - yes I know that's what I was doing but I never could get
it to work! But I just went back and played with it some more and now it's
working. I think there was something about how I was opening the database
variable for it - I was doing it wrong.
-Rick
Liz McGuire <liz at (no spam) paradoxcommunity.com> wrote:
Quote: select ID_num, sum(q1), sum(q2), (sum(q1) + sum(q2)) as total_sum
from table1
where ID_num = ~sID
or:
where ID_num = '~stID'
...depending on whether it's a string or not and whether your variable
includes the single quotes or not.
As for the QBE, I'm fairly sure you'll need two of them - since they're
in a script, that shouldn't be such a big deal.
Liz
RickM wrote:
Geez this thing LOL! posted before I was ready.
Hope some knowledgeable soul can tell me how to do this. I have a table
with 3 cols: an ID number and 2 quantities. I can sum up each of the
quantities
easily by checking the ID column and putting "calc sum" in the other 2
cols.
But I also need to add a 3rd column which is the sum total of the other
2 calc sum columns.
In SQL it looks something like this:
select ID_num, sum(q1), sum(q2), (sum(q1) + sum(q2)) as total_sum
from table1
group by ID_num
but how do I do this with a QBE? It's imperative that i do this with
QBE
because this will be contained in a script with a whole bunch of other
QBEs
and they use tilde variables to work together systematically, and I haven't
had any luck using tilde variables in in SQL queries in .ssl scripts.
I tried this - it provided an answer but it was the wrong one.
ID_num | q1 | q2 |
check | _1, calc sum | _2, calc sum, calc _1 + _2 as total_sum |
Any other suggestions? Thanks!
-Rick M. |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Sat Dec 05, 2009 12:31 pm
|
|