Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2010
    Posts
    58

    Unanswered: Charts and Reports

    Hey all, have got a little problem with the charts and reports in the database that I have made.

    From the last time I was here, I picked up and learned a bit of VBA and combined all my queries into one big VBA code which deals with all the calculations and generates a number of different tables at the end. Using the VBA gave me the flexibility to automate a number of tasks and also gave me a better option to hard-code some pre-requisuites into the database design.

    Now I've got to a point where I have all my forms ready and my reports are also almost done. But I have had a couple of small problems with the report part of things. I've designed all of these in Design view.

    First problem is with this sub-report that I have, it is a sub-report holding Annual Financial information about the specific companies. It's been combined into another report which holds all the information about the companies, when you enter the name of the company in the form, it pulls up all this specific information that has been calculated, plus the financial info. The financial sub-report is in a little table of its own on the main-report, the problem is that depending on the number of years worth of data there is in the table, it'll actually generate that many sub-reports and tables, so if it has information about the years 2008 & 2009 then it'll show both those years in one sub-report and generate, two such sub-reports in the main report.

    The second problem I've had is with the charting, I found out how to use the chart but for some reason it is not filtering the chart based on the company name that is entered, for some reason it only generates one chart for the first name it finds in the Historical_Stock_Data table. Not sure how to deal with this.

    Edit: Btw using Access 2007
    Last edited by md85; 09-21-10 at 08:18.

  2. #2
    Join Date
    Aug 2010
    Location
    NYC
    Posts
    10

    suctrep grade

    It's hard to diagnose the issue without seeing the code thats not working properly. I'd recommend posting the mdb / accdb file or at least a snippet of the code involved with generating the charts.
    --------------------------------------------------
    Matt
    Case Culture - Fashion Cases for iPhone and BlackBerry

  3. #3
    Join Date
    Jun 2010
    Posts
    58
    Thing is I used the design tools in Access to do it all without actually touching the code itself. (Wasn't too sure about how to access the SQL part when I generated the chart) Looking at the property sheet in Access, the Row source SQL is as follows:

    Code:
    TRANSFORM Sum(Historical_Stock_Data.SharePrice) AS SumOfSharePrice
    SELECT (Format([Dates],"Short Date")) AS Expr1
    FROM Historical_Stock_Data
    WHERE (((Historical_Stock_Data.Dates) Between #9/15/2008 17:47:42# And #9/15/2010 17:47:42#))
    GROUP BY (Int([Dates])), (Format([Dates],"Short Date"))
    PIVOT Historical_Stock_Data.StockCode;
    Now I also tried the DateAdd function on that because I want it to only use the last 24 months but it gave me an error.

    The Database file itself, even if it only holds sample data still is quite big at 52mb otherwise I would post it up here as well.

  4. #4
    Join Date
    Jun 2010
    Posts
    58
    Code:
    SELECT Final_Table.Name, Final_Table.Area, Final_Table.LastTrade, Final_Table.Change, Company_Information.CompanyDescription, Company_Information.Listing, Financial_Info.WhichYear, Financial_Info.Sales, Financial_Info.Production, Financial_Info.Revenues, Financial_Info.OperatingProfit, Financial_Info.IncomeBeforeTax, Financial_Info.EarningPerShare, Financial_Info.CashFlowFromOperation, Company_Information.[CEP NAV], Final_Table.MonthsHigh, Final_Table.MonthsLow, Company_Information.CurrencyCode, Company_Information.NOSH, Final_Table.MarketCap, Final_Table.MarketCapDollars, Company_Information.StockCode
    FROM (Company_Information INNER JOIN Final_Table ON Company_Information.CompanyName = Final_Table.Name) INNER JOIN Financial_Info ON Company_Information.StockCode = Financial_Info.StockCode;
    The above is the source code for the Main report, the sub-report basically includes the Financial_Info table embedded in a sub-report in the main report.

    Again I used the Design tools by dragging and dropping the appropriate tables, basically using the Access tools that are there to make the reporting easier and much more nicer to do, again the problem with this is that when there are more rows in the table, then there are more embedded tables generated as well. So for example, if the financial table has years 2008 & 2009, then both will appear in the embedded report & for some reason access will generate a second embedded report below the first one.

  5. #5
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    It sounds like your data is not being filtered consistanly. It may be easier to base your reports/charts/graphs on a query rather than a table. Your criteria can then be added to the query.

  6. #6
    Join Date
    Jun 2010
    Posts
    58
    Hey was able to figure it out, there was just something wrong with some of the filtering and the groupings.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •