Help with designing tables

[Artful Dodger]

Hello there,
I posted a thread yesterday and then said that I have solved the problem. I was wrong. Here is what I am trying to do.
Using Access 2002 I am trying to design a database to store some financial data. Below are the fields for the first table:
Stocks (Symbol, Ticker, Country, Currency, etc). Note that not all fields are listed.
I want to have another table (even more) to store the schedule for the dividend. Basically for each new entry in the Stocks table I want the Symbol field to be inserted into the Div_Schedule table that stores the Symbol, dates when the dividend is due and its value.

Can you please suggest how do I go about it.

Your help is greatly appreciated.
Thank you.

 

[stephen81]

Can you give an example of the data you want in the tables? Is there going to be only one entry in the div_schedule table for each entry in the stocks table and vice versa?
Where do the dates and dividend value come from for the div_schedule table?

 

[Artful Dodger]

Hello Stephen,
Here is some data that will go into the Stocks table:

Symbol TUI_AI
Ticker TUI_AI Equity
Currency EUR
Country Germany
and so on.

The Div_Schedule table is tricky. I want whenever a new value in the Stocks table is entered same value to go into the Symbol1 field in the Div_Schedule table. There could be many (up to 15) different entries for each stock that appears in the Stocks table. These values depend on the date that the dividend is due.
And to answer your third Q. The dates come from an Excel sheet.
I hope this will help.

Thank you.

 

[stephen81]

I think what you want is probably something like this.
tblStocks contains your list of stocks with stock_ID as your primary key, each only entered once though.
tblDiv_Schedule contains the records of the dates and values of the stocks, with stock_ID as a foreign key (related to stock_ID in tblStocks) and Div_Schedule_ID as it's primary key.
Each time you enter a new record in tblDiv_Schedule, as long as you put the Stock_ID that matches the corresponding stock in tblStocks you can then bring together the two sets of information with a query (see example).

I hope that makes sense. I'm sure it could be explained better but hopefully the attachment will help.

 

[Artful Dodger]

Thank you for the prompt reply Stephen. I have to go. I looked at the test mdb and I think it could be what I need. I did not think that I can do it with a AutoNumber field as a Prime Key. I shall test it tomorrow morning and will give you an answer.
Thank you. Talk to you tomorrow.