Posting data to tables problem

[faizulhu]

Please can't someone rescue me from this problem.

I have a database containing 3 tables: Book, Member, and Loan. A relationship exists between the 3 tables(the relationship has no problems, I have enabled referential integrity). I created a query named Loan Query which uses all the 3 tables.

In the loan Query, the Member ID field is in Loan Table and the Member Name field is in the Member Table. This allows the database to automatically type the Name when the Member ID is entered into the query retrieving the information from the Member Table. This also works fine. But the problem is that the Name field of the Loan TABLE (not query) remains blank.

All I want to do is for the database to also write the Name in the Loan Table along with the Loan Query. I will also have to apply the same for other fields also.

I am a beginner in Microsoft Access and so whosoever replies, please elaborate the solution.

 

[Pat Hartman]

Access will not automatically copy data because that would violate database normalization standards. The only data that is "copied" to another table is the key field which allows the tables to be joined. So the Loan table should have the MemberID to join back to the member table and the BookID to join back to the book table. It should also have in and out dates and any other columns that relate specifically to the loan. To create loan records, you would choose a member and choose a book from combos on your form.

If you don't need to maintain history, a simplier solution is to eliminate the loan table and place the MemberID in the Book record. When the book is returned, replace the MemberID with nulls to indicate that the book is back in the library.