by Rick and Barbara Osgoo » Mon, 15 Sep 1997 04:00:00
I am writing an application using VB5 and Access 97. I have a dbListbox
that presents a list of 'Sections' to a user. In the same table as the
section, is a field that tracks the number of hours spent on that
'Section' (WorkTime). Its type is Number. I am now trying to create a
query that will sum the total hours spent on the selected section. I
have tried many combinations but my brain is now fried.
Any help would be appreciated
Rick Osgood
by Jim Fergus » Mon, 15 Sep 1997 04:00:00
Rick,
dim db as databaseQuote:>I am now trying to create a
>query that will sum the total hours spent on the selected section.
set db = 'some db variable
lngSection = dbcombo 'or wherever
strSQL = "select sum([hours]) as SumOfHours from " & _
"yourtable where [Section] = " & lngSection
set rst = db.openrecordset(strSQL)
txtTotalHours = rst!SumOfHours
You could also do this with a parameter query instead of building up
the entire SQL statement on the fly at run-time.
-- Jim Ferguson, FMS
http://www.fmsinc.com
Greetings,
I'm trying to construct a view wich contains two columns based on
different sum functions of the same base table. In the following SQL
script view sum2v produces the required results, but I have a feeling
I'm doing things the hard way.
create table sum2 (
kode number,
jaar number,
bedrag number
);
insert into sum2 values(1, 96, 1);
insert into sum2 values(1, 96, 2);
insert into sum2 values(1, 96, 3);
insert into sum2 values(2, 96, 4);
insert into sum2 values(2, 96, 5);
insert into sum2 values(2, 96, 6);
insert into sum2 values(1, 97, 7);
insert into sum2 values(1, 97, 8);
insert into sum2 values(1, 97, 9);
insert into sum2 values(2, 97, 10);
insert into sum2 values(2, 97, 11);
insert into sum2 values(2, 97, 12);
commit;
select * from sum2;
create view sum2v96 as
select kode, jaar, sum(bedrag) sumbedrag
from sum2
where jaar = 96
group by kode, jaar;
select * from sum2v96;
create view sum2v97 as
select kode, jaar, sum(bedrag) sumbedrag
from sum2
where jaar = 97
group by kode, jaar;
select * from sum2v97;
create view sum2v as
select a.kode, a.sumbedrag sumbedrag96, b.sumbedrag sumbedrag97
from sum2v96 a, sum2v97 b
where a.kode = b.kode;
select * from sum2v;
Please share your valuable SQL knowledge with me (and all other
newsgroup readers) and come up with a more efficient/ellegant
solution.
Thanks in advance,
Rob Mocking
2. Data Shaping: Anybody using it successfully....
4. SQL and RPC
5. Which is faster Sum(fld1) + Sum(fld2) +...Sum(fld50) -- OR this
6. Sql Query
7. SQL Plus -- need detail lines and sum of Amount, if the sum is not 0
8. LONG truncation problem (v7.3.3)
9. SQL Question - Summing and Grouping
10. Q: sql question(sum across column w/o group by)
11. SQL question: ordering based on SUM/AVG/COUNT
12. Using DISTINCT AND SUM together SQL question
13. SQL - Sum and Group by Question