Randy Lane
|
| Posted: 03/12/2002, 3:59 PM |
|
How do I total (sum) a column of numbers at the bottom of a grid?
I have a page that pulls data by search criteria like member, location,
hours spent, date range and cost.
then I want to show a total at the bottom of the page. I already created an
additional "Total" form, and I understand that some SQL goes into the
"Event - Open" Tab of the form properties, I just don't know exactly what.
Randy
|
|
|
 |
Glenn Holden
|
| Posted: 03/12/2002, 4:46 PM |
|
You can use the SQL tab on the form. Check the Use SQL String box and enter
something like this below. The exact syntax depends on your db. I usually
test the syntax outside of CodeCharge with Access or MySQL admin (it depends
on your database). Here's some SQL to start you off.
select sum(cost) from table_name where <search criteria>
Also this is more complicated but useful:
http://www.gotocode.com/art.asp?art_id=103&
"Randy Lane" <randy@crystalimagesoftware.com> wrote in message
news:a6m4pv$bac$1@news.codecharge.com...
> How do I total (sum) a column of numbers at the bottom of a grid?
> I have a page that pulls data by search criteria like member, location,
> hours spent, date range and cost.
> then I want to show a total at the bottom of the page. I already created
an
> additional "Total" form, and I understand that some SQL goes into the
> "Event - Open" Tab of the form properties, I just don't know exactly what.
>
> Randy
>
>
>
>
|
|
|
|
Randy Lane
|
| Posted: 03/12/2002, 7:34 PM |
|
Thank you Glenn,
I tried something similar to this using the example in the Book Store.
But that just told me the total for that field in the whole table. I want
to total the hours spent or the dollars charged according to the search
criteria set up by the user to populate this grid.
I am using MS SQL Server for the database, page output is .asp
Randy Lane
"Glenn Holden" <glenn@nilenet.com> wrote in message
news:a6m7gg$ggo$1@news.codecharge.com...
> You can use the SQL tab on the form. Check the Use SQL String box and
enter
> something like this below. The exact syntax depends on your db. I
usually
> test the syntax outside of CodeCharge with Access or MySQL admin (it
depends
> on your database). Here's some SQL to start you off.
>
> select sum(cost) from table_name where <search criteria>
>
>
> Also this is more complicated but useful:
> http://www.gotocode.com/art.asp?art_id=103&
>
>
>
>
>
> "Randy Lane" <randy@crystalimagesoftware.com> wrote in message
>news:a6m4pv$bac$1@news.codecharge.com...
> > How do I total (sum) a column of numbers at the bottom of a grid?
> > I have a page that pulls data by search criteria like member, location,
> > hours spent, date range and cost.
> > then I want to show a total at the bottom of the page. I already created
> an
> > additional "Total" form, and I understand that some SQL goes into the
> > "Event - Open" Tab of the form properties, I just don't know exactly
what.
> >
> > Randy
> >
> >
> >
> >
>
>
|
|
|
|
Glenn Holden
|
| Posted: 03/12/2002, 8:48 PM |
|
This gets a bit more complex. You would need to place this in an event (or
two) so you can run some asp code. I don't have a way to test asp so I
can't really provide tested code. Maybe someone else can elaborate or knows
a better way all together.
I can help with some ideas, but first, would it work to have more than one
summary total, regardless of the search criteria? Ie: have both, the total
hours spent AND the total dollars charged. It's much easier.
Otherwise, the only way I can think of is (in short) to
1. Declare a global variable (sTot) in the page open event.
2. In the Grid's open event, examine the sSQL variable (used to create the
search result grid) and use it to set the sTot var to a SQL string that will
derive the total you desire.
3. In the Total's open event, assign the sTot string to sSQL. That will
return the value you want.
"Randy Lane" <randy@crystalimagesoftware.com> wrote in message
news:a6mhbe$238$1@news.codecharge.com...
> Thank you Glenn,
> I tried something similar to this using the example in the Book Store.
> But that just told me the total for that field in the whole table. I want
> to total the hours spent or the dollars charged according to the search
> criteria set up by the user to populate this grid.
> I am using MS SQL Server for the database, page output is .asp
> Randy Lane
>
>
> "Glenn Holden" <glenn@nilenet.com> wrote in message
>news:a6m7gg$ggo$1@news.codecharge.com...
> > You can use the SQL tab on the form. Check the Use SQL String box and
> enter
> > something like this below. The exact syntax depends on your db. I
> usually
> > test the syntax outside of CodeCharge with Access or MySQL admin (it
> depends
> > on your database). Here's some SQL to start you off.
> >
> > select sum(cost) from table_name where <search criteria>
> >
> >
> > Also this is more complicated but useful:
> > http://www.gotocode.com/art.asp?art_id=103&
> >
> >
> >
> >
> >
> > "Randy Lane" <randy@crystalimagesoftware.com> wrote in message
> >news:a6m4pv$bac$1@news.codecharge.com...
> > > How do I total (sum) a column of numbers at the bottom of a grid?
> > > I have a page that pulls data by search criteria like member,
location,
> > > hours spent, date range and cost.
> > > then I want to show a total at the bottom of the page. I already
created
> > an
> > > additional "Total" form, and I understand that some SQL goes into the
> > > "Event - Open" Tab of the form properties, I just don't know exactly
> what.
> > >
> > > Randy
> > >
> > >
> > >
> > >
> >
> >
>
>
|
|
|
|
Roy van Manen - Skyliner Software
|
| Posted: 03/06/2003, 12:13 PM |
|
I've solved this by using two grids on the same page with the same "where"
clause in the sql.
In the top grid I show something like select field1, field2, field3 from
tbltest
In the bottom grid I've put the select sum(field2) etc in sql.
Roy van Manen.
"Glenn Holden" <glenn@nilenet.com> schreef in bericht
news:a6mln4$a67$1@news.codecharge.com...
> This gets a bit more complex. You would need to place this in an event
(or
> two) so you can run some asp code. I don't have a way to test asp so I
> can't really provide tested code. Maybe someone else can elaborate or
knows
> a better way all together.
>
> I can help with some ideas, but first, would it work to have more than one
> summary total, regardless of the search criteria? Ie: have both, the
total
> hours spent AND the total dollars charged. It's much easier.
>
> Otherwise, the only way I can think of is (in short) to
>
> 1. Declare a global variable (sTot) in the page open event.
> 2. In the Grid's open event, examine the sSQL variable (used to create
the
> search result grid) and use it to set the sTot var to a SQL string that
will
> derive the total you desire.
> 3. In the Total's open event, assign the sTot string to sSQL. That will
> return the value you want.
>
>
>
>
>
> "Randy Lane" <randy@crystalimagesoftware.com> wrote in message
>news:a6mhbe$238$1@news.codecharge.com...
> > Thank you Glenn,
> > I tried something similar to this using the example in the Book Store.
> > But that just told me the total for that field in the whole table. I
want
> > to total the hours spent or the dollars charged according to the search
> > criteria set up by the user to populate this grid.
> > I am using MS SQL Server for the database, page output is .asp
> > Randy Lane
> >
> >
> > "Glenn Holden" <glenn@nilenet.com> wrote in message
> >news:a6m7gg$ggo$1@news.codecharge.com...
> > > You can use the SQL tab on the form. Check the Use SQL String box and
> > enter
> > > something like this below. The exact syntax depends on your db. I
> > usually
> > > test the syntax outside of CodeCharge with Access or MySQL admin (it
> > depends
> > > on your database). Here's some SQL to start you off.
> > >
> > > select sum(cost) from table_name where <search criteria>
> > >
> > >
> > > Also this is more complicated but useful:
> > > http://www.gotocode.com/art.asp?art_id=103&
> > >
> > >
> > >
> > >
> > >
> > > "Randy Lane" <randy@crystalimagesoftware.com> wrote in message
> > >news:a6m4pv$bac$1@news.codecharge.com...
> > > > How do I total (sum) a column of numbers at the bottom of a grid?
> > > > I have a page that pulls data by search criteria like member,
> location,
> > > > hours spent, date range and cost.
> > > > then I want to show a total at the bottom of the page. I already
> created
> > > an
> > > > additional "Total" form, and I understand that some SQL goes into
the
> > > > "Event - Open" Tab of the form properties, I just don't know exactly
> > what.
> > > >
> > > > Randy
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
|
|
|
|
|
