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_Stock_data table; which has a composite key of (StockCode,Dates). 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.
10 5348
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?~)
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.
The SQL for the chart is:
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 it needs to filter the data by StockCode, so it can generate the graphs for each company seperately
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.
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!~)
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).
severin at my username dot com
Have sent it to you! Thanks!
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!~)
Thank you very much! It works perfectly now, really appreciate the help.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Chris Mosser |
last post by:
I'm looking to add a page to my site where I can get stock quotes for the
wired, but more importantly, the wireless web(ie my cell phone). I know how
write the needed php and WAP application, I...
|
by: quartz12h |
last post by:
Hi,
We tried 2 techniques to mesure db size and both fails to return
realtime size.
a) under innodb, we ’show variables’, find the innodb_data_file_path
variable and parse the size (works...
|
by: Mudcat |
last post by:
I have done a bit of searching and can't seem to find a stock market
tool written in Python that is active. Anybody know of any? I'm trying
not to re-create the wheel here.
|
by: adamwhite567 |
last post by:
Hi
I am quite new to access and i can grasp things quite easily.
Basically i am make a stock control data base for a food company. I am making it so theres an order table and a stock table....
|
by: adamwhite567 |
last post by:
Hi
I am quite new to access and i can grasp things quite easily.
Basically i am make a stock control data base for a food company. I am making it so theres an order table and a stock table....
|
by: asmusdk |
last post by:
I am working on creating a stock management database for a small company.
The company receives the merchandise from a handful of suppliers and stocks it before it is sent to the customers –...
|
by: tellme |
last post by:
hi...i've one project and need to complete insteadly.I need to develop one system to record a stock.My problem is when we insert new stock,the quantity of the stock in the database will be increase...
|
by: lawardy |
last post by:
Hi,
I'm quite new with Reporting server 2005.
I'm wondering if there is a way to write a select query that will get the data from 2 diffrent tables located in 2 diffrent database box.
for...
|
by: Grzegorz Danowski |
last post by:
Hello,
I have made few reports in rdlc technology. They look and print fine, but if
I export any report to pdf file I have some problems with polish specific
chars:
a. if I open pdf in...
|
by: Shalini Bhalla |
last post by:
i want to make a site on Stock Market for which i need to capture current stock market prices for processing . Can you tell me how i an get that data ?
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |