Comparing total years records

Results 1 to 2 of 2

Thread: Comparing total years records

  1. #1
    Join Date
    Dec 1969

    Default Comparing total years records

    I have to write a query that pulls all the records from an Access database, total them by year and show the percentage of change for one year to the next.<BR><BR>The result would look like:<BR><BR>1999 $35,000<BR>2000 $40,000 % of change from prev year: 14.2%<BR>2001 $45,000 % of chaneg from prev year:<BR><BR>and so on.<BR><BR>The problem I have is that I have no clue how many years are in the database. I would know how to do it if I did know but what do you do if you don&#039;t??<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default Easier to do this via VBScript

    &#060;%<BR>SQL = "SELECT Sum(amount) AS yrTotal, Year(someDate) AS yr " _<BR> & " FROM table GROUP BY Year(someDate) ORDER BY Year(someDate)"<BR><BR>Set RS = conn.Execute(SQL)<BR><BR>priorTotal = -1<BR><BR>Do Until RS.EOF<BR> yr = RS("yr")<BR> total = RS("yrTotal")<BR> Response.Write yr & ": " & FormatCurrency(total) <BR> If priorTotal &#062; 0 Then<BR> Response.Write " (" & FormatPercent( (total-priorTotal)/priorTotal) & ")"<BR> End If<BR> Response.Write "&#060;BR/&#062;" & vbNewLine<BR> priorTotal = total<BR> RS.MoveNext<BR>Loop<BR>%&#062;<BR><BR><BR>Quite frankly, I don&#039;t understand at all how knowing how many years are in the DB affects the answer to this. You have truly mystified me.<BR><BR>

Posting Permissions

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