create a chart on a form

[bean_2k1]

i have about 1500 records in my database and i want to display a bar chart for each record, i can tryed creating one but when i try adding the months it says i only can add 6 fields but i want to include each month not just 6 months in the chart, this is an on going database so in the future there will be more months added. here is the picture of the form i want to be able to show 2005 and 2006 figues in the same chart

 

[Jonathan Kok]

You'd need to change the way your database is laid out to do it. Basically, you'd need two tables--an "Agents" table and a "Monthly Amounts" table. Tie the two tables together via an ID number, then build your form based on that. From there, it's quite easy. The graph doesn't update automatically, but with a little VBA coding you could make it do so after a new record is added.

I've attached a simple database that should help explain things.

HTH,
Jonathan

EDIT: Hang on...just realized you'll need a pivotchart to compare year-to-year. Gimme a minute to try to work it out...

EDIT2: Ok, fixed...but I gotta tell ya, PivotCharts in Access are a PITA compared to Excel. Surprising, considering they're using the same components (or should be).
I ended up creating a query to base the pivotchart on, adding Year and Month fields based on the date. Access PivotCharts won't let you split up grouping in the same way as Excel, so I needed multiple instances of the same field. Fun, fun!

 

[Rich]

If you have each month as a separate field in your table then you have a design issue

 

[bean_2k1]

the only reason i have the months in different fields is because when i export the data from our system it is put in to a excel file which then i just import in to this database, there are like 35000 records in the excel file

 

[Rich]

You're re-creating a spreadsheet in Access, it won't be long before you come up against the 255 field limit. The number of records isn't a problem in itself, the number of fields is.

 

[Jonathan Kok]

Is this going to be an ongoing function where you import the records into the database from an Excel spreadsheet, or is it a one shot thing, after which you'll be typing the values in yourself?

Where is the Excel Spreadsheet getting its data from? Is there any way to create the spreadsheet in a different format?

 

[bean_2k1]

Na its an on going job the spreadsheet is taken from our customer database program called chorus, the database will be updated after each month

 

[Jonathan Kok]

All I can suggest is that you have the spreadsheet filter by month so you end up with agent, month, and amount, and that's it (with the month in the recognized 'date' format). If you do the starting ground work, adding the following months won't be as difficult. Assuming you don't change historical data, of course.

 

[Jonathan Kok]

As an alternative, you could use a proper Reporting tool with crosstab functionality (such as Crystal Reports) to directly access the backend of the Chorus database and create the graphs. Depending on what Chorus uses as a backend, of course.