Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Yo

This is a discussion on Yo within the Microsoft Access forums, part of the Question Forums category; Inexperienced user of Access but I know a few things here and there. Just wondering though. I have seperate copies ...

  1. #1
    Board Regular
    Join Date
    Feb 2004
    Posts
    122

    Default Yo

    Inexperienced user of Access but I know a few things here and there. Just wondering though. I have seperate copies of about five database files that I need to make into only one and then sum them. I am trying to determine the best way to make it happen.

    Any insights would be great.

    Thanks.

  2. #2
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,191

    Default Re: Yo

    My suggestion would be to create a consolidated database, that you copy all the tables for the others to by using cut and paste.(This will create a static copy, if you want it dynamically linked, you need to use a different process) You could then create a query to do your sums based on all the data tables.
    If you do want a dynamic link, create a new database and right click on the table area. You should be presented with a link tables option. Select that and find each of your databases in the file selection. You can link your other database table to the consolidated one using this. Then create your sum query this way.

    Hope this helps.
    Xcelerated Solutions
    Office Automation Solutions for the Toronto Area

  3. #3
    Board Regular
    Join Date
    Apr 2003
    Location
    Jacksonville, FL
    Posts
    1,344

    Default Re: Yo

    The methods are many, but, it all depends on how exactly it's stored currently. Text, XLS, MDB?

    First, put them all in a single Access MDB. Makes it easier to do the rest. If they're in other MDB's, right click and export the tables to the other (unopened) database.

    Once there, make some append queries with the wizard. Assuming they're all identical (same fields, lengths, etc etc)

    Copy one of them to a new table name. Then go to the queries and make a select query for one of the other tables. Then convert it to an append query and select the table you just made above (for the first one). If all the field names match, it'll dynamically assign the correct fields to update.

    Repeat 3 more times.

    Mike

  4. #4
    Board Regular
    Join Date
    Feb 2004
    Posts
    122

    Default Re: Yo

    Sorry guys, these descriptions do not make any sense so I will be more specific. I have a bunch of MDB's that I sent out to employees and they are supposed to fill them out and e-mail them back to me. They are all identical access MDB's all with the exact same format. I am just trying to put them into one "master" MDB and then sum them to come up with one total MDB. Just trying to figure out the best way to do it.

  5. #5
    Board Regular
    Join Date
    Apr 2003
    Location
    Jacksonville, FL
    Posts
    1,344

    Default Re: Yo

    Ok, now that you've provided additional information that clarifies the question...

    We both suggested that you collect all the data tables into a single mdb file (Access is a mdb file - look at the extension at the end of the database name).

    The easiest way that doesn't require coding skills is to use the import wizard on the primary mdb to bring in the data. A *safe* approach is to import the extra tables into new TEMPORARY tables. This gives you a chance to look at each separately should there be issues OR if you need to repeat the import process.

    Second step would be to create SELECT queries on each of these new tables. Access has a button on the toolbar that allows you to convert the SELECT queries quickly into APPEND queries. Append queries write the contents of all records in a table into another table. Aka, this is how you move all the records from the temporary tables into the real primary table in the primary database.

    As information - and a possible shortcut. Access will allow you to import data directly into an existing table and add (not replace) the existing records. You could just use the import wizard and skip the temp table/append query business.

    Personally, if you don't know how to do queries - you should learn. I can't begin to describe what type of capabilities they (SQL queries) allow.

    Lastly, if you wanted to get fancy - a VBA function could be written to automate the import process. Unlimited options here, limited by your skills.

    Mike

  6. #6
    Board Regular
    Join Date
    Feb 2004
    Posts
    122

    Default Re: Yo

    Is it possible to take me through the specific steps?

    Like 1) do this
    2) do this
    3) do this

    This is helpful but I am too much of a rookie with this.

    Thanks

  7. #7
    Board Regular
    Join Date
    Apr 2003
    Location
    Jacksonville, FL
    Posts
    1,344

    Default Re: Yo

    Quote Originally Posted by mdmilner
    The easiest way that doesn't require coding skills is to use the import wizard on the primary mdb to bring in the data. A *safe* approach is to import the extra tables into new TEMPORARY tables. This gives you a chance to look at each separately should there be issues OR if you need to repeat the import process.

    As information - and a possible shortcut. Access will allow you to import data directly into an existing table and add (not replace) the existing records. You could just use the import wizard and skip the temp table/append query business.
    Excerpts from above.
    Just do this - go to the File-Get External Data-Import menu option. This starts the import wizard. When it prompts you which table to import it into, use the same destination table each time.

    The wizard interface handles everything, but you're going to have to read the screen.

    Mike

  8. #8
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,116

    Default Re: Yo

    If your users will be regularly updating and you want to synchronise, there are two approaches that haven't been mentioned here. First combine the data, then:
    1. If all users are on the same network and can access a shared folder, create a split database. The backend goes in the shared folder (you nominate this when splitting the DB) and you give everyone a copy of the front end. Back end is data (tables) -- front end is everything else. This works fine for up to 5-10 users. No need to import data once you're done -- there is only one data set and everyone uses it. Advantages -- Reversible if you need to go back to a sinlge database. Esy to make changes to teh databases logic and distribute the new version without affecting the data. Drawbacks -- with plenty of users in a large database, performance can be slow. You can also end up with record-locking issues if 2 users try to access the same record simultaneously.
    2. If your users are taking their databases on the road and need to synchronise daily or weekly, consider using replication. This results in one central master database, with as many replicas as you require. When users connect back onto the main network, they synchronise their data with the master. Drawbacks -- The process is irreversible. If you want to change your mind later, too bad. Making updated versions of the application side (forms, reports, queries) is likely to be a pain.
    Check the Help and the Knowledge Base for more info on replication.

    Denis

  9. #9
    Board Regular
    Join Date
    Feb 2004
    Posts
    122

    Default Re: Yo

    There are about 9 different tables. Which one do I use or do I use them all.

  10. #10
    Board Regular
    Join Date
    Apr 2003
    Location
    Jacksonville, FL
    Posts
    1,344

    Default Re: Yo

    Quote Originally Posted by tstoneh
    There are about 9 different tables. Which one do I use or do I use them all.
    We've never seen the database. How would we know?

    I'm honestly beginning to question your original assertion that you knew a few things, but were just new to Access. There's only so far a volunteer based help forum can go without actually doing the work for you. And, our answers frequently require at least some knowledge or willingness on your part to figure things out.

    Please start by looking at the tables for your data. If you're not sure what you're looking at, you're probably going to have ask somebody in-house that can actually look at things.

    Mike

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com