Related Topics

Consolidate Tables Into One File.

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

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.

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].

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

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'

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

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.

Macro to paste data from Access Tables to Ms Excel
Please find attached sample MS Acces db and Ms excel file.

I need the data from the 3 Access tables to be populated across to the three tables as shown in the MS Excel file.

Assume the file path of the excel file is: C:\Documents and Settings\All Users\Documents\microsoft\Sample_File.xls

so the table Sample_Data_1 gets populated across to MS Excel worksheet Records_1 C11:F14 and so on.

In the original file I have a sub form which runs the macro.

Split your Access database into data and application
Even if all your data is in Access itself, consider using linked tables. Store all the data tables in one MDB or ACCDB file - the data file - and the remaining objects (queries, forms, reports, macros, and modules) in a second MDB - the application file. In multi-user situations, each user receives a local copy of the application file, linked to the tables in the single remote data file.

Feature missing: Create database with linked tables having relative paths.
I have looked and looked and tried and played with lots of VBA scripts, but I am still frustrated. Clearly, this feature is needed and does not exist. This was the best place I could go to,

In MS access, you can create a accdb file having tables that are always importing their data from another accdb file. These are called linked tables. The problem is that the linked tables will link themselves to the full path (starting from C:\.\.).

There needs to be a way to store only the short path (which is [current dir]/file.accdb). This is relative to the databaselocation regardless of wher you store the file.

Import Excel to Access

I have two access files. First file keeps only the Code and second file contains all the tables and data.

I want to import the data from excel sheet into access (Second file which contains all the tables) using VBA. Below code is written in first file and data need to be imported in second file. What is the syntax I need to use for this?

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, sTable, "c:\Options.xls", True

Output tables to an excel file on a network drive
I am currently using a macro to output tables to an excel file on a network drive.
This all works great but I was wondering whether I could use VBA to do this but incorporate a date into the file.
I.e. currently the file goes to the drive and sits there but next time it is run it tries to overwrite. So I was wondering if incorporating a date into the extract (File name which includes date of extraction) could be done?

Complex Form Creation Question
I am trying to create a consolidated database from 50+ Excel spreadsheets, with the following needs.

1. Can be easily updated on a periodic basis by an individual unfamiliar with advanced Access or serious code.
2. Can upload from spreadsheets with different formatting (I want to sidestep the need to homogenize formatting in each excel file). Formatting may vary in either spacing or theDate system used (I want to consolidate to a 28 day accounting schedule). Some are monthly, others use 28 day periods.
3. Create one unified database that can be examined via pivot-tables or queries.

but I’m not sure that Access is capable.

1. I was hoping to create two separate forms for user-friendly upload of excel information—one for the monthly spreadsheets, and one for the 28 day periodic spreadsheets.
2. In these forms, I would be able to input the spreadsheet location and column lookup numbers corresponding to various columns (each marking a 28 day period) in a consolidated Access table.
3. There could also be inputs for different values that would correspond to every record of a given upload.

how to activate a macro file in another access file
I have divided my access file into 2 components. one is the front end and the other is the backend which contains tables only. Now the problem I have is I run a macro which loads the data from an excel sheet into the tables. I cant run the macro on the front end since they are all linked tables. I need to run the macro on the backend file which will load data. How do I activate that macro in the backend file from the front end file. how do I mention it? macro "what do I give here

Populate primary key into imported tables
I've recently designed a fillable PDF file that users can type in and send it back as a xml file.

I want to import the xml into Access database so we can keep track of records. Unfortunately, it comes out a number of tables. And only 1 table has a primary key, other tables do not. Is it any way I can populate the primary key into those tables? I could do it manually but it would be tedious. I would like to find a smarter way. It might relate to programming but I have no idea of it

Combining tables and keeping relations
I'm currently working on improving a database that was handed to me and my first thought as I looked through it was to combine a few tables as they should have been from the start. It's proving a bit difficult (for me at least) since the tables I have to combine into one, 4 of them, have a one-to-many relationship to 5 tables that also need to be combined but this time into two tables.

The first four tables that need to be combined into one all have autonumbered primary keys and contain one foreign key. The five tables they are related to have a composite primary key and some form a many-to-many relationship between the first four tables (others extend the many-to-many relationship to a single table which has not be mentioned before).

I have already laid out the new database in a seperate access file as I would like it to turn out so I'm not set on keeping the old database, exporting to a new file as long as I can keep the data and relations is fine.
How can I work with this setup in a manner that keeps my relations right and is relatively quick?

Updating linked tables after conversion
I've recently upgraded a 2003 database to 2007. When I try to update the linked tables using the linked tables manager it shows the tables in the old .mdb file. When I select the new .accdb file and then try to start the update it gives the following error message: "The Microsoft Access Database engine cannot find the input table or query named 'table name'. Make sure it exists and the name is spelled correctly." All of the table names in the link manager show the .mdb file