Yo

tstoneh

Board Regular
Joined
Feb 2, 2004
Messages
126
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
mdmilner said:
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
 
Upvote 0
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
 
Upvote 0
tstoneh said:
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top