Update 150 different table values at once

FreddyKrueger · Oct 21st 2005

Hi all

I have a master table that contains 15000 cases. Lets call it the "Master Table" This master table was split into about 150 smaller tables. ("Table1" to "Table150"). I have a unique field called ("ID")

What I want to do is:

I have created an addition field in my "Master Table" called "Table Name". Now I want to use an update query (or any other means possible) to update this field with the smaller table name (Table1 / Table2 / ....../ Table150) into which the specific case was divided. Is this at all possible?

I can do it table by table but this will take too long.

I'm don't use access a lot so this might sound dumb.
Please help

Regards,

Freddy

**norie** · Oct 21st 2005

Re: Update 150 different table values at once

Freddy

You probably want to use VBA to do this.

Have you already added the records from each table to the master table?

FreddyKrueger · Oct 21st 2005

Re: Update 150 different table values at once

Hi Norie

The master table is basically the sum of all the smaller tables. So yes. Now I only want to indicate in the master table (by adding a "Table name" field) to which smaller table each record belongs.

F

**norie** · Oct 21st 2005

Re: Update 150 different table values at once

Freddy

Have you already added the smaller tables to teh master table?

This is a crucial question. How are you going to identify which record came from which table?

FreddyKrueger · Oct 21st 2005

Re: Update 150 different table values at once

HI Norie

I now understand what you mean. The Master Table has a unique field called "ID". I can use this field to check with entry came from which table.

**norie** · Oct 21st 2005

Re: Update 150 different table values at once

Freddy

Can't you then just use 1 update query?

How can you identify which table the record came from using the ID?

FreddyKrueger · Oct 21st 2005

Re: Update 150 different table values at once

Norie

What I can do is the following:

I link the "Master Table" to "Table 1" (smaller table) using the ID field.

No I run a simple update qeury:

Field: Table name (this is the new field I have created in the Master Table and that I want to update)
Table: Master Table
Update to: "Table 1"

This query writes "Table 1" next to all the cases (that belong to Table 1) in the Master Table.

I can repeat this process 150 times but it is gonna take me bout 6 hours.

Does this make sense??

Thanks

F

**norie** · Oct 21st 2005

Re: Update 150 different table values at once

Freddy

What you need to do is dynamically create and run the update queries using VBA.

Can you post the SQL code for 1 of the update queries?

Are the tables actually called Table 1 etc?

FreddyKrueger · Oct 24th 2005

Re: Update 150 different table values at once

Hi norie

Using VBA sound like a good idea, although I have no experience on macros in access. The SQL code for one update query is as follow:

SQL

UPDATE MAINTABLE INNER JOIN TABLE1 ON MAINTABLE.ID = TABLE1.MAINTABLE SET MAINTABLE.[Table name] = "TABLE1";

And the Table names are not table1 to table150. They have all got individual names.

f

**norie** · Oct 24th 2005

Re: Update 150 different table values at once

Freddy

How are the tables named?

Do you have a list of the table names?

Are there only these tables and the main table in the database?

FreddyKrueger · Oct 24th 2005

Re: Update 150 different table values at once

Hi Norie

I'll attach a list of all the small and the main table name. These are not the only tables in my database. But I can save a copy of the database and just delete all the other tables.

Freddy

**norie** · Oct 24th 2005

Re: Update 150 different table values at once

Freddy

If you had those table names in a table you could possibly use something like this. Please note it is totally untested.

Code

Sub Test()
Dim db As Database
Dim rst As Recordset
Dim strTableName As String
Dim strSQL As String


    Set db = CurrentDatabase
    Set rst = db.OpenRecordset("SmallTables")


    rst.MoveFirst


    While Not (rst.EOF)
        strTableName = rst.Fields("TableName")
        strSQL = "UPDATE HR INNER JOIN " & strTableName
        strSQL = " ON HR.ID = " & strTableName & ".HR Set HR.[Table name] = ' &  strTableName & " ';
        DoCmd.RunSQL strSQL
        rst.MoveNext
    Wend
    
End Sub

Display More

Update 150 different table values at once

Participate now!

Share