Inserting Graph into Report, Data sorted by month

[yotvb531]

I have a financial database with individual transactions, each logged with date and account number. I have a report that generates sums of each account by month. I want to add a monthly pie chart in the report that shows how much each account made up the month's transactions--pretty straight forward, I should think. I've tried this every possible way I can think of, but always get blank pie charts. Then I tried making a query to get the information for me, but when the query groups it by month, it turns it into a text field and I can't link that to my report date. When I try to use the various functions to convert the text date to a date date (FormatDateTime...), I get the error about mismatch data type. If I make a new query that simply converts that date into a "date" (it actually lets me do this), I have no way to link its results back to the other query.

Any ideas on how to make it work?

Thanks!

[June7]

Is this a multi-year dataset? Don't forget to include year in grouping/filtering/linking.

Calculate month/year fields in report RecordSource and graph RecordSource.
Format([date field],"mmyyyy")
Use this calculated value for grouping and linking.

I think any format function results in a string value, hence the mismatch error. CDate function converts a value back to date entity.

[yotvb531]

Yes, it is a multi-year dataset. The report itself has everything filtered by month, so I'm not sure how I would filter it also by year.

The report has headers/footers in this order: Report, Page, Date, Code, Detail, (then obviously the reverse). I try adding the pie chart in the date header or footer, since the date section is grouped by month. I just get blank pie charts--just the title, no circle or anything. I have tried selecting either my custom query or simply using the master table of transactions (having it sort itself by month in the wizard). When I click preview in the wizard, it looks correct (obviously for whatever sampling that it finds). I tell it to link date to date and code to code. Then I end up with the blank chart.

Sorry, I didn't follow your suggestion for calculating fields in report RecordSource and then graphing it. Most of what I do in Access is clicking here and there and just figuring it out. I've googled it pretty extensively and everyone makes it seems pretty straight forward to add a chart, but for whatever reason, it just isn't working for me (probably because my database is so different). This really has me stumped...which is why I've reached out here. Running Access 2010 on Windows 8, though I get the same behavior in Access 2010 in Windows 7.

[June7]

The graph object in Access is not very intuitive. I analyse graph issues best when I can work with the db. If you want to provide, upload to a fileshare site such as Box.com and post link to the file. Make copy, remove confidential data, run Compact & Repair, zip if large.

[yotvb531]

I made a new db. I added some fictitious transactions and made the table columns mostly identical to mine, so that when I imported the queries and report, they would work properly. You can find it at:

https://www.sugarsync.com/pf/D7159739_60498245_752694

I added four pie charts for each month, though I only need one. I just added it four different ways, each one not working.

Thanks in advance!!

[June7]

Does this get you want you want:

Report RecordSource:
SELECT [Date], Withdrawal, Deposit, Code, Description, Budget, [Text Budget], Month([Date]) & Year([Date]) As MonYear FROM [Category List] INNER JOIN [Account History] ON [Category List].ID = [Account History].Code;

Account History for Graphs query:
SELECT Sum(Withdrawal) As SumOfWithdrawal, Description, Month([Date]) & Year([Date]) As MonYear FROM [Category List] INNER JOIN [Account History] ON [Category List].ID = [Account History].Code GROUP BY Description, Month([Date]) & Year([Date]);

Graph RowSource:
SELECT Description, SumOfWithdrawal FROM [Account History for Graphs] WHERE MonYear=[Reports]![Monthly Spending Graph Distribution]![MonYear];

Change the Date section to group on MonYear. Put a textbox in the MonYear footer bound to MonYear. Delete the Master/Child links properties of the graph control.

Open report in Layout view, save report, close. Open in Design view and the graph should show your info.

[yotvb531]

I still get blank pie charts. Check the link above for an updated version, with your suggestions applied. With the db above, do you get real pie charts? I.e. is it maybe my configuration or something?

[June7]

Yes, charts show data. Don't know what is wrong with the later version of db. I thought report might be corrupted so tried to build new one by copy/paste controls (except the graph) into new report. Then built graph. Still didn't work. I could copy/paste the entire working report from first db to the later db and report worked. Very bizarre as I built working report from your first file. I give up figuring out why later db not working. Am attaching the db that works for me.

[yotvb531]

Strange indeed. However, the version you attached is working for me. I copy-pasted it into my real db and it is working. I messed around a little with the sorting and visual appearance of the graph. I noticed that the sorting was a little weird, and almost got it to where it would sort most recent month to oldest month. But then it was counting 9, 8, 7, 6, 5, 4, 3, 2, 12, 11,10, 1.... I tried changing my computer short date settings to be double digits, but that didn't seem to affect it, even after restarting access. I finally figured I'd try an IIf statement in there.

MonYear: Year([Date]) & IIf(Month([Date])<10,"0" & Month([Date]),Month([Date]))

works like a charm.

I guess you can call this case closed. Thanks for your help!

[June7]

Oops, I should have formatted the month/date string to always have 2 digits for month or convert the string to a number value.

Format([Date], "mmyyyy")