Results 1 to 5 of 5

How should I be entering data into a relational DB with multiple tables?

  1. #1
    Mynotoar is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    5

    How should I be entering data into a relational DB with multiple tables?

    I'm trying to read a dictionary file using VB.NET and output it into an Access DB. I'm a beginner with Access, so what I'm not quite sure about is how to enter data into multiple tables at once.

    For example, I've got three tables (I've got 20, but simplifying,) called "Entry", "Keyword" and "Keyword_Priority". The Entry is the main table in the database, which records one entry in the dictionary. Each entry can have multiple Keywords, and each Keyword can have multiple Keyword_Priority elements. So Entry --< Keyword is one-to-many, and Keyword --< Keyword_Priority is one-to-many.

    If I were to run a series of SQL statements to insert data into just these three tables, how would I go about it? Would I do:

    1. Insert into Entry, then store the Entry.ID somehow
    2. Insert into Keyword using the Entry.ID as a foreign key.
    3. Insert into Keyword_Priority, using Keyword.ID as a foreign key.



    Or would I do it the other way around ...

    1. Insert into Keyword_Priority
    2. Insert into Keyword
    3. Insert into Entry

    In other words, do I insert data from the top-down, or from the bottom-up?

    -

    Also, when inserting data into multiple tables, how would I store the Primary Key from each table to be used in the next SQL Insert statement?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,352
    The first process, certainly. Always enter and commit parent record first, then enter dependent records. Yes, will have to somehow grab and save the PK to save into dependent records.

    I don't use VB.net for this. I build user GUI with Access' own tools and would therefore build a form/subform/subsubform arrangement which will automate saving keys to dependent records.

    However, if you want 'lookup' tables of words and priorities to restrict user choices, then those tables would need records first or create them 'on the fly' during data entry of parent/child related records.

    I am not sure why you need tblKeyword_Priority - surely a word can have only 1 priority value assignment related to a specific Entry? Provide an example of data. What is an 'Entry'?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    188
    Hi

    Your 3 tables would have the following structure:-

    tblEntries
    -EntryID - PK - Autonumber
    -Entry

    tblEntryKeywords
    -EntryKeywordID - PK - Autonumber
    -EntryID - FK - Number (Linked to EntryID PK from tblEntries)
    -Keyword

    tblKeywordPriority
    -KeywordPriorityID - PK - Autonumber
    -EntryKeywordID - FK - Number (Linked to EntryKeywordID - PK from tblEntryKeywords)
    -Keyword_Priority

    You would then create a Main form based on tblEntry and 2 subforms for tblEntryKeywords & tblKeywordPriority



  4. #4
    Mynotoar is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    5
    Hi, thanks for the reply!

    "and would therefore build a form/subform/subsubform arrangement which will automate saving keys to dependent records."

    That makes intuitive sense. This dictionary file that I'm reading has 180,000+ entries however, and each entry contains a lot of data, so manually entering them is neither practical nor feasible. Which is why I'm using VB.NET - I've stored each entry as a Class object, which I want to then write a subroutine to read all of the elements in the Class object, and insert into the database.

    Is it possible to do inserts using SQL commands which mimic the behaviour of forms and subforms, and automate saving keys for dependent records?

    "
    or create them 'on the fly' during data entry of parent/child related records."

    I'm guessing this is what you mean. Is this something you can do using SQL?

    "
    I am not sure why you need tblKeyword_Priority - surely a word can have only 1 priority value assignment related to a specific Entry? Provide an example of data. What is an 'Entry'?"

    Sure. So it's not my data, it's sourced from Jim Breen's JMdict project, which is a Japanese dictionary written in XML. Each "entry" contains a bunch of data about a Japanese word. The "Keyword_Priority" field just indicates which frequency list the word belongs to - and there are a few different frequency lists. E.g. the word "明白" (meaning "obvious") has the following Keyword Priority assignments: ichi1, news1, nf10, and each assignment means a different list of words which the entry appears in. Most of the fields for each entry can have multiple assignments, hence why the DB structure is so complicated.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,352
    Yes, code can save records to Access tables but I doubt SQL alone can accomplish. I have used VBA to import data and manage relational integrity of dependent records. It's not simple code. More complicated if you want to assign autonumber primary keys. I have done with and without autonumber primary keys.

    So data in XML file? Access can import XML. If nodes are properly structured, import wizard will direct records to appropriate tables, sometimes a transform file is needed. Otherwise lots of VBA code to accomplish.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-25-2013, 08:35 AM
  2. Entering Data to update all tables.
    By mike02 in forum Forms
    Replies: 19
    Last Post: 06-06-2013, 09:06 AM
  3. Replies: 3
    Last Post: 10-01-2012, 04:43 PM
  4. Replies: 2
    Last Post: 11-02-2011, 08:10 PM
  5. Entering data in forms with lookup tables
    By gretsch in forum Forms
    Replies: 1
    Last Post: 11-16-2010, 11:44 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums