Linking a Paid Table to Main Database

[faxylady]

I maintain a database for my job, which is obtaining lead appointments for
salesmen. This main database contains all the info necessary about the
appointments. Now I want to create a PaidSales Table to keep track of info
regarding paid for sales. For ex., the PaidSales Table would have the date
sold, party sold to, saleman's name, date payment received and an OLE object
field for a copy of the received check.

When the Sales field is clicked in the main database, I would like the sales
information transferred from that database into the PaidSales Table. This is
relational database design, which I have never done before and therefore I am
requesting help. Thanks.

 

[KARL DEWEY]

You are using a phrase of 'main databse' that I believe you mean 'main table'.In a relational database the data is not 'transfered' but is 'related'
either in a one-to-many or many-to-many fashion. How would these two tables
be related?

Some describe a relational database as being like a family tree - Head of
household - children - grand-children.
If you were a fruit seller then at the top you would have a list of fruit
that you sell, then varieties of each.
Fruit ---
FruitID - autonumber - primary key
Fruit - text - name of fruit - apples, pears, grapes
Season - text - when available

Variety ---
VarietyID - autonumber - primary key
FruitID - number - long integer - foreign key - related to FruitID
Name - text
Price - number - currency
UnitOfSale - text

 

[Carl Rapson]

I maintain a database for my job, which is obtaining lead appointments for
salesmen. This main database contains all the info necessary about the
appointments. Now I want to create a PaidSales Table to keep track of
info
regarding paid for sales. For ex., the PaidSales Table would have the
date
sold, party sold to, saleman's name, date payment received and an OLE
object
field for a copy of the received check.

When the Sales field is clicked in the main database, I would like the
sales
information transferred from that database into the PaidSales Table. This
is
relational database design, which I have never done before and therefore I
am
requesting help. Thanks.

You don't need to copy the sales information to the PaidSales table; all you
need to copy is the unique identifier of the sales (appointment?) record.
Using that, you can easily fetch the appointment information any time you
need it, and you aren't storing information redundantly.

Since you're new to relational database design, here's a nomenclature tip -
information is stored in fields (sometimes called columns) in a table. The
database encompasses all tables and their relationships with each other. As
Karl pointed out in his post above, you'll need to become familiar with the
concepts of primary and foreign keys if you're going to work with relational
databases.

Carl Rapson

 

[faxylady]

Thank you for your excellent response. Can you recommend some material that
I can study for relational databases. My training offered some basics on
relational databases but they are still an enigma for me. I would like to
use them in some things that I do. Thanks again.

 

[John W. Vinson]

Thank you for your excellent response. Can you recommend some material that
I can study for relational databases. My training offered some basics on
relational databases but they are still an enigma for me. I would like to
use them in some things that I do. Thanks again.

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

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

espcially the Database Design 101 links in Jeff's site.

There are many good books as well. If you can find a copy of Rebecca Riordan's
(alas out of print) _Designing Relational Database Systems_ (Microsoft Press)
it's a treasure. Another that I've seen highly praised (I don't have a copy)
is Michael Hernandez' _Database Design for Mere Mortals_.


John W. Vinson [MVP]