Table As A SubTable Of A Query

[AJJR]

I am very new to Access.

I have simplified my data in order to isolate my problem which is as follows.

I currently have a task that I organize on paper and would like to write a database to do it.

The task is tracking review dates for stocks I follow and storing summary notes for each successive review.

What I do now is write the next date I wish to review a stock on a calendar. When that date arrives I do the review and write a summary which I store in binder with other summaries for the same stock.

My plan for organising this in Access is:

To create two tables.

One table, called Stocks, has two fields in each record. One field (the key field) is for the Ticker Symbol of the stock. The second field is for it's next review date.

The second table, called Summaries, has four fields. One is an auto generated key field, one is for the Ticker Symbol of the Stock the summary is on (A lookup field from the Stocks Table) and the date the summary is done.

I can open the Stocks Table, sort it by review date, and see any stocks that are due for a review. For those that are, as the Summaries table is a Subtable of Stocks, I can click on the + sign for the record that holds the stock I want to review, write the summary in a new record, and add the date I have done it on. This works fine.

But, as it's a learning project (one that I can build upon to eventually incorporate forms etc) I would like to Query the Stocks table for only records that have a review date on or before today. I would like to then assign the Summaries Table as a Subtable of the query and have it work the same as it does in the above paragraph.

When I try and do this, however, all the records in the Summaries table appear beneath each record in the query I expand the subtable for.

I have tried adding a relationship between the Ticker Symbol field in the query and both of the other tables, exclusively and at the same time but that didn't work.

I would really appreciate any guidance.

Thanks

[Rabbit]

Can you post the SQL that you're using?

[Seth Schrock]

I would highly recommend reading the following link: Database Normalization and Table Structures. This will help you design your database so that it can be added to very easily without major changes to your existing design. Also, you gain much more control over your data if you use forms to view the data instead of looking at the table.

[ariful alam]

If you need to show the specific data from table B based on table A's selected data, than you should use Form. Create a form for table A and add table B as a sub form of the form that created for table A. When you add table B as a sub form in form A, a window will appear to link the form records based on a common/key field. choose, by own and specify the field name in both form to link the records.

Thanks

[twinnyfo]

AJJR,

I think I have a very easy solution for you. I just created a main form for your Stocks, showing the next review date. However, I filtered that form by all dates <= Date().

Then I created a subform for your summaries, but it is filtered by only dates >= Date(). These are Master/Child by the Stock ticker primary key.

Whenever a summary is due, you can add your summary. When you click save, the current date is saved to the summary table and it resets the next summary date (arbitrarily 7 days from now).

Hope this helps, but I agree with others, that you should take a look at normalization first.