Results 1 to 10 of 10
  1. #1
    briar is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    9

    Help with Basic Table Structure

    Thank you for help! Beginner here.

    Can't get past basic table structure. To follow rule of non-repeating data, I try breaking things down to smallest data in tables. Example: table "Make" (Ford, Chevy, GM) and another table "Model" (Mustang, F150, Tahoe) - relationship should be one-to-many: one- "Make" to many- "Model" (Ford Mustang, Ford F150, Chevy Tahoe). Another table, "Inventory" has vehicle specifics "Year", "VIN", etc. On "Inventory" table, I want to pull in the descriptors from "Make" and "Model". Even Table Analyzer doesn't like what I'm doing. Wizards says, "some records found with very similar values" and suggests I match the Ford with the Chevy??? Should I pull "Make" to "Model", somehow combine data and then pull to "Inventory". Also, would love to add "Color" and "Style", etc. but that confuses things even more.

    Desired result would be "Inventory" form to have drop-down boxes for "Make", "Model", "Color", "Style", etc. for ease in data entry. Also ability to run report not just on each unique vehicle in "Inventory" table, "1965 white Shelby Mustang car", but also reports for "all Fords", or "all green Fords", or "all green Chevy Tahoe". Can't find the "rule" that explains best way to separate these tables to to non-repeating data and then linking together through relationships. Better to use Lookup Fields only on the "Inventory" table? Better to use composite keys? It can't be this hard - I've got to be missing something? Thanks again!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works."

    One approach would be to have field in Model to hold foreign key of MakeID. Then Inventory would have foreign key field for ModelID along with fields for year, style, etc.

    I avoid composite keys. Only used once.

    I never build lookup fields in table. Use combobox on form.
    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
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Hi briar

    See if the simple db attached helps you to understand. Please ask if you have any questions
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    briar is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    9
    Thank you both....and loved the quote!! Going nuts with simplest decisions! Will dig in!

  5. #5
    briar is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    9
    Hi Bob,

    Thanks again for the file and help!! Let's see how to put this...I see how your file works and can replace with my info one record at a time, but I am trying to start new file from pulling large table in Excel so I don't have to manually enter data. What is best approach to getting all my data into a similar structure to yours? When I run the Table Analyzer on imported Excel table and build tables similar to yours, afterward, I want ability to rename primary keys (ItemID) and foreign keys to (MakeIDFK), etc. so they make sense. That's where the problems start occurring. Is it better to create tables first with no data, or build from existing Excel table pulled into Access and then separate out specific fields into new tables? Maybe with just a few entries to make sure it works, and then append data on main table? Frustrating myself because it's easy to copy your file, but want to also understand what is going on. Love Access concept but this one step is such a mental block...argh!

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Review thread with similar issue https://www.accessforums.net/showthread.php?t=80592
    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.

  7. #7
    briar is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    9
    Thank you June7! Looking forward to exploring this link

  8. #8
    briar is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    9
    Hi June7,

    OK, waving white flag. Attaching my real file. Trying to track some old change I found and created Excel spreadsheet from data off internet. My logical self says that CoinType and Coin should be separate tables. I purposefully changed info on rows 5-8 coin and type to "see" if my db was working correctly. I can complete a make table query, but can't get past that for separating out these 2 columns, yet retaining their relationship to existing data. I will be appending more rows later, like wheat pennies, memorial pennies, etc. Can't thank you enough for looking at this! Steps on your solution would be most appreciated!

    TestChange.zip

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not necessarily.

    You could have a 'lookup' table of coins that would serve as a source for comboboxes on form for selecting coin for new records in Original.

    tblCoins
    CoinID
    CoinName
    CoinType

    Then a field in Original for saving only CoinID as foreign key.

    Could also have a tblCoinTypes to serve as lookup source for CoinType field in tblCoins.

    Neither of these lookup tables is critical to your db structure. CoinType and Coin fields could actually serve as sources for comboboxes. But if you do want to do more strict normalization then use lookup tables and save ID's.
    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.

  10. #10
    briar is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    9
    Thank you June 7.....will try this route. The table design is my nemesis

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

Similar Threads

  1. Replies: 3
    Last Post: 10-12-2016, 06:57 AM
  2. Replies: 7
    Last Post: 09-11-2014, 11:48 AM
  3. Table Structure
    By ccordner in forum Database Design
    Replies: 22
    Last Post: 01-17-2012, 03:22 PM
  4. Help with Table Structure
    By medtech2 in forum Database Design
    Replies: 5
    Last Post: 10-14-2011, 05:43 PM
  5. Table Structure
    By riley73 in forum Database Design
    Replies: 5
    Last Post: 05-03-2011, 07:13 AM

Posting Permissions

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