So I'm entirely new at Access, and have only a very elementary background in SQL and general database stuff, but I've been asked at work to build a small database with book titles and authors. I've got a table of authors, who've got auto numbered IDs, and a table of titles, who have their own auto numbered ID as well as a field for the author's ID, into which should go the value from the author table's auto numbered field. I think this is the correct way to do it, but I'm not even certain about that.
Now what I want to do, is be able to create an append query which allows someone to fill in a title and an author, and have that record added to the title table, with the AuthorID field in the title table being filled with the AuthorID from the author table which corresponds to the author name entered. Is this something I can do? Am I trying to go about this all wrong? I appreciate any and all advice on how I could make this work, thank you!
Rudimentary work, here. First of all, do not allow your users to fill in a text field for Author. Bad, bad idea. Misspellings, typos, etc... will all work to make it a nightmare. Make the Author a dropdown, and put 2 columns in that dropdown; Author Name and Author ID. Pull the data from the Authors table. You can hide the Author ID field by making it's width equal to 0. Now, when you're writing the values to the table, you take the dropdown's Value (as opposed to Text) and then you're writing the Author ID into your table just as you requested.