469,964 Members | 1,581 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,964 developers. It's quick & easy.

HT make a table create new records if parent tables are updated?

2
Ver: Access 2003

Hi everyone and thanks in advance,

General Issue:
I want to make a table whose data polls from two other tables and will update (add new records) if additions are made to either of those reference tables.

Simplified Example:
Table 1 [Students] has a record for each student.
Table 2 [Library] has a record for each book in the library.
Table 3 [ReadIt] - I made this table with 4 columns - ReadItKEY(Primary key), Students (Primary key from [Students]), Books (Primary key from [Library]) and Completed (a yes/no field). I ran an update query which populated this table by making a record for every student with every book. Basically, each record equates to "Has this student read this book? - Y/N". I made Enforce and Cascade Update relationships between the parent tables and [BooksRead].

Problem:
How can I make it so that if a new student is added to [Students] or a new book is added to [Library] that [ReadIt] will update with new records for those additions?

My Solution:
The most success I've had is, after adding a new student to [Students], to run another parameter update query in which I manually enter the new student's Primary Key # from [Students]. It does the job but isn't as smooth and foolproof as I'd like.

Does anyone have any ideas on how to do this better?

Thanks again!
Dec 17 '07 #1
1 1235
Landon
2
Why is it you never see an editing problem, even after re-reading it several times, until you post it. :(

Under the Tabe 3 paragraph, the last sentence should read : "I made Enforce and Cascade Update relationships between the parent tables and [ReadIt].

Sorry everyone!
Dec 17 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Ed_No_Spam_Please_Weber | last post: by
1 post views Thread by Thanks | last post: by
5 posts views Thread by PAUL | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.