how to combine two tables into one table

i would appreciate some help.

I need to combine two tables into one table. there is one field that is in
common in both tables. I have set up a relationship between the two, but then
what? how do i "see" the new table?

I am trying to combine two tables (table 1 "customer name" and "address",
table 2 "customer name" and "item bought"). the common field is "customer
name", but in table 1, i have two listings for the same "customer name", work
and home address. How can I combine the tables so that "customer name", "item
bought", "work address", "home address" is combined into one table?

I NEED some HELP! My boss is breathing down my neck!

newbie arty wrote:
i would appreciate some help.

I need to combine two tables into one table. there is one field that is in
common in both tables. I have set up a relationship between the two, but then
what? how do i "see" the new table?

I am trying to combine two tables (table 1 "customer name" and "address",
table 2 "customer name" and "item bought"). the common field is "customer
name", but in table 1, i have two listings for the same "customer name", work
and home address. How can I combine the tables so that "customer name", "item
bought", "work address", "home address" is combined into one table?

I NEED some HELP! My boss is breathing down my neck!

Don't let him get away with it. Access is powerful and rewarding, but
the best of us need time to learn it. You need training or at least a
good book, and time to absorb it. Otherwise you _will_ fail where you
_should_ succeed. Personally, I like the video training from Lynda.com
(monthly fee), but here are some other good resources:

===
Free samples from Lynda.com on table design:
http://www.lynda.com/home/Player.aspx?lpk4=31001 (A2007)
http://www.lynda.com/home/Player.aspx?lpk4=13150 (A2003)

Microsoft tutorial on table design:
http://tinyurl.com/ms-table-design-tutorial

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
====

One at a time, or you'll go up in smoke!

Look at the top three links on table design. It's usually far better to
have a numeric "key" field to link tables. In a one-to-many
relationship, you simply include the key field from the "one" record
(customer) as a "foreign key" field in the "many" records (item bought).
So "Item bought" contains a reference to the customer id.

Concentrate on getting table design right first, and everything else
will be much, much easier.

HTH

Phil, London

Then you create a query to combine the information, and the output of a
query initially looks just like a table.

This is not a good idea. Your current table with separate rows for each
address per customer is the correct design. Having separate columns for work
and home is what's known as 'encoding data as column headings'. A
fundamental principle of the database relational model (the Information
Principle) is that data is stored as values at row positions in tables and in
no other way.

What you do need to do, however, is create another table which has one row
per customer and contains the customer names along with any other values from
table 1 which are always the same for each customer. If for instance table 1
contained an account number column which identifies each customer, whose
value would of course be repeated for both addresses, then that should go in
the new Customers table.

To do this you should first create an empty Customers table with a columns
Customer Name and any others like AccountNumber which are always the same for
each customer. Make the Customer name column the primary key of the table.
Then create an append query based on table 1 and append the relevant columns
into the new Customers table. You'll get a message saying that it can't
append a number of rows due to key violations; don't worry, this is what you
want to happen as it means only one row for each customer will be appended.

Once you are happy that the right rows have been inserted into the new
Customers table you can delete redundant columns like Account number from
table 1, but NOT Customer Name! That needs to stay in table 1 as the foreign
key which references the primary key of Customers. This does assume that all
customers have distinct names of course.

Table 1 (CustomerAddresses) should have a column AddressType into which you
can put values 'Home'. 'Work' etc to distinguish the address types.

You should then delete the relationship between table 1 and table 2, and
create new relationships:

1. Customers to table 1 (CustomerAddresses) on Customer Name. This will be
a on-to-many relationship and you should enforce relational integrity and
cascade updates.

2. Customers to table 2 (CustomerPurchases) on Customer Name. This will
again be a on-to-many relationship and you should again enforce relational
integrity and cascade updates.

For data entry create a form based on Customers and embed forms based on
tables 1 and 2 in the Customers form as subforms, linking them on Customer
Name. The subforms should be in continuous form view or datasheet view, and
the main customers form in single form view. You don't need to include
controls for Customer name in the subforms. only in the main form; the
linking mechanism will automatically insert the correct customer names into
tables 1 and 2 when you add new address or purchase records in each subform.
You can then insert as many addresses or purchases per customer as necessary
simply by going to the customer's record in the main form and inserting new
rows in the subforms.

You can do the same with a report, creating a parent report based on
Customers and two subreports based on tables 1 and 2.

Data should only be entered or view via the form and/or report, never in the
table or queries based on them in raw datasheet view. Its in the form or
report that you see the data brought together, not in a table.

If you need to bring the data together for other purposes, e.g. to export to
Excel, you can join the tables in a query. A query could in fact be set up
to produce results similar to the table you were intending to create.
Queries can also be used to aggregate the data, e.g. counting the purchases
per customer or summing the value of purchases per customer.

Ken Sheridan
Stafford, England

newbie arty wrote:
i would appreciate some help.

I need to combine two tables into one table. there is one field that is in
common in both tables. I have set up a relationship between the two, but then
what? how do i "see" the new table?

I am trying to combine two tables (table 1 "customer name" and "address",
table 2 "customer name" and "item bought"). the common field is "customer
name", but in table 1, i have two listings for the same "customer name", work
and home address. How can I combine the tables so that "customer name", "item
bought", "work address", "home address" is combined into one table?

I NEED some HELP! My boss is breathing down my neck!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200909/1

Huh? "Having separate columns for work and home is what's known as
'encoding data as column headings"

While in theory it might sound good, but every field name describes
the data. FirstName and LastName are encoding data in column
headings.

The only downside to a WorkAddress and HomeAddress is you can't have a
third (Snowbird address, for instance), and you'll be wasting some
space in rows that don't have both addresses. The upside is not
having to do more joins to get addresses.

Bryce-Cobb is great and all, but it's not a requirement, and sometimes
breaking those rules are permissable.


Chris

On Sep 22, 5:46*am, "KenSheridan via AccessMonster.com" u51882@uwe
wrote:
This is not a good idea. *Your current table with separate rows for each
address per customer is the correct design. *Having separate columns for work
and home is what's known as 'encoding data as column headings'. *A
fundamental principle of the database relational model (the Information
Principle) is that data is stored as values at row positions in tables and in
no other way.

What you do need to do, however, is create another table which has one row
per customer and contains the customer names along with any other values from
table 1 which are always the same for each customer. *If for instance table 1
contained an account number column which identifies each customer, whose
value would of course be repeated for both addresses, then that should go in
the new Customers table.

To do this you should first create an empty Customers table with a columns
Customer Name and any others like AccountNumber which are always the same for
each customer. *Make the Customer name column the primary key of the table.
Then create an append query based on table 1 and append the relevant columns
into the new Customers table. * You'll get a message saying that it can't
append a number of rows due to key violations; don't worry, this is what you
want to happen as it means only one row for each customer will be appended.

Once you are happy that the right rows have been inserted into the new
Customers table you can delete redundant columns like Account number from
table 1, but NOT Customer Name! *That needs to stay in table 1 as the foreign
key which references the primary key of Customers. *This does assume that all
customers have distinct names of course.

Table 1 (CustomerAddresses) should have a column AddressType into which you
can put values 'Home'. 'Work' etc to distinguish the address types.

You should then delete the relationship between table 1 and table 2, and
create new relationships:

1. *Customers to table 1 (CustomerAddresses) on Customer Name. *This will be
a on-to-many relationship and you should enforce relational integrity and
cascade updates.

2. *Customers to table 2 (CustomerPurchases) on Customer Name. *This will
again be a on-to-many relationship and you should again enforce relational
integrity and cascade updates.

For data entry create a form based on Customers and embed forms based on
tables 1 and 2 in the Customers form as subforms, linking them on Customer
Name. *The subforms should be in continuous form view or datasheet view, and
the main customers form in single form view. *You don't need to include
controls for Customer name in the subforms. only in the main form; the
linking mechanism will automatically insert the correct customer names into
tables 1 and 2 when you add new address or purchase records in each subform.
You can then insert as many addresses or purchases per customer as necessary
simply by going to the customer's record in the main form and inserting new
rows in the subforms.

You can do the same with a report, creating a parent report based on
Customers and two subreports based on tables 1 and 2.

Data should only be entered or view via the form and/or report, never in the
table or queries based on them in raw datasheet view. *Its in the form or
report that you see the data brought together, not in a table.

If you need to bring the data together for other purposes, e.g. to export to
Excel, you can join the tables in a query. *A query could in fact be set up
to produce results similar to the table you were intending to create.
Queries can also be used to aggregate the data, e.g. counting the purchases
per customer or summing the value of purchases per customer.

Ken Sheridan
Stafford, England

newbie arty wrote:
i would appreciate some help.

I need to combine two tables into one table. there is one field that is in
common in both tables. I have set up a relationship between the two, but then
what? how do i "see" the new table?

I am trying to combine two tables (table 1 "customer name" and "address",
table 2 "customer name" and "item bought"). the common field is "customer
name", but in table 1, i have two listings for the same "customer name", work
and home address. How can I combine the tables so that "customer name", "item
bought", "work address", "home address" is combined into one table?

I NEED some HELP! My boss is breathing down my neck!

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/20...