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.

'## 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.

Thanks for that. it's pretty close to what i had worked out just couldn't get it to work.