473,320 Members | 1,987 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

consolidating tables

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
1 2607
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: David | last post by:
My organization currently has 2 different MySQL database servers. We are in the process of moving the databases from Server A to Server B and have Server B be our primary database server. I've...
44
by: Mariusz Jedrzejewski | last post by:
Hi, I'll be very grateful if somebody can explain me why my Opera 7.23 (runing under linux) doesn't show me inner tables. Using below code I can see only "inner table 1". There is no problem with...
6
by: imani_technology_spam | last post by:
Let's say I have two tables: CREATE TABLE dbo.OldTable ( OldID int NOT NULL, OldNote varchar(100) NULL ) ON GO
3
by: Random Person | last post by:
Does anyone know how to use VBA to relink tables between two MS Access databases? We have two databases, one with VBA code and the other with data tables. The tables are referenced by linked...
11
by: dskillingstad | last post by:
I've been struggling with this problem for some time and have tried multiple solutions with no luck. Let me start with, I'm a novice at Access and I'm not looking for someones help to design my...
2
by: Gregc. | last post by:
G'day I have two tables with transactional data. Table 1 has a Cost Centre, Account, Fund Code and a YTD0-6 figure for each Account Number. Table 2 has Cost Centre, Account, Fund Code, Period...
59
by: phil-news-nospam | last post by:
In followups by Brian O'Connor (ironcorona) to other posts, he repeats the idea that using tables in CSS is not something that should be done because IE doesn't support it. Of course I'm not happy...
0
by: xiaowei | last post by:
hi, I am using Access 2003. I have many tables which track the changes made to the data base. Each table caters to one previously existing table. Hope my explanation is clear enough. Each table have...
2
by: yasmine | last post by:
Hi friends, How can i consolidate the values of a table which occurs mostly? what i'm asking is consider a database which contains many tables. Each table has a field namely contents. The value of...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.