0

I have two Microsoft Access database tables. They are named Historical_Stock_Prices and Balance_Sheets. I need to combine data from each of these tables to create a table called Daily. I need to take the fields Ticker, [Date], and [Close] from Historical_Stock_Prices and I need to take the field Common_Stocks from Balance_Sheets.

I will not be taking every row from the Historical_Stock_Prices and Balance_Sheets though. I will only be taking the rows that are on or before a date selected in a DateTimePicker named dtpDateSelection.

Now the main problem that I have is that Historical_Stock_Prices contains a row for each day. While Balance_Sheets contains a row for each quarter. So for each day in a quarter the figure Common_Stocks figure that comes from Balance_Sheet will be the same.

How do I do this?

Here is the code that I have so far:

Dim Date1 As Date = dtpDateSelection.Value
Dim cmd As OleDbCommand = New OleDbCommand("CREATE PROC Daily AS SELECT Ticker, [Date], [Close] From Historical_Stock_Prices WHERE [Date] = " & Date1 & "", con)
cmd.ExecuteNonQuery()

This obviously does not incorporate the table Balance_Sheet at all. Also, currently my where statement is throwing an error.

Additional Information: Table Schema

Balance_Sheet Schema

Historical_Stock_Prices Schema

gromit1
  • 577
  • 2
  • 14
  • 36
  • 1
    How are Historical_Stock_Prices and Balance_Sheets linked together? Do they have a primary/foreign key? If they don't have a primary/foreign key then I am guessing they are linked via a Quarter and Year field. – Linger Oct 02 '13 at 20:08
  • @Linger They are not linked. I'm not sure what the best way to link them is. `Historical_Stock_Prices` has a primary key of two fields. They are `Ticker` and `[Date]`. `Balance_Sheets` has a primary key of three fields. They are `Ticker`, `[Year]` and `Period`. – gromit1 Oct 02 '13 at 20:11

3 Answers3

2

Inner join on the quarter that Historical_Stock_Prices and Balance_Sheets have in common. That is, every date falls in a quarter. If Balance_Sheets has a field named Period that is the number 1, 2, 3 or 4, corresponding to each quarter in a year, and you have a Year field that is a 4 digit number then this select query should work and can be easily converted into a make table query.

select Ticker, Date, Close, Common_Stocks
from Historical_Stock_Prices, Balance_Sheets
where Format(Historical_Stock_Prices.Date, "q") = Balance_Sheets.Period
      and Year(Historical_Stock_Prices.Date) = Balance_Sheets.Year
Jeremy Cook
  • 20,840
  • 9
  • 71
  • 77
  • The suggested code returned this error message `Additional information: Cannot find table or constraint.` – gromit1 Oct 02 '13 at 19:48
  • 1
    Do you have a field in Balance_Sheets named Quarter or something equivalent to it? I see that you mentioned Year and Period fields above. Does Period represent a quarter (e.g. have the value 1, 2, 3 or 4)? – Jeremy Cook Oct 02 '13 at 20:15
  • I've changed `Balance_Sheets.Quarter` to `Balance_Sheets.Period` but I am still getting the same error. – gromit1 Oct 02 '13 at 20:25
  • 1
    Try this out. When I tested something like it in Access 2010 it didn't like the Format(...) in the inner join. So I've cross joined the tables (using a comma instead of the words inner join) and moved the on statement into a where clause. Also make sure all of the field and table names exist and are spelled correctly and that none of them have whitespace. If that doesn't work you'll need to post the schema of your two tables for us to review. – Jeremy Cook Oct 02 '13 at 21:20
  • I have verified that all my field and table names exist and are spelled correctly. I guess the next step is posting the schema of my two tables. What is the best way to do this? – gromit1 Oct 03 '13 at 13:48
  • I have added the schema of my two tables to my original post. – gromit1 Oct 03 '13 at 14:38
  • 1
    Have you tried running the select query from within the Access database? If it does not work, can you run a very simple query from within Access and from vb.net (e.g. `select * from Historical_Stock_Prices`)? – Jeremy Cook Oct 03 '13 at 14:42
  • The whole Select query does not work withing Access. `select * from Historical_Stock_Prices` and `select * from Balance_Sheets` does work. I think one problem may be that in your answer `Date` and `Close` should be `[Date]` and `[Close]` – gromit1 Oct 03 '13 at 14:47
  • When I try this `select Historical_Stock_Prices.Ticker, [Date], [Close], Common_Stocks from Historical_Stock_Prices, Balance_Sheets where Format(Historical_Stock_Prices.[Date], "q") = Balance_Sheets.Period and [Year](Historical_Stock_Prices.[Date]) = Balance_Sheets.[Year]` within Access I do not get the `Snytax Error` message anymore. I get this error message `Type mismatch in expression` – gromit1 Oct 03 '13 at 14:52
  • I think the `Type mismatch in expression` is because I'm trying to link the table using `Period` (as an integer) from `Balance_Sheets` to `[Date]` (as a date) from `Historical_Stock_Prices. – gromit1 Oct 03 '13 at 15:36
  • 1
    Looks like Access unhelpfully converted `Year(Historical_Stock_Prices.[Date])` to `[Year](Historical_Stock_Prices.[Date])`, which is causing it to try and reference the [Year] field instead of use the Year(...) method. Replace `[Year](Historical_Stock_Prices.[Date])` with `Format(Historical_Stock_Prices.[Date], "yyyy")`. – Jeremy Cook Oct 03 '13 at 16:49
  • I made the changes and I still get `Type mismatch in expression` – gromit1 Oct 03 '13 at 17:20
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/38564/discussion-between-jeremy-cook-and-gromit1) – Jeremy Cook Oct 03 '13 at 19:30
2

Create query off of Historical_Stock_Prices that includes 2 new fields, Quarter and Year, with the appropriate functions to pull those values from the date of the row. Then join your two tables using those fields. Your final statement in VB should go against that query.

As for why you are getting an error on the SQL statement, you do not have # around the parameter.

Dim cmd As OleDbCommand = New OleDbCommand("CREATE PROC Daily AS SELECT Ticker, [Date], [Close] From Historical_Stock_Prices WHERE [Date] = #" & Date1 & "#", con)

Note that this is still bad form, and is just waiting for SQL Injection attacks. BEst to look up how to use parameterized queries.

APrough
  • 2,671
  • 3
  • 23
  • 31
  • Thanks for the suggestion. I will work on it. I had previously tried using a paramaterized query but i was getting an error on that too. Here is what I had: `SELECT Ticker, [Date], [Close] From Historical_Stock_Prices WHERE [Date] = ?", con) cmd.Parameters.AddWithValue("?", Date1)` – gromit1 Oct 02 '13 at 19:55
  • 1
    It's usually better to use something like this `cmd.Parameters.Add("@Date", OleDbType.Date).value = Date1` – APrough Oct 02 '13 at 20:03
2
"SELECT [H].[Ticker], [H].[Date], [H].[Close], [B].[Common_Stocks] 
FROM [Historical_Stock_Prices] AS [H], [Balance_Sheets] AS [B] 
WHERE [H].[Ticker] = [B].[Ticker] 
AND Int(Format([H].[Date],'Q')) = Int([B].[Period]) 
AND Int(Year([H].[Date])) = Int([B].[Year]) 
AND [H].[Date] <= #" & Date1.value & "#"
Linger
  • 14,942
  • 23
  • 52
  • 79
  • The `Period` is numeric. I got this error though `Additional information: Cannot find table or constraint.` – gromit1 Oct 02 '13 at 20:30
  • 1
    take out the `ON Year(H.Date) = B.Year` and `AND Format(H.Date,'Q') = B.Period` and change `AND H.Ticker = B.Ticker` to `ON H.Ticker = B.Ticker` and try it just to see if you get the same error – Linger Oct 02 '13 at 20:34
  • 1
    @nordeen1, are you sure you have a table called Historical_Stock_Prices? Or is it "Historical Stock Prices"? – APrough Oct 02 '13 at 20:41
  • @Linger Yes. The name of the table is Historical_Stock_Prices. – gromit1 Oct 02 '13 at 20:43
  • 1
    This error `Additional information: Cannot find table or constraint.` occurs when Access cannot find a table by the name you are using. Check both of your table names for spelling, and that there are no blank spaces in it (including beginning and end) – APrough Oct 02 '13 at 20:47
  • 1
    Are you running the query as is, or are you creating a procedure with it? Try running as a query without creating a procedure to see if it works – Linger Oct 02 '13 at 20:48
  • @APrough I have checked all of my table and field names for spelling and blank spaces and I haven't found any. – gromit1 Oct 03 '13 at 13:46
  • @Linger I was running a procedure with the query but I have since tried just running the query and got an error that way too. – gromit1 Oct 03 '13 at 13:47
  • 1
    Man, it should work as it is. But, I edited my answer to try something else. If it doesn't work check every field and table name closely to make sure they are correct. – Linger Oct 03 '13 at 13:58
  • @Linger Same error. Someone suggested to try posting the schema of my two tables for review. Any idea on the best way to do this? – gromit1 Oct 03 '13 at 14:04
  • 1
    Just open the tables up in design view and get a screneshot of them – Linger Oct 03 '13 at 14:12
  • @Linger I added brackets to some of the words to clear up some syntax errors I was getting. Here is the new code: `SELECT H.Ticker, H.[Date], H.[Close], B.Common_Stock FROM Historical_Stock_Prices AS H INNER JOIN Balance_Sheets AS B ON [Year](H.[Date]) = B.[Year] AND Format(H.[Date],'Q') = B.Period AND H.Ticker = B.Ticker WHERE H.[Date] = #" & Date1 & "#"`. But I am still getting this error `Type mismatch in expression` – gromit1 Oct 03 '13 at 15:00
  • 1
    `B.Common_Stock` should have been `B.Common_Stocks`. It looks like you missed the `s` on the end of the field name. I have updated my answer to reflect this. – Linger Oct 03 '13 at 15:06
  • @Linger I've changed to `B.Common_Stocks`. I'm still getting this error `Type mismatch in expression`. – gromit1 Oct 03 '13 at 15:10
  • 1
    I changed the query to force the period and year to an integer – Linger Oct 03 '13 at 15:14
  • @Linger I'm getting `Syntax error` again. I think it may have to do with words like `Year` and `Date` not being `[Year]` and `[Date]` – gromit1 Oct 03 '13 at 15:24
  • @Linger I think the `Type mismatch in expression` is because I'm trying to link the table using `Period` (as an integer) from `Balance_Sheets` to `[Date]` (as a date) from `Historical_Stock_Prices. – gromit1 Oct 03 '13 at 15:35
  • Actually in the above query the period and the result of the yaer function are being converted to Int. I have modified the query again to force everything to convert to what is should be and added the brackets around all of the fields. – Linger Oct 03 '13 at 15:36
  • @Linger I don't get any errors in VB.net but when I view the table `Daily` there are no rows. The columns are there but no data in the rows. – gromit1 Oct 03 '13 at 15:48
  • @Linger Any idea why there would be no rows? – gromit1 Oct 03 '13 at 17:53
  • Try the JOIN with just `CStr([H].[Ticker]) = CStr([B].[Ticker])` and you definitely should get rows. Then add the Year part of the join and check. Then add the period and check. Find out where you are not getting any results. You could also try the Year, Ticker, and Period in a WHERE clause instead of a join – Linger Oct 03 '13 at 19:18
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/38563/discussion-between-linger-and-gromit1) – Linger Oct 03 '13 at 19:24