#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0

    Inserting Graph into Report, Data sorted by month


    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!
  2. #2
  3. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    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.
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0
    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.
  6. #4
  7. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    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.
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0
    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!!
  10. #6
  11. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    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.
  12. #7
  13. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0
    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?
  14. #8
  15. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    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.
    Attached Files
  16. #9
  17. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0
    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!
  18. #10
  19. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    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")

Similar Threads

  1. Report not sorted in correct order.
    By Slikbaz in forum Microsoft Access Help
    Replies: 0
    Last Post: November 22nd, 2010, 10:15 AM
  2. XML data into a graph
    By amean_n in forum .NET Development
    Replies: 0
    Last Post: June 4th, 2010, 08:57 AM
  3. Graph from a report
    By stevechriskirk in forum Microsoft Access Help
    Replies: 3
    Last Post: June 22nd, 2009, 12:16 PM
  4. HELP with graph on Report
    By sp3 in forum Microsoft Access Help
    Replies: 3
    Last Post: July 13th, 2006, 02:07 PM
  5. Data types cannot be compared or sorted
    By teeheiman in forum Microsoft SQL Server
    Replies: 1
    Last Post: March 29th, 2006, 09:43 PM

IMN logo majestic logo threadwatch logo seochat tools logo