Comparing total years records

# Thread: Comparing total years records

1. Senior Member
Join Date
Dec 1969
Posts
1,030

## 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. Senior Member
Join Date
Dec 1969
Posts
96,118

## 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
•