Share via

Best way to get data into Access

Anonymous
2022-07-09T19:28:35+00:00

I work for a real estate office and have been tasked with getting our data into an Access database.

The files are set up like this;

  • There is a folder called "Contracts" that contains folders by the type of contract, (lease, land, sale)
  • Within each folder of "Type", there are folders organized by area where the real estate is located
  • In the "Area" folders, each piece of real estate is in a folder alphabetically by address
  • Within the "Address" folders are all of the documents pertaining to that property

My question is how on earth do I go about getting this data into an Access database?? Any input will be greatly appreciated!

Thank you,

Donna

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2022-07-09T22:42:42+00:00

    You appear to have a hierarchical structure, so your tables would be related by a series of one-to-many relationship types like this:

    ContractTypes---<Areas---<Properties----<Documents

    The Documents table could, in addition to columns (fields) representing the attributes of the documents (description, date etc.), also contain a text column containing the paths to the location of the document files, which could be Word files, image files etc.  It would then be a relatively simple task to open each document in its associated application from within the database.

    For the user interface the most likely set-up would be a Properties form, in single form view, within which is embedded a Documents subform in continuous forms view, linked to the parent form on DocumentID or similar.

    If you are relatively new to Access you might like to take a look at DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file, as its name suggests, provides an introduction to some of the basic principles and methodologies used in developing a relational database in Access.

    1 person found this answer helpful.
    0 comments

11 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,810 Reputation points Volunteer Moderator
    2022-07-11T16:23:20+00:00

    I would look at the rtepokies from Ken and myself before folowing the other suggestion.

    0 comments
  2. Anonymous
    2022-07-11T14:53:45+00:00

    Thank you so much! I will give it a try.

    Donna

    0 comments
  3. ScottGem 68,810 Reputation points Volunteer Moderator
    2022-07-11T02:25:49+00:00

    Hi Donna, If I follow youyou have documents in folders that represent properties you handle. You also have other folders with documents pertaining to contracts.

    So my question to you is what do you want to put in your Access database? Do you wan t to just put links to the documents? Or do you want to input data pertaining to the properties. How you get the data into Access depends on what data you want to see. So please be more clear so we can give specific solutions.

    0 comments
  4. Anonymous
    2022-07-09T20:35:37+00:00

    Hi DHL,

    I'm Abraham and I'd be happy to help you with your question.

    You need to set a unique identifier for each table you will be creating in the database. This will enable you to query the database much easier if you are fetching data from multiple tables.

    example: you need to insert all the files in a different table

    1. INSERT INTO contracts (id, refID, column1, column2, column3, ...)
      VALUES (value1, value2, value, ...)

    where the refID is the unique identifier for each table

    I hope this information helps. If you have any questions, please let me know and I'll be glad to assist you further.

    Best Regards,

    Abraham

    0 comments