Results 1 to 5 of 5
  1. #1
    Gambit17 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    26

    How to manage the manage the input of data?


    Hi, not sure if this is the correct place to be posting this,but I could use some help.

    My database receives input from probable 2 or 3 users, using different computers. They input their data from hard copy records. What would be the best way to reconcile their info so that the database as all the info imputed.

    Thanks for the help.

    gambit17

  2. #2
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Are you asking how to have different users put data into the same database? Or are you asking how to make sure that all the data got in?

    For the first, typically there's a back end that stores the data, and a front end that has the forms for inputting the data. You can google "splitting" a database to get information on FE/BE structure. Normally, you can build the whole database and test it in a single file,. then split it FE/BE immediately before deploying. Or, if your two users won't be using it at the same time much, you could just leave it together.

    For the second, that's a science in itself. Double entry, spot checking, check sums, lots of other strategies. Depends entirely on contextual information.

  3. #3
    Gambit17 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    26
    Hi,

    Thanks for your reply, I am relatively inexperienced when it comes to access. I am going to have users in different areas inputting data into a database. I would then like to merge these two databases, or at the very least just merge the two tables together. I need something a little bit simpler than splitting, my users are not very computer literate.

    If you have any ideas that would be great.

    Thanks

  4. #4
    Gambit17 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    26
    Yeah in fact, I think merging two tables together would probably be the easiest. So there would be two different copies of the database. Each with the same table that i would like to merge and then delete the one copy of the database. The 2 tables would have overlapping autonumbers, but it really doesnt matter if these are reset in the one table.

    How could i do this thank you.

  5. #5
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742

    General Comments about Methods

    What's the structure of the data? How many linked tables are involved? If the data is only going into one table, then the issue is probably trivial, whichever way you do it.

    Create your basic database. Back it up. Clone the database structure, with no data, with different names. Distribute one clone to each person/team that will be entering data. These would be DEDs (data entry databases) rather than FEs.

    Are you on a LAN? Can you put the input databases in one place, and have a separate database that can "reach" both of the input databases through the LAN? Create a VBA import routine in the main database that will link up to the data entry databases, copy the data to the main database, then delete the data from the DED.

    If you are not on a LAN, then create an export routine for the data entry databases that will export the data into an excel spreadsheet, then an import routine that will import it into the main database and assign new autonumbers. You could also create a routine that would autmatically email the tables to you for import. There are lots of easy ways to make this happen.

    By the way, if I were you, I'd build either one or two fields into the main database that identify the database that was the source, and the record's autonumber for that database. Proper normal form says make it two fields, typical usage says concatenate a 2-3 char text field in front of a 6-digit numeric field that captures the original autonumber, and store it in a single text field. If you won't be using the two fields independently, there's no reason to store them independently.

    If you were going to track the original autonumbers, then I would suggest that, after the main database imports the data and deletes all the records from the DED, it should use VBA to add one dummy record back to the DED, remembering the dummy's autokey, then compact and repair the DED, then delete the record. That way, the DED stays small, but does not restart autonumbering from 1. If you were a real geek, you could delete all but the last record, rather than deleting all before the compact and repair, but Access doesn't guarantee the autonumbers will be sequential anyway, so it's extra fiddly work for no real gain.

    Oh, also, is this a one-time thing or will it be going on all the time? If it's a one-time thing, with a fixed end date, then just build your database, distribute to both data entry groups, and when you get both completed databases back, copy them both to backup -- always always always make a backup before you alter a working database -- then use one database to import the data from the other and to append to the first. Again, the simpler the database, the more likely you can achieve your results manually. The more different tables and linkages that aren't to fixed-values in other tables, the more likely you'll need a programmatic solution.

    Now that I've finished saying all that, it is just as simple once you get your database working, to back it up, use the autosplitter to create the FE/BE and then distribute the FE to the data entry people. You'll need to relink them to the backend a single time when they get on the desired machines, and then you're done.

    If you plan to be modifying and maintaining the database across time, I recommend building it once, backing it up, breaking it into FE/BE, and using Rob Larson's autoupdater facility to distribute the FE to simplify your life. It can be found here http://www.btabdevelopment.com/ts/freetools. It might seem a little intimidating at first, but it's very well documented. Just walk through the steps one at a time and it works.

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

Similar Threads

  1. Manage Access 2003 ULS thru SQL or Code
    By evander in forum Security
    Replies: 3
    Last Post: 01-27-2012, 06:37 PM
  2. how to manage left employees
    By MissVinegar in forum Access
    Replies: 3
    Last Post: 01-13-2012, 11:31 PM
  3. How to Manage this type of Data?
    By cap.zadi in forum Database Design
    Replies: 31
    Last Post: 12-20-2011, 11:13 AM
  4. Manage attachments
    By accessnewb in forum Programming
    Replies: 6
    Last Post: 08-19-2011, 11:52 AM
  5. splitting tables to manage their size
    By aat in forum Database Design
    Replies: 5
    Last Post: 09-22-2010, 12:47 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