Creating Query For Each Year to be used in Report chart

[Mordacia]

I have an accounts payable database with fields for "Date Paid" and "Amount Paid." I would like to create a query from this database that will create a new calculated field called "Year Paid" that will separate the totals by year paid and will sum the totals into one record for each year. The reason I need the years to be records and not separate fields is that I want to create a chart in a report displaying the yearly totals, and Access limits you to 6 fields in a chart. I currently have 7 years of totals and that number will continue to grow. Any tips would be greatly appreciated!

 

[JerryDennison]

You do not need to have seperate fields to create seperate columns for your chart. Use a totals query with the following:

Year: Year([DateFieldName])
set this field's total property to Group By

FieldNameToTotal
set this field's total property to Sum

Now, base you chart on this query and it will give you an unlimited number of columns (1 for each year) with their respective total. "Advice is a dangerous gift, even from the wise to the wise, for all course may run ill." J.R.R. Tolkien