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(t blName 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.OpenRecordse t("SELECT TOP 1 * FROM [" & tblName & "];", ,
dbOpenSnapshot)
For k = 0 To rsG.Fields.Coun t - 1
getFieldNames = getFieldNames & "[" & rsG(k).Name & "], "
Next k
rsG.Close
Set rsG = Nothing
getFieldNames = Left(Trim(getFi eldNames), Len(Trim(getFie ldNames)) - 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("DEL ETE FROM [HISTORICAL];")
For i = 0 To db.TableDefs.Co unt - 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(t blName)
& ") "
cSQL = cSQL & "SELECT " & getFieldNames(t blName) & " "
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.)
