Results 1 to 6 of 6
  1. #1
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85

    Combining Data from 3 locations into one database

    I've been given the task of combining data from three locations into one database in Access, something that is relatively new to me.



    I'm trying to decide whether to have the locations enter data into an Access database or into an Excel spreadsheet at each location. Regardless of the program used to enter data, I would probably like to link each of the files to my main Access file and combine the three into one database there. I do not want the locations to add data directly to the main database if possible.

    My question is, would this be done by an append query? If so, how? Also, my thought is that the data at each location would be added to every day. I would need to be able to add the NEW data only to the database without duplicating the previous day's data. How would I go about this? Any suggestions or starting pointers are appreciated.

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    I would first decide which program is best suited to your needs, as both can link to other data sources. If you will be reporting and querying off this data, and will be structuring them into normalized, relational tables, then go with Access. If you are looking merely to combine them into one place, stick with Excel.

    If you do decide to go the Access route, What I would do is totally cut the users off from entering data into excel and linking to it. You would do one import of the data, structure it as you wish, and then create a form interface for them to use. Once that is done, you can split the database into a back end that will stay on a network drive that everyone can access, and a front end which every user will receive a copy of for their desktop. Users will not be able to alter the data in your tables unless you give them explicit permission to do so via form controls.

  3. #3
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85
    I'm thinking the best way is to go with Access rather than Excel. However, I'm new to this part of the game and all the backend - frontend talk is clear as mud to me. I'm a very visual person. Any suggestions as to tutorials that will help me sort that out?

  4. #4
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85
    One thing I do know is that I want people doing the data entry to see as little as possible of the other side of my database. The simpler for them, the better.

  5. #5
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    This is a good place to start. You can create an environment where the user will see nothing but the forms you created. To them, it will be just like you created a new piece of software (essentially, you did). They don't even need to be aware that a backend exists.

  6. #6
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85
    Actually, I found a link on the MS Office website called "Adding rows by using an append query" that solved my issues completely. There's a section that talks specifically about combining 3 tables into one! It was fine for my simple needs.

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

Similar Threads

  1. Replies: 2
    Last Post: 05-04-2011, 09:54 AM
  2. Replies: 0
    Last Post: 01-26-2011, 05:51 PM
  3. Replies: 0
    Last Post: 07-09-2010, 06:22 AM
  4. Deploying Access db to different locations
    By lupis in forum Security
    Replies: 7
    Last Post: 06-01-2010, 05:00 AM
  5. Combining textbox and checkbox data
    By jgarner in forum Access
    Replies: 0
    Last Post: 12-11-2008, 11:10 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