Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    135

    Table for Locations over time


    Hi all
    Next question (does he ever stop)?

    My data ranges over 200 years, and in that time locations have change Name, States and Countries. Most DB seems to use City, State, Country, but then again boundaries donít move very often.

    Again I see ways of storing this:
    1) Use 1 Table and have To From dates and a link to the next name.
    2) Use 2 tables (a Geo Table (Locations donít move (much)) and a Political Table (Man named things do)).

    The first one can return false data if the code doesnít find the real bottom. The second one feels wrong somehow .
    Comments??
    Neil

  2. #2
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    Hello, Western Neil. Southwestern Neil here. :-)

    You didn't really specify how the data is used or what the data is. But, in any case, based on what you wrote, what I would do is have a separate location table, with a start and end date for each location (end date for the current location would be blank), and then specify the location there, linking it to your main entity on the entity's ID field, which you would store as a foreign key in your location table.

    Then, if you want to get the current location for an entity, you get the location with the most recent start date and use that. If you need to go back historically to a past date, then you'd find the location for which the date is between that location's start date and end date.

    I don't know what you mean by "a link to the next name," but yeah, that sounds wrong, whatever it is.... ;-)

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,942
    I agree with nrgins
    - if location names change you need some mechanism to record same
    - you haven't told us much of your overall requirement. We seem to be adding pieces...

    Can you step back, and tell us in plain English --what is the subject involved? What is your goal?

  4. #4
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    135
    Sorry about that guys. I got focused on what I wanted, and forgot to bring you guys along. I do this alot , so anyways

    I’m new to MS Access (I have it, but I’m not set on it), but have worked with Relational Data Managed Structures and written COBOL programs using it.

    I’m trying to create a database to track Lineages and Order of Battle (OoB) of military units in the 20th century with an emphasis on Canada and WW2. Much of the data is date based but may not have a compete date (i.e. early Dec 1941) or even a fixed start. The DB will track the Name, Size, Branch, Superior, Level, Location, etc., and the date. Each of these attributes can change at different dates but may not exist at any point in time. With examples I can explain how this all works but it’s not worth while until someone says “sure I will Help”!

    My flat file is about 12,000 records, but it is NOT totally normalized. I don’t want to change the flat file structure until I have a DB structure that will fit and handle the data quirks as I know it. The commercial aspect of the project is very low and it is being self-funded. All credit and acknowledgment will be shared in proportion to the help received. I am willing to discuss this projects’ data structure in this forum or in private as the coach wishes.
    So the Question is …

    Can someone coach me in designing the DB structure and get me started on this

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,942
    Neil,

    Here are 2 tutorials form RogersAccessLibrary.
    1) Class information

    2) Widgets

    You have to work through (30-60 minutes) them, but you'll learn how to design a database.

    You should also watch videos 1,2 and 4 from this series


    Get a clear description of what you are trying to do. It's often more difficult than you think, but it will help you and it will help in communicating with others.

    Good luck.

  6. #6
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    I don't know about orange, but I just meant like a little more information about how the locations were going to be used, not the whole ball of wax. :-)

    In any case, did my response about how to set up the locations table solve this for you?

  7. #7
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    135
    Sorry about that, that pre-written response got away from me but it does say what this about.

    Getting back to this threads' question (their I go again),
    Some examples of places changing names:
    1) Fort Macleod has changed its name to Macleod and back again (offical I think once).
    2) Kitchener, Ontario was called Berlin before WW1.
    3) Alot of Quebec places restored their french names in the 60-70's.

    Boundaries have also moved as Alberta was part of the NWT and was in a couple of Districts as Canada grew. The Military Districts boundaries have also changed over time, and have been dropped from usage. This means the DB should not return info out time.

    All these are small bits now, but if I want to historical correct I need to account for them. No good talking about the Newfoundlanders from Canada in 1916 or even 1944.

    Neil

  8. #8
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    135
    I know what I want Orange, but some tabby has played with my ball of string.

    I have found it hard scoping this thing out, I keeping finding rabbit holes to go down and all getting lost in the details.

    Will try harder Neil

  9. #9
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    So that's a "yes," the Locations table situation is resolved?

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,942
    Look at the tutorials and get a sense of the level of info required. Knowing what you are trying to do is more than half the "battle" --sorry for the pun.

  11. #11
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    135
    Sorry SW Neil but its a hold. I'm unsure of how this will work, and Orange has given me some homework to do . I was hoping for a canned answer but it looks like I may have to do some custom work.

    I shall return, Neil

  12. #12
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    OK, cool. FWIW I find that when developing databases, it often helps to just model data in different ways with different table designs. Sometimes you have to play around with it a little until you get it right. That, of course, implies that you understand the basics of relational table design. So it's a combination of knowledge and just getting your hands dirty and playing with it.

    One day we'll be able to say, "Cortana: design a database for me where...." But we're not there yet. ;-)

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,942
    Further to nrgins comment, here is a post I made in response to a similar situation on a different forum. See the stump the model link for more info.

    Good luck.

  14. #14
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    135
    I get invalid link, but the forum does show up
    Neil

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,942
    Below is a copy/paste of the link I pointed you to.

    I added some links in my previous post re Relations/relationships.

    If you are new to database and Access, then working through on of the tutorials at RogersAccessLibrary may be extremely helpful to you. They start with a problem description; lead you through a procedure to identify entities and attributes (tables and fields) and relationships; to produce a data model (blueprint) for your database that supports your requirements. Each tutorial is similar and takes about 45 minutes to 1 hour to complete. You have to do work through the tutorial, but what you learn can be used with any database and/or dbms.

    Here are a few tutorials:

    Class information database
    Catering Business

    You may also want to review Normalization. This is the first of a series of related articles.

    Also, you will find that using a naming convention that does NOT allow embedded spaces in field and object names will save you many syntax errors as you progress.
    Better to use alphanumeric and the underscore(_) character only. And do not use a number as the initial character in a name.

    Here is a link to "stump the model" post that will help you test your model before getting too deeply into trial and error mode.

    Good luck with the project.
    __________________
    DebuggingTips
    ErrorHandlingTips

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sales Locations and Sales to Locations
    By Dinger045 in forum Access
    Replies: 1
    Last Post: 04-19-2016, 12:41 PM
  2. Replies: 16
    Last Post: 10-12-2014, 01:27 PM
  3. Replies: 4
    Last Post: 01-23-2014, 03:48 PM
  4. Trusted locations
    By kcmiuser in forum Security
    Replies: 1
    Last Post: 08-14-2013, 09:48 AM
  5. Replies: 0
    Last Post: 01-26-2011, 05:51 PM

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 - Senior Forums