Results 1 to 2 of 2

Database Design - Consolidation

  1. #1
    Pierro1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    1

    Question Database Design - Consolidation

    Hello everyone,
    I am a finance professional and an absolute beginner with database and access. :-)
    I would please need your help regarding my project: I am trying to consolidate data from multiple entities in my group around the world, and then make dashboards on Power Bi out of it. This database needs to be user-restricted, meaning for instance User A in Australia will be able to only see the Australian data in the database while entering/updating it. As data needs to be updated regularly, I would like to avoid any copy paste as possible and would like the user to update directly the main database. Hence will the dashboards on PowerBI automatically be updated in real time without my intervention.
    We have office 365. I hope my explanations are clear enough
    Thank you very much for your help
    Pierro

  2. #2
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,547
    you haven't asked a specific question so cannot give a specific answer.

    an absolute beginner with database and access
    before you start read up on and understand the principles of normalisation and database relationships. As a finance professional you are probably skilled in excel. Access works in a completely different way to Excel. for example Excel tends to have few tables 'wide and short'. Access has many tables 'tall and narrow'. Excel combines data and presentation into one view, Access stores data in tables whilst presentation is handled in forms and reports via queries. If you try to apply excel logic to access you will ultimately fail.

    Some basic rules -
    do not have spaces or non alphanumeric characters (#$/ etc) in table or field names https://bytes.com/topic/access/answe...es-field-names
    avoid using reserved words https://support.office.com/en-gb/art...9-f855bdd9c5a2
    name your fields in the context of the whole application, not just the table e.g. ID? ID of what? customer? product? nominal code?
    don't use lookups and multi select fields in your tables - they look useful but will cause you grief in the long term - http://access.mvps.org/access/lookupfields.htm
    split your database - tables go in the BE (back end) and the BE is located in a folder which all users can access. queries, forms, reports, modules all go in the FE (Front End) a copy of which is located on each users local machine. Not splitting or splitting and allowing users to share the FE will lead to corruption. https://support.office.com/en-gb/art...3-51b1d73498cc

    This database needs to be user-restricted, meaning for instance User A in Australia will be able to only see the Australian data in the database
    you will need a login process and a table associating the logged in person with the entity

    the login process might be based on their windows login, or you need a specific login name and password

    once done, the tables will need to be associated with the entity so that they can only see their data.



    would like the user to update directly the main database
    if users are all over the world they will all need access to the server and folder with the back end. Note that onedrive is not a suitable location. However users who are on a different continent from the server location may experience performance issues. In which case investigate using citrix or remote desktop.

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

Similar Threads

  1. Replies: 9
    Last Post: 06-23-2018, 10:25 PM
  2. Consolidation of Data
    By Ramtrap in forum Access
    Replies: 2
    Last Post: 12-19-2016, 07:16 AM
  3. Consolidation codes from different table.
    By suverman in forum Queries
    Replies: 3
    Last Post: 05-13-2011, 10:39 AM
  4. Need help with code logic/consolidation
    By bg18461 in forum Programming
    Replies: 1
    Last Post: 03-31-2010, 04:19 PM
  5. Consolidation DB
    By dimitrz in forum Database Design
    Replies: 7
    Last Post: 03-23-2010, 12:44 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
  •  
Tech Forums: Microsoft Office Forums