Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    Join Date
    Jun 2010
    Posts
    58

    Unanswered: Stock Market Database

    Hey all, just starting a new thread here in this forum. This is following on from my earlier thread at http://www.dbforums.com/database-con...-database.html . I got help for some database design issues on there. Have started by designing a little test database with only a few companies and their historical stock market information.

    I'll give an overview of what has been achieved so far:

    I've got a CompanyInfo table, which stores all the data that does not really change, a location table because many companies could have the same location, and a company could also have multiple locations, a HistoricalStockData table, with 5 years worth of stock market data, with a composite (StockCode, Date) key, a FinancialInfo table showing annual sales & EBIT information of a company and finally a HistoricalCurrency table with exchange rates into USD for companring and analyzing data in the end in a single currency.

    have now come to the queries part; firstly I would like to know how to display data together in one field instead of in differen fields. For example; a company called Premier Oil has licences in 3 different countries, with the select query that I have made it outputs the same data but all in different rows like so:

    CompanyName | StockCode | NOSH | Locaion

    Premier Oil | PMO.L | 115mn | UK

    Premier Oil | PMO.L | 115mn | Indonesia

    etc...

    I mean I would rather it outputted the data in the following way:

    Premier Oil | PMO.L | 115mn | UK, Indonesia.....

    Secondly I've worked out DMAX is there to select the highest value over a certain time. Whats the formula if I want to select the last value and then use it in a calculation? This would be for the Historical Stock Data table which has a composite (StockCode, Date) key, so I would want to select the last possible date to calculate the Market Cap for that particular day.

    Would like to add, that the above where I'm asking for the information to be grouped together, the information is taken from two different tables; One which is purely for CompanyInformation and the second which purely has Locations; there is a third table which I use to form the association between these two tables. This is because many of these companies share locations, many have multiple locations so I thought it was best to do it in the way I did, correct me please if Im wrong?

    Cheers!

    Edit: would like to state that am using Access 2007
    Last edited by md85; 06-22-10 at 08:36.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is quite a verbose solution but follows the standard pattern for this in VBA. I would strongly recommend (from bitter experience) you don't use his error message on error though - just return a string saying something like "ERROR". I would make this recommendation for any function you call from a query.
    Microsoft Access tips: Concatenate values from related records

  3. #3
    Join Date
    Jun 2010
    Posts
    58
    Thanks! That is quite a lengthy soultion giving it a try now.

    Also another thing I wanted to ask was; I have my HistoricalStockData table which has the composite (StockCode, Date) key and a field for StockValue, now I want to be able to do some calculations, but these need to be done on the last date or latest date that is there, what sort of query would I need to be able to do that?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If I said "use a derived table" would you have an idea of where to start?

  5. #5
    Join Date
    Jun 2010
    Posts
    58
    Well as you can probably tell from the questions that I've been asking, I'm pretty new to both database design and Access but just from the name of what you have mentioned it seems like a table that would be brought up through a query and then used for calculations?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Precisely. This is a problem to work through logically.

    First step - write a query that returns last date for each stockcode.

  7. #7
    Join Date
    Jun 2010
    Posts
    58
    Ok that sounds simple.

    Something like the following:

    SELECT Company_Information.CompanyName, Historical_Stock_Data.SharePrice, Historical_Stock_Data.Dates
    FROM Company_Information INNER JOIN Historical_Stock_Data ON Company_Information.StockCode = Historical_Stock_Data.StockCode
    WHERE (((Historical_Stock_Data.Dates)=Date()))
    ORDER BY Company_Information.CompanyName, Historical_Stock_Data.SharePrice;

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by md85 View Post
    Ok that sounds simple.
    Yah - but not as you wrote it. You've tried to jump ahead too many steps.

    We don't care about the name or shareprice at this stage. And we want the most recent (not necessarily today's) date.
    Code:
    SELECT Historical_Stock_Data.StockCode, MAX(Historical_Stock_Data.Dates) AS last_date
    FROM Historical_Stock_Data
    For each stock code we have the last date - agreed?

  9. #9
    Join Date
    Jun 2010
    Posts
    58
    Ok got it. So now we have the last date for each stock code.*

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Exactly. I am struggling for time - read this:
    SQL – Derived Tables Tech Ahead
    The above SQL is the derived table. Use the link for inspiration (the only thing you can't use are the comments AFAICS).

  11. #11
    Join Date
    Jun 2010
    Posts
    58
    Sorry for the late response earlier, had other tasks that I had to get done at the office and thanks alot for the help! You have pushed me in the right direction, thats for sure. Will update you on how I get along with it.

  12. #12
    Join Date
    Jun 2010
    Posts
    58
    Quote Originally Posted by pootle flump View Post
    This is quite a verbose solution but follows the standard pattern for this in VBA. I would strongly recommend (from bitter experience) you don't use his error message on error though - just return a string saying something like "ERROR". I would make this recommendation for any function you call from a query.
    Microsoft Access tips: Concatenate values from related records
    Also, been having some issues with getting the filtering criteria for this right. How would I word the argument in the 3rd criteria if I have 3 tables, One the Company_Information table with a primary key called StockCode, second the Location table which is just a locations table with LocationID primary key and the third is the table which I have used to connect these two with a StockCode foreign key and LocationID foreign key. This is due to the fact that both the Company_Information and Locations tables have a many to many relationship between in each other, so thought this was the best way to go abouts it. Correct me if I'm wrong.

    Edit: Specifically talking about ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID]) the bold part.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Post the query you have made to produce the data in post one, where there is one location per row. Put it in to the code tags. I will edit it for you and explain what I have done.

  14. #14
    Join Date
    Jun 2010
    Posts
    58
    Code:
    SELECT Company_Information.CompanyName, Company_Information.StockCode, Company_Information.NOSH, Company_Information.[CEP NAV], Location.Location
    FROM Location INNER JOIN (Company_Information INNER JOIN CompanyLocation ON Company_Information.StockCode=CompanyLocation.StockCode) ON Location.ID=CompanyLocation.LocationID
    ORDER BY Company_Information.StockCode, Company_Information.NOSH, Company_Information.[CEP NAV];
    Edit: Had the wrong code before! :P
    Last edited by md85; 06-24-10 at 05:41.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Right - I won't edit the code because of time again!
    You can do it though.
    You need to:
    a) remove the location table from the FROM clause
    b) Remove Location.Location from the SELECT clause
    c) Edit the code in post #15. The table is Location, the first argument is Location and the last argument will be LocationID. Add this to the select clause.

Posting Permissions

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