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

    Unanswered: Finding the 24-month High/Low

    Hey, just posting another thread for a different Access related question. Basically this is in regards with the Stock Market Database that Im designing. I need to be able to show the 24-month High/Low values for the daily market update. The only data which will be inputted daily into the database would be the daily Stock values for the 70-odd companies, the price of brent fuel and the different currency rates into USD.

    Now the database already houses 5+ years worth of stock data for each company, I want to be able to show the 24-month High/Low Stock values.

    The table which stores the Historical_Stock_Data has the following fields:

    StockCode(PK), Date(PK), SharePrice, Volume

    There is a composite key between (StockCode, Date).

    Cheers!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Date is a bad name for a column.
    List of reserved words in Access 2002 and in later versions of Access
    Have a look at the Date() function and also the DATEADD() function. Using the two you can create a where clause to only return the last two year's worth of data.

  3. #3
    Join Date
    Jun 2010
    Posts
    58
    My mistake in the thread, I've actually named the field Dates. Going to have a look at those two functions now & come back to you.

  4. #4
    Join Date
    Jun 2010
    Posts
    58
    Hmmmm...ok have figured out how the DateADD function works.

    With the following statemet:

    Code:
    SELECT Historical_Stock_Data.Dates, Historical_Stock_Data.SharePrice, Company_Information.CompanyName
    FROM Company_Information INNER JOIN Historical_Stock_Data ON Company_Information.StockCode = Historical_Stock_Data.StockCode
    WHERE (((Historical_Stock_Data.Dates)=DateAdd("yyyy",-2,Date())));
    It gives me the value of the stock 2 years ago. Now have to get the value over the two years, would I have to use a between somewhere in the middle of the WHERE clause?

    Edit: Figured it out, was just missing '>=' in the WHERE clause, still learning!

    Ok now gotta use these values and find the MAX(..) and MIN(..) values.
    Last edited by md85; 06-30-10 at 13:14.

  5. #5
    Join Date
    Jun 2010
    Posts
    58
    Ok, have gotten a derived table from the query 24-months like I said above, have been trying to use the MAX and MIN functions. First I thought I would try and get just the MAX value and got the following code.

    Code:
    SELECT Max([24-Months].SharePrice) AS 24_high, [24-Months].CompanyName, [24-Months].Dates
    FROM [24-Months];
    This is just to give the company name, the highest value in 24 months and thats it. But I get the error 'You tried to execute a query that does not include the specified expression 'CompanyName' as part of an aggregate function'. Not really sure how to tackle this.

  6. #6
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    You need to group by the company name.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Rogue is right. Any column in the SELECT clause that is not an aggregate expression or constant must be in the GROUP BY clause.

    BTW - you don't need a derived table for this query, nor a stored query (which is actually what it looks like you have used). You can roll it all up in to one query.

  8. #8
    Join Date
    Jun 2010
    Posts
    58

    Stock Market database - Critique & Normalization

    Edit: My Mistake!
    Attached Files Attached Files
    Last edited by md85; 07-01-10 at 11:07.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    this isn't a new thread. Also, if you are intending to ask this in DB Concepts then I recommend you post your design either in relational notation, Standard SQL or an image. Most respondents will not download an Access database.

  10. #10
    Join Date
    Jun 2010
    Posts
    58
    Lol.....my mistake, was actually starting the new thread at the same time, so posted this in here by mistake!

  11. #11
    Join Date
    Jun 2010
    Posts
    58
    Ok, was assigned some other work to do but finally got back around to the database today. Tried the Max function out; but its not giving me the right output. Would it be in the following way?

    Code:
    SELECT Historical_Stock_Data.Dates, Max(Historical_Stock_Data.SharePrice) AS ['24 Month High'], Company_Information.CompanyName
    FROM Company_Information INNER JOIN Historical_Stock_Data ON Company_Information.StockCode=Historical_Stock_Data.StockCode
    WHERE (((Historical_Stock_Data.Dates)>=DateAdd("yyyy",-2,Date())))
    GROUP BY Historical_Stock_Data.Dates, Company_Information.CompanyName;

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You won't want to group on dates.

  13. #13
    Join Date
    Jun 2010
    Posts
    58
    Ok cheers for that, working great now, but what if I want to show what dates the two 24 month High & Low took place on as well. I know it'll probably display them in different rows from each other. Is there a way to Concatenate the dates in the same field as the High/Low in the following way:

    CompanyName 24 Month High 24 Month Low

    PremierOil 1587 - 07/07/2008 569 - 27/10/2008


    Im trying to firgure out how I could use the earlier Concatenate Function that you showed me for this. Can I use it again in the same query as the following:

    Code:
    SELECT Max(Historical_Stock_Data.SharePrice) AS ['24 Month High'], Min(Historical_Stock_Data.SharePrice) AS ['24 Month Low'], Company_Information.CompanyName
    FROM Company_Information INNER JOIN Historical_Stock_Data ON Company_Information.StockCode=Historical_Stock_Data.StockCode
    WHERE (((Historical_Stock_Data.Dates)>=DateAdd("yyyy",-2,Date())))
    GROUP BY Company_Information.CompanyName;
    Or should I make another query?

  14. #14
    Join Date
    Jun 2010
    Posts
    58
    Tried something different, but I know this is wrong because the Date also gets aggregated at the same time as the SharePrice, but the odd thing is it shows the right values for one set of sample data, but the wrong values for another set of sample data.

    Code:
    SELECT Max([SharePrice] & " - " & [Dates]) AS ['24 Month High'], Min([SharePrice] & " - " & [Dates]) AS ['24 Month Low'], Company_Information.CompanyName
    FROM Company_Information INNER JOIN Historical_Stock_Data ON Company_Information.StockCode = Historical_Stock_Data.StockCode
    WHERE (((Historical_Stock_Data.Dates)>=DateAdd("yyyy",-2,Date())))
    GROUP BY Company_Information.CompanyName;
    Do you think the solution to this particular problem is as simple as just using the & operator? Since its not the same as what I had to do for the Location concatenate problem.

Posting Permissions

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