Joe Miller
|
| Posted: 04/20/2004, 9:08 AM |
|
I have finally figured out how to get grid totals, but it only show the totals for the page not for the complete search, if the results are spread out over 2-3-4-5 pages it will only shows page totals not Search results totals, the formula I am using is:
Function Webpros_BeforeShowRow() 'Webpros_BeforeShowRow @3-553AD1C3
'Custom Code @16-73254650
' -------------------------
Webpros.Summ.Value = Webpros.Summ.Value + Webpros.Pro_Amount.Value
' -------------------------
'End Custom Code
I am using MS Access 2000
With CodeCharge Studio Version 2.2
With ASP
Webpros = Table Name
Pro_Amount = Field I want to total
Summ = Label I want to display the total in
Connection Name = Connection1
Search Form Name = WebprosSearch
Any help will be great,
Thank You
|
|
|
 |
DonB
|
| Posted: 04/20/2004, 2:44 PM |
|
Data is only fetched one page at a time. Thus, the other records are not
included in the recordset and not included in the total you calculate from
the recordset. To do that you will either need to:
not page the grid (no navigator or set the navigator to display one huge
page)
or
you will need to query for "SUM(columnX) for each "column X" in your data (a
second database query that returns one row of just the totals) then attach
that to the grid as a new row of data (like you've done with the totals you
currently have created).
--
DonB
http://www.gotodon.com/ccbth
|
|
|
|
Pinochet
Posts: 13
|
| Posted: 04/20/2004, 4:23 PM |
|
Hi Joe,
One way to get a total count for a grid is to use the 'Grid Builder' to build your grid. You want to insure you check the check box 'Display total number of records' on Step 4 of 5.
This automatically builds your grid and places a 'Total Records Count' label on the grid
Another possiblity, if you already have your grid all set up and working, is to use the SQL statement SELECT COUNT(FIELDNAME) FROM WEBPROS; to return the count of rows in your table. You could place this SQL in an event like beforeShow. I've down the following and received the correct results:
In a labels before show event
label1.VALUE = CCDLookUp("count column_name)", "table_name", empty, DBconnectionName)
the above function CCDLookUp is explained in the CCS2.2 documentation under Component References>ASP>Functions. And in this case is just mirroring the SQL SELECT COUNT column_name FROM TABLE Table_Name (You can also add where clause here to limit you selection)
Hope this helps
_________________
don't worry it doesn't mean anything anyway |
 |
|
peterr
Posts: 5971
|
| Posted: 04/20/2004, 4:42 PM |
|
I think that the CCDLookup method is also what we use in our Store example. You can see that our Store displays "18 products found" at http://examples.codecharge.com/Store/Products.php?s_keyword=a , even though the results are spread over multiple pages.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Joe
|
| Posted: 04/22/2004, 7:11 AM |
|
|
|
|
|
Joe
|
| Posted: 04/22/2004, 7:14 AM |
|
Source: CCDLookUp function
Command Text: SELECT SUM(Pro_Amount) FROM WebprosQuery
Error description: No value given for one or more required parameters. (Microsoft JET Database Engine)<br>
-------------------------------------------------------
This is what my code looks like
WebprosQuery.label1.VALUE = CCdlookUp("SUM(Pro_Amount)", "WebprosQuery", empty, DBConnection1)
what am i doing wrong??????????
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/22/2004, 10:55 AM |
|
Please try:
WebprosQuery.label1.VALUE = CCdlookUp("SUM(Pro_Amount)", "WebprosQuery", "", DBConnection1)
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Joe Miller
|
| Posted: 04/22/2004, 11:11 AM |
|
No it still does not, work, I still get the same error as before, Anything i am doing wrong????? i really appreciate all your help,
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/22/2004, 11:18 AM |
|
Usually this error means that the field or table doesn't exist in the database. Since "WebprosQuery" looks like a query, then possibly the "Pro_Amount" field is not correctly defined in the query - the database cannot recognize it.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Joe Miller
|
| Posted: 04/22/2004, 11:26 AM |
|
LABEL PROPERTIES
Control Source: Database Column
Label: Label1
**************************************
GRID PROPERTIES
Data Source Type: Table
Connection: Connection1
If you need anything else,
Joe
|
|
|
|
Joe
|
| Posted: 04/22/2004, 11:30 AM |
|
peterr, Thank You So Much,
i just got it, your Customer Service skills are Great and the response time was even better, One more question, can i add MIN/MAX/ect... instead of SUM
Thanks again
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/22/2004, 11:35 AM |
|
Thanks 
I think that MS Access supports Min/Max as the database function, so it should work. However I'm not sure because some functions in MS Access are executed as program-type functions, not database. Therefore some SQL statements may work directly in MS Access if using some VB Functions or even custom-defined functions (in MS Access modules), but they may not work from outside of MS Access (via ODBC or JET driver). It's best to try and find out.
Min/Max looks standard though, so I would think that it should work.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Joe Miller
|
| Posted: 04/22/2004, 11:38 AM |
|
Peterr,
Thanks for everything, But now I have one more problem, that code brings back the total of the whole database, I have a search form before the Grid results, That will search by customer account name; Account products; Shipping terms, ect, So I need that to bring up just the total for the search.
Example: Customer ABC wants to search everything he has ordered, that went Ups Ground, Only,
Thank You
Joe Miller
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/22/2004, 11:47 AM |
|
Please try:
WebprosQuery.label1.Value = CCdlookUp("SUM(Pro_Amount)", "WebprosQuery", WebprosQuery.DataSource.Where, DBConnection1)
(in the Before Show event)
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Joe Miller
|
| Posted: 04/22/2004, 11:51 AM |
|
PERFECT !!!!!!!!!!!!!!!
Thanks so much, Have a great rest of the day,
Joe
|
|
|
|
Joe Miller
|
| Posted: 04/26/2004, 9:33 AM |
|
peterr
You gave the following code to get the sum of a field, and it worked perfectly, I just need a little more help, what if i wanted to +/-/*/divide the totals by another sum value,
WebprosQuery.label1.Value = CCdlookUp("SUM(Pro_Amount)", "WebprosQuery", WebprosQuery.DataSource.Where, DBConnection1)
I want to divide the total Pro_Amount by total Pcs
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/26/2004, 9:13 PM |
|
I think that you can use all these operators directly in the SQL (just like SUM in your case), although this may depend on the database. Since these are SQL functions I recommend referring to SQL docs, for example:
http://www.google.com/search?q=sql+mathematical+operators
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
|
