Stock Market Database: Charts and Reporting problems.

[mddb]

Hey all, have been tasked to design a database at work, that can hold information about some 70 odd companies. The database holds information about their financials, Historical Stock data, historical currency data and a few other things about each of the companies.

I'm no database expert by a long shot, but with some help and using some of my past knowledge in basic programming I was able to write some VBA code, to generate some calculated tables for a number of other values.

Now there are two issues that I'm currently having:

Firstly, it's regarding the charts option in Access; I need to be able to generate daily stock market price change and volume charts using the data in the Historical_Stoc k_data table; which has a composite key of (StockCode,Date s). I was able to generate both charts fairly easily and modify them but I'm not totally sure why it isn't filtering the chart for any of the other companies, it's only using the data for the first company starting with the letter 'A'. Not too sure how this will work.

Secondly, I'm generating single company reports, which holds information about there daily price, 24 month Hi/Lo, Market cap, informationg about their concessions plus all the financial information which is in that table as well. Now what I did here was, designed a subreport which holds the yearly financial data and embedded it in the main report. The filtering here for the companies works fine; in that when I press a button on the form, it prompts for the company name, then it brings up a report based on that. The problem here is, depending on how many years worth of information there is in the financial table, the report generates that many embedded subreports in the main report; for example when I put data for 2009 & 2010 it generates two embedded subreports in the main report, both showing the same data.

Any help would be appreciated.

[parodux]

What is the SQL for the chart?

What is the SQL for the report?

What is the SQL for the subreport?

And what fields binds the subreport to the report?~)

[mddb]

I'm using Access 2007, so for this part have been using the wizard to design the reports and forms. Not sure how to access the SQL part of it.

[mddb]

The SQL for the chart is:

TRANSFORM Sum(Historical_ Stock_Data.Shar ePrice) AS SumOfSharePrice
SELECT (Format([Dates],"Short Date")) AS Expr1
FROM Historical_Stoc k_Data
WHERE (((Historical_S tock_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_Stoc k_Data.StockCod e;


Now it needs to filter the data by StockCode, so it can generate the graphs for each company seperately

[mddb]

The source code for the main table is:

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 embedded table gets its information from the Financial_Info table; and this is just straight information sorted by Year; no queries or calculations.

[parodux]

Hi, I would like to help... but time is ..... if you send me the db I will take a look and see what I can do!~)

[mddb]

Hey pardoux, thank you very much for the offer, if you pm me your email address I will send a version with only sample data to you. (Its about 7mb in size after I took off all the company logos from the reports).

[parodux]

severin at my username dot com

[mddb]

Have sent it to you! Thanks!

[parodux]

Hi,

* the filter on the company report buttons macro was a bit off
* the groupings on the report was a bit mixed up
* the financial info had to be taken out of the SQL for the report. That's what created the repetitions.
* the charts are made from a cross tab query, which is wrong in this case
* there was a company filter missing on your charts report
* and little things here and there....
* I added a listbox for your companies that can be used as filter

enjoy!~)

[mddb]

Thank you very much! It works perfectly now, really appreciate the help.