Results 1 to 1 of 1
  1. #1
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147

    Location Names over Time

    Attached (PDF => ERD, Zipped ExcelBook => data tables) files are a small part of the DB that I seem to be forever designing.



    Like at lot of my posts I think I have a workable solution (this time! ‘again’). My aim from this chunk is to teach myself Access Queries, Forms and Reports, since this part of the project. As most of you know I understand ERD’s, VBA, and Excel, what I don’t have full understanding of Access.

    A general overview of this chunk is:

    1. The 7 tables are designed for a given date and location, give the name and regional Titles (Toronto, ON or back in time York, UC). The earliest records (1800’s) will be for small places and the latter ones will be for larger ones (1950’s) with most of the smaller not being used.
    2. A4-tblUnitLocations.UnitFK links to 6 other tables and the whole DB will be over 50 tables (so far).
    3. Table numbering is “L” (for location), # (level with 1 = no dependents), Table Name. The L#- is a crutch so I can find the table in Access and load them from Excel without tripping over relation integrity. It may go way at some point.
    4. Notice that the data tables are a work in progress, and should give solid idea of the data


    My main question “is the Data Structure correct and workable in Access?”, given my beginner status. In some ways I’m just checking to make sure I’m not creating grief for myself. Some of my more specific questions are:

    1. Every time I look at this project, the ERD changes because I think of a "better" way or have missed something. Is this Normal? When do you stop?
    2. The whole DB is handling Temporal (dated) data. I am using a start Date and assuming the end is the next record (sorted by date) or Now. I have not designed a method of ending the thread or passing the heritage on to another record. (Our big cities have swallowed many smaller places, which in the past where truly places of note.) Any thoughts, ideas, or methods?
    3. In table in L1-tblRegions is RegionID, which is only required field. The other field (RegionNote) is there to help the humans understand. Should this field be dropped later? Is this okay?
    4. Notice that RegionCapitalFK is linked to LocationID which links back though tblPlaceNames and tblRegions. I have not designed a stop method, expect by using a null field, which is not part of my design philosophy. This almost makes tblRegions a MUCK table. The maximum depth I see is about 6 calls, but it could be more if small areas are done. Any suggestions?
    5. F1-tblAccuracy needs 1 or 2 fields added (+ - days) to handle Fuzz dates. Any suggestions?


    I have asked these questions before, but not with this ERD or Data. They are in the following threads:

    1. Table for Locations over time at https://www.accessforums.net/showthread.php?t=60730
    2. Should I change DB engine? at https://www.accessforums.net/showthread.php?t=75397
    3. Single Field Table Design at https://www.accessforums.net/showthread.php?t=70426
    4. Access SQL for Recursion at https://www.accessforums.net/showthread.php?t=75300
    5. Fuzzy Dates at https://www.accessforums.net/showthread.php?t=76530


    Thank you for looking and thinking about this
    Western Neil
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 11
    Last Post: 02-25-2019, 02:09 PM
  2. Replies: 1
    Last Post: 02-08-2019, 03:09 AM
  3. Replies: 4
    Last Post: 11-29-2017, 10:01 AM
  4. Replies: 4
    Last Post: 08-31-2016, 12:47 PM
  5. Time/Date stamp Location
    By Western_Neil in forum Database Design
    Replies: 6
    Last Post: 07-10-2016, 09:29 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
  •  
Other Forums: Microsoft Office Forums