Combining tables with different structures and formats

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a contacts database with about 50 different tables in it. What I want
to do is combine all the tables into one large comprehensive table. The
first few tables have only a first name, last name and phone number. The
next set of tables has a little more data such as first name, last name,
address, city, state, zip, phone number, fax number. The most complete set
has much more comprehensive contact detail.

The large table I want to create will be structured on the most
comprehensive detail of the last set of tables.

What I have done is created the BIGTable with the structure based on the
last, most detailed tables. I simply opened the last table in design view
and clicked Edit, Select All, Edit Copy. Then I re-opened the BIGTable and
clicked Edit, Paste. This gave me the structure I wanted for my BIGTable.

When I re-opend my first and smallest table, hit Edit, Select All, Edit,
Copy, then re-opened the BIGTable and Clicked Paste Append, only two columns
appended--just the last name and phone number. The first table originally
had a first name, last name and phone number, so one column disappeared.

What do I need to do to copy data from one table to another? In other
words, how do I copy data from a table with fewer fields to another with more
fields. The larger table contains all the fields that the smalller tables
have.

Thanks
 
I have a contacts database with about 50 different tables in it. What I want
to do is combine all the tables into one large comprehensive table.
Click to expand...

Why on EARTH would you want to essentially destroy the relational
structure of your data?
The
first few tables have only a first name, last name and phone number. The
next set of tables has a little more data such as first name, last name,
address, city, state, zip, phone number, fax number. The most complete set
has much more comprehensive contact detail.
Click to expand...

Have you worked at creating a "normalized" structure? It sounds like
you have a great deal of redundant data in these tables, especially if
the same names are turning up in multiple tables. You would do much
better to carefully (using pencil and paper, NOT Access!) identify the
"entities" - real-life categories of people, things, or events - of
importance. Each type of entity should have its own table. In
particular, a table of People would contain ALL of the personal
information (which might or might not include the phone and fax
numbers - that may be an entity in its own right); and that
information would be stored ONCE, and only once, in the entire
database.
The large table I want to create will be structured on the most
comprehensive detail of the last set of tables.
Click to expand...

That sounds like the role of a Query joining multiple normalized
tables; it would be very unlikely that it would be necessary or
practical to construct a Table.
What I have done is created the BIGTable with the structure based on the
last, most detailed tables. I simply opened the last table in design view
and clicked Edit, Select All, Edit Copy. Then I re-opened the BIGTable and
clicked Edit, Paste. This gave me the structure I wanted for my BIGTable.

When I re-opend my first and smallest table, hit Edit, Select All, Edit,
Copy, then re-opened the BIGTable and Clicked Paste Append, only two columns
appended--just the last name and phone number. The first table originally
had a first name, last name and phone number, so one column disappeared.

What do I need to do to copy data from one table to another? In other
words, how do I copy data from a table with fewer fields to another with more
fields. The larger table contains all the fields that the smalller tables
have.
Click to expand...

An Append query... but not knowing anything about the structure of the
tables, I can't suggest exactly how to construct it.


John W. Vinson[MVP]
 
The structure of the BIGTable has firstName, middlename, lastname,
suffix,company, Jobtitle, busstreet,buscity,busstate,zip,BusPhone, BusFax,
homPhone, HomeFax, mobile, pager, categories, emailaddress, web page. The
smallest table has FirstName, LastName, Phone.
 
You would use append queries to copy rows from one table to another. You
would use update queries to update existing rows.
 
The structure of the BIGTable has firstName, middlename, lastname,
suffix,company, Jobtitle, busstreet,buscity,busstate,zip,BusPhone, BusFax,
homPhone, HomeFax, mobile, pager, categories, emailaddress, web page. The
smallest table has FirstName, LastName, Phone.
Click to expand...

Ok, I can see probably several tables here: * indicates the Primary
Key.

People
*PersonID Autonumber
FirstName
MiddleName
LastName
Suffix
CompanyID <assuming each person is affiliated with only one company>
JobTitle

Companies
*CompanyID Autonumber
CompanyName
<possibly company address or other info about the company as an
entity in its own right>

Categories <<< list of all valid categories
*Category

PersonCategories <<< many to many resolver table
*PersonID Long Integer <<< who is in this category
*Category Text <<< what category are they in

JobTitles <<< for data entry as source of a combo
*JobTitle Text

PhoneTypes
*PhoneType Text <<< e.g. Home, Fax, Pager, etc etc

Phones
*PersonID <<< whose phone is it
*Phone <<< the actual phone number
PhoneType <<< link to PhoneTypes

EMails
*PersonID
*EMailAddress

WebPages
*PersonID
*URL

The tables with two-field primary keys are because (for example) you
might have some people with multiple web pages, and some web pages
shared by several people.

I cannot see ANY rationale for creating one huge monster wide-flat
spreadsheet table out of this information.

Comments? Questions?

John W. Vinson[MVP]
 
Thanks for your input. Your suggestion of implementing Append queries
worked. Let me be more specific as to what I'm doing.

I have collected data for the last 10 years (mainly fax nos) that I stored
on diskettes. These are Winfax dBase III files that I have imported into
Access. When someone calls back and says "Take me off your list," I need to
have a way to remove their number. Their number might be in several
different files. the BIGTable will have all the files where duplicates can
be deleted and the info defined into categories, etc. I took the primary key
off in the BIgTable because I received some error messages regarding key
violations.
 
Thanks for your input. Your suggestion of implementing Append queries
worked. Let me be more specific as to what I'm doing.

I have collected data for the last 10 years (mainly fax nos) that I stored
on diskettes. These are Winfax dBase III files that I have imported into
Access. When someone calls back and says "Take me off your list," I need to
have a way to remove their number. Their number might be in several
different files. the BIGTable will have all the files where duplicates can
be deleted and the info defined into categories, etc. I took the primary key
off in the BIgTable because I received some error messages regarding key
violations.
Click to expand...

If you implement a properly normalized structure, with cascading
deletes enabled on the relationships, then removing a person from the
database becomes trivially simple: you delete the person's record in
the "one" side People table, and all related records in all related
tables will be deleted.

It is NOT necessary (or prudent) to put all the data into a big
wide-flat table to do this!

You're using a relational database. Use it relationally!

John W. Vinson[MVP]
 
Back
Top