Topics Search

Consolidate Tables Into One File.

Consolidate Tables Into One File.
Views: 2
The situation:
The company that I work for has developed five databases over the last ten years.
All of the databases are split, with the backends residing in the same folder on a network drive.
Some of the backend files are .mdb, others are .accdb.

There is a frontend for each backend, but each of the frontends has links to more than one backends.

Would there be any advantage to consolidating all of the tables into one backend file? Any downside to doing the consolidation?
Sponsored Links:

More topics


Consolidate with a UnionALL query

I have many tables that I consolidate with a UnionALL query. One common field in every table is [YEAR]
What I would like to do is filter in the main union query all records that are 2011 or 2012 only.

This is what I have:

Consolidate multiple columns into one

I would like to consolidate all the values in multiple columns into one long column

amalgamating tables

I have a number of identically structured tables of data that I want to consolidate into one large table and I also need to include in the amalgamated file the name of the table that the data came from.

As an example, two tables of data. The first table is "tbl ONE" and the second "tbl TWO". Each table has a Field called [Data]. I want to create a new table that amalgamates the [Data] field info from both tables but also creates a new Field, [SourceTable}, that records which table the data originated. So structure of the Amalgamated table is [Data], [SourceTable].

Duplicate data fields

I've a badly designed database that I need to improve. Right now, I'm trying to consolidate duplicate data fields.

I have the same field in 2 tables. Each table has data in the field, but the data in each table is for different dates. In the main table, the field was used through 2001, and then someone created a new table for this field. They didn't remove the old field, or move the data to the new field. The old dates only exist in the old table, the newer dates are in both tables, but only have data for this field in the newer table.

Is there a simple way to consolidate the data from the two fields? I want all the data to be in the new table.

Use Select Case to Consolidate Switch Function

I need to consolidate 5 fields stored in a table into 29 different categories (eventually to be used in a cross-tab query to generate counts of each category). I'm limited in using switch and/ or iif statements because of the number of nested statements I would need.

Is there a way to do this using by creating a function and using select case? I'm not sure how to reference multiple fields in a table.
Here is an example of what I was originally thinking of...

Consolidate duplicate rows with one varying column

I have multiple tables that contains the same 3 column headings , Site, Part, and Customer. I can't do this manually as the list is in the thousands and exceeds Excel capacity.see below for what I would like to happen

Consolidate MDBs

Over a period of time I developed several stand alone applications
I would now like to combine them into one overall application.

Can I set up the main form to select the application you need to work with.
Set the relationships to all the tables in all applications .

For instance in app A request a report from app B with out going back to the main form and running app B.

If this can be done is there a limit on the number of tables that an app can have

Calling Macro when Form is opened

The desire is to call the Macro "Consolidate" from the Module "AutoConsolidate" at the time the form is opened.

So within Form_Open I have:

DoCmd.OpenModule ("AutoConsolidate")
DoCmd.RunMacro ("Consolidate")

I get:
Run-time error '2485':
Microsoft Office Access cannot find the macro 'Consolidated'

Unique ID across all tables

I have a database that tracks objects in our company (i.e. Cell Phones, Computers, Servers, Software, Warranties, etc). In total we have 33 different objects we track. But now I need to have a unique ID across all objects. I thought about making a tblObjectIDs with one column, ObjectID, and then making an ObjectID column in all the other tables. The problem is that there is a 32 relationship limit per table and I've already exceeded that before I've even gotten started and who knows how many more tables I'll need to add in the future.

My other idea was to consolidate all tables into one and just having a column that specifies what type of Object it is. The problem is that there is such a wide variety of properties each table has that I'll exceed the limit of how many columns you can have in a table.

What's the best design approach for this situation?

Create new database file and transfer tables using VBA

I have a database with lookup tables as well as data entry tables. I'd like to have a button which allows the user to start a new database file with the lookup tables containing the data, but with empty data entry tables.
Essentially, I want to be able to save the data from the original database and start a new database (file) with all of the lookup tables intact, but a clean set of data entry tables.