Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Jan 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    grouping db records and totals

    I'm collecting turnovers for various companies from a db and each of the companies has a county code. e.g. Yorkshire is defined as number 2.

    I need to group the companies together by county and need to display a totals line for those members only and then on the following lines I want the next county and its companies and so on.

    what i need is the totals line like this:

    Acct Name Jan Feb Mar Reps-Code
    p12d Private £1000 £2510 £5203 2
    r72t Retail £5965 £7512 £1316 2
    TOTALS £6965 £10012 £6519
    s87e Albion £2395 £1000 £500 19
    ayt4 Range £950 £7512 £1316 19
    TOTALS £3345 £8512 £1816

    So basically i need it after all companies grouped by the Reps-code. So all companies with reps-code 2 have their own totals, then the ones with the code 19 and so on.


    However i cant get it to display like that and i get the following instead:

    Acct Name Jan Feb Mar Reps-Code
    p12d Private £1000 £2510 £5203 2
    TOTALS £1000 £2510 £5203
    r72t Retail £5965 £7512 £1316 2
    TOTALS £5965 £7512 £1316

    I am grouping the companies by the Reps-code and that works as the companies are grouped but the problem is with the totals line.

    I'm using a do while loop to check that the records are present in the db and the totals line is inside that loop. I tried putting the totals after the loop but that only displays 1 Totals line for ALL companies.

    Im sure its pretty simple to do but i cant seem to get around what im doing wrong.

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Code:
    '## query database:
    
    Set Recordset = DatabaseConnection.Execute("SELECT [Acct], [Name], [Jan], [Feb], [Mar], [ Code ] FROM [Companies] ORDER BY [ Code ]")
    
    dim currentCode : currentCode = 0
    dim currentJanTotal : currentJanTotal = 0
    dim currentFebTotal : currentFebTotal = 0
    dim currentMarTotal : currentMarTotal = 0
    
    do while not recordset.EOF
    
    thisRecord_Code = cInt(recordset.Fields("Code"))
    thisRecord_Jan = cDbl(recordset.Fields("Jan"))
    thisRecord_Feb = cDbl(recordset.Fields("Feb"))
    thisRecord_Mar = cDbl(recordset.Fields("Mar"))
    
    if thisRecord_Code <> currentCode then
    
    '## you are on the first record of a new bunch of county codes
    '## write the totals row
    '## then write the row as normal
    '## then reset your totals and code
    
    currentCode = thisRecord_Code
    currentJanTotal = thisRecord_Jan
    currentFebTotal = thisRecord_Feb
    currentMarTotal = thisRecord_Mar
    
    else
    
    '## you are on a record with the same county code as the one preceding
    '## write out a row for it
    '## then add the figures to your running totals
    
    currentJanTotal = currentJanTotal + thisRecord_Jan
    currentFebTotal = currentFebTotal + thisRecord_Feb
    currentMarTotal = currentMarTotal + thisRecord_Mar
    
    end if
    
    recordset.movenext
    
    loop
    Does that help at all? Briefly: if you make sure that your records are ordered by the field you want to *group* them by, then you just use that as a flag: each time you come to a record with a new county code, you set that as the "current" code and reset your totals. If the record that you come to has the same code as the current code, then you just need to write out a row for it and add its figures to your totals.

  • #3
    New to the CF scene
    Join Date
    Jan 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for that. it's pretty close to what i had worked out just couldn't get it to work.


  •  

    Posting Permissions

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