By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,544 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

consolidating tables

P: n/a
I've used Access pretty basically for the last couple of years, and I'm
slowly trying to get a bit more advanced.
I'm attempting to consolidate multiple tables with similar information.
Some tables might have an extra column, but for the most part these
fields are similar (data within fileds is unique, at least somewhat
unique).
What is the best way to get the headers all populated and to fill the
data fields accordingly. For example, let's say my largest column set
is 30 fields, however some of my tables have only 20 or 25 fields of
that same header. I don't want to lose any header information, and i
want the data to fill whatever column it fillls in the individual
tables i've created. How can i consolidate these tables together??
I'm attempting to consolidating anywhere from 10 -20 seperate tables at
a time into one large "historical file". I thought this could be done
easily as i remember having this issue a while back. However it has
been some time since i've dealt with Access at this level. Any help
would be appreciated. Thanks...

May 12 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
DFS
Monkey Boy wrote:
I've used Access pretty basically for the last couple of years, and
I'm slowly trying to get a bit more advanced.
I'm attempting to consolidate multiple tables with similar
information. Some tables might have an extra column, but for the
most part these fields are similar (data within fileds is unique, at
least somewhat unique).
What is the best way to get the headers all populated and to fill the
data fields accordingly. For example, let's say my largest column set
is 30 fields, however some of my tables have only 20 or 25 fields of
that same header. I don't want to lose any header information, and i
want the data to fill whatever column it fillls in the individual
tables i've created. How can i consolidate these tables together??
I'm attempting to consolidating anywhere from 10 -20 seperate tables
at a time into one large "historical file". I thought this could be
done easily as i remember having this issue a while back. However it
has been some time since i've dealt with Access at this level. Any
help would be appreciated. Thanks...

Your name's funny. I used to make fun of my best friend, who was really
hairy, by calling him Monkey Boy - he hated it.

1) First step is cleaning the tables and data a bit. Pick through all your
tables and standardize the column names (if one table has CustID and the
other has CustomerID, choose one and use it everywhere). Same with column
datatypes (if CustID is datatype Text in one table and datatype Number in
another, choose one and use it everywhere). A byproduct of this is you will
probably end up cleaning the data a bit as well. That's a good thing.

2) Build your new "historical" table into which all the other data will go.
It must have every field found in all the other tables, and it should match
the data types you end up with after the cleaning process in step 1.

3) Make a copy of the database, and do the following in the copy until we
get it to work. Then do it in the master.

4) Create a new module, and paste the following code

Public Function getFieldNames(tblName As String) As String

'BUILDS A STRING CONTAINING ALL THE FIELDS IN A TABLE
getFieldNames = ""

Dim db as database, rsG As Recordset, k As Byte
set db = currentdb()

Set rsG = db.OpenRecordset("SELECT TOP 1 * FROM [" & tblName & "];", ,
dbOpenSnapshot)
For k = 0 To rsG.Fields.Count - 1
getFieldNames = getFieldNames & "[" & rsG(k).Name & "], "
Next k
rsG.Close
Set rsG = Nothing

getFieldNames = Left(Trim(getFieldNames), Len(Trim(getFieldNames)) - 1)

End Function

Public Sub appendHistory()

Dim db As Database, cSQL As String, tblName As String
Dim response As Byte, i As Byte

Set db = CurrentDb()

db.execute("DELETE FROM [HISTORICAL];")

For i = 0 To db.TableDefs.Count - 1
If db.TableDefs(i).Name <> "HISTORICAL" Then

tblName = db.TableDefs(i).Name
response = MsgBox("Append data from " & tblName & "?", vbYesNo,
"Build History")

If response = vbYes Then
cSQL = "INSERT INTO [HISTORICAL] (" & getFieldNames(tblName)
& ") "
cSQL = cSQL & "SELECT " & getFieldNames(tblName) & " "
cSQL = cSQL & "FROM [" & tblName & "] "
db.Execute (cSQL)
End If

End If
Next i
MsgBox "Finished"

End Sub
5) I named your destination table HISTORICAL. Change the code if you want.
Run the appendHistory routine and see what happens.

6) Post back here and we'll get you through it.
(the alternative to the code method I use here is to cut and paste the data,
or build and run append queries. Either can get tedious if you have lots of
tables.)

May 12 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.