Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ASP to calculate sum of records

    Hi guys,
    How does one go about this?. I have an access db called
    pmldb.mdb
    There are four fields I'm concerned about,
    parts, weight, material grade and size.

    I need an asp script to calculate the sum of the 'weight' for each part.
    Thus I need the total weight for each of the part called Pipes, consumables, valves and instruments.
    and also i need the option where i can group it by either material grade, size or part.
    An example, if there are 130 records; 70 of them are pipes, 30 are consumables, 20 are valves and 10 are instruments. I would need the total sum of the weight
    of all 70 pipes. The same would apply to consumables, valves and instruments. And have them them all displayed with the total for each four.
    Thankyou in advance...
    ps. I've had a go at it below. Please, if you've got a better idea, then it would be appreciated if you can show me, the below one is incomplete (but would work). My asp is a bit rusty, just started learning it.

    <%
    'Connect to database

    'Query database
    SQL = "SELECT * FROM Table WHERE Part = 'Pipes'"
    Set DB.Execute(SQL)

    intQuant = 0

    Do Until RS.EOF
    intQuant = intQuant + RS("Weight")
    RS.MoveNext
    Loop
    %>

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP to calculate sum of records

    What I've been doing is a three-step process:
    1. Use a Make Table Query to create a table with the appropriate fields
    2. Query the table using the SQL aggregate functions (Sum, Avg, etc)
    3. After all the data are presented, I run a Drop Table query.

  3. #3
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP to calculate sum of records

    Hi Mark (Nice name [img]/forums/images/smilies/smile.gif[/img]
    I see what your saying. Ok, what do you think of this one that I did below?.
    Also, Is there a way I can display the results of the records for pipes for example?. So the 20 records that were involved for the calculation of the weight of the pipes will be displayed.? Thanks in advance

    <%
    'Connect to database
    Set db = Server.CreateObject("ADODB.Connection")
    db.Open "DSN=ImportExcel"
    'Query database
    set RS = db.execute("SELECT parts,sum(weight) as SWEIGHT FROM tblSheets GROUP BY Parts")
    %>
    <table>
    <%
    Do Until RS.EOF
    %>
    <tr>
    <td>
    <%=RS("Parts")%>
    </td>
    <td>
    <%=RS("SWeight")%>
    </td>
    </tr>
    <%
    RS.MoveNext
    Loop
    %>
    </table>

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP to calculate sum of records

    Hi Mark,

    Your code looks great! That should do the trick...

    As for showing each individual record for Pipes, you'll need to use a different Select statement for this. If you need to see each individual record for all Parts, your original method might be best. The Group By is only appropriate if you only want to see a summary.

    Hope this helps!

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP to calculate sum of records

    Hey MarkJ ~ I tried the "more direct" method, using ID, Year, and Month as Select fields (because there are criteria on them), with SumOF the other fields, and i always wind up with distinct records for each year and month. I'll go back and look at my SQL again, perhaps I can simplify what I'm doing...

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP to calculate sum of records

    <P ID="edit" class=small>(Edited by MarkJ on 21-May-03 21:30. Corrected Group By clause (removed Sum(Weight) - not needed in Group By if it's an Aggregate function))</P>Hi Mark,

    My suggestion is along the same lines as Peter's, except it may be a little more direct.

    You can use a SQL statement to do all of this work for you. You can use the GroupBy clause of SQL kinda like this:
    (assumes that your field names are Part and Weight)

    SELECT Part, SUM(Weight) AS SumOfWeight FROM Table GROUP BY Weight

    This should give you a distinct group for each part along with the sum of the weights for each part. This should save having to loop through a recordset (which will also work, but this way should be quicker). If you need more fields, be sure to add them to the SELECT clause as well as the Group By clause.

    Hope this helps!

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP to calculate sum of records

    Hi Peter,

    That's the nature of the Group By clause - the output will return a distinct record made up of each field in the clause. It makes sense that if you want to see, for instance, sums for certain fields along with their ID, month, and year that you'll see distinct records with all of the specified fields. Now, if you want to see sums for each ID, leave off month and year to group by only ID.

    I hope this makes sense. My understanding of SQL is decent, but I haven't gotten to the point of being able to adequately explain it to others yet...

    For more information you can check one of my favorite SQL sites: SQL Team

  8. #8
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASP to calculate sum of records

    AHA! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Changing the "Group By" to "Expression" in my In Access "query builder" saves 2 steps in my 3-step process!

    Thanks Marks - you both taught me something! I can throw even MORE code away <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •