How to build a successful database for Sales

I am building a sales tracking systems for my work. I have to track the
following: employee's, what location they work at, reason customer came in,
who refered them, opening or closing product(s), etc. My biggest problem is
that customers can open mulitple products and each product can be opened with
multiple checks. How do I link (relationship) the products with their
multiple checks?
Ex. -
I have built my database once with have fields in my Sales Tracking table
listed as: product 1, source funds 1, source funds 2, source funds 3; product
2, source funds 4, source funds 5, etc; That is why I am stuck in the mud.
Please help.

You are sort of at the general starting point of a database design which is
big topic. Below is what I give the people i our company who are at your
stage, sort of trying to put a whole book into a 1/2 page via
"oversimplifications" etc.

Once you are about 1/4 of the way through, (at least to the point of
defining the terms that you use) you'll be able to easily get a lot of useful
help in this forum.

Hope that helps a little.


Fred




The first step is to develop an organized picture of the
information/entities/process that you wish to database. Your end goals (and
a pre-reading of this) will also influence this process, as will knowing that
your core items will be tables which are lists of entities. Decide which
types of entities which get listed and what the real world
relationships/linkages are between these entities, realizing that the
linkages themselves are a type of entity. Create, write down and follow
clear definitions for any words that you use.

Then design a table structure to implement what you clarified / decided.
Entities which are something more than just a linkage become tables of that
type of entity.

- Information which is tied to the entity on a one-to-one basis is generally
an "attribute" of that entity and generally become fields in that entity's
table. -

- Information entities which have a "many to one" (="one to many")
relationship between each other are put into seperate tables and then linked.
Linking is a three step process. Step 1 Create fields in the tables
needed to implement step 2. This will be a Primary Key (PK) field in the
table on the "one" side (usualy automatically loaded via an autonumber) and a
Foreign Key (FK) in field the table on the "many" side. Step 2 happens
when entering data. To link two records, enter the "one" side record's
primary key content into the intended FK field in the "many" side record.
Step 3 is "drawing the link line" between the fields to be linked. There are
many ways to do this,(table design lines, queries, sql statements) most take
just a few seconds to do and most can even be done just at the moment that
the data is used.

-When information entities which have a "many to many" relationship, they
are linked by creating a (junction) table which is list of instances of those
links. Then create 2 links from the junction table record to the other 2
records in the normal "one to many" fashion, in both cases the junction table
record is on the "many" side.

Write down and follow definitions for what constitutes each type of record,
field and linkage. The definitions for your FK fields wil constitute
definiions of links.

Fred,

I am basically new to Access with less than 2 months of experience. I think
I have most of your suggestions already built into my database, but I can not
figure out how I should design my "Sales" table that includes multiple
product sales with each product have multiple funds sources (other bank
checks). That is where I am at. I would appreciate more help in this area.

thank you,
Norm

"Fred" wrote:

You are sort of at the general starting point of a database design which is
big topic. Below is what I give the people i our company who are at your
stage, sort of trying to put a whole book into a 1/2 page via
"oversimplifications" etc.

Once you are about 1/4 of the way through, (at least to the point of
defining the terms that you use) you'll be able to easily get a lot of useful
help in this forum.

Hope that helps a little.


Fred




The first step is to develop an organized picture of the
information/entities/process that you wish to database. Your end goals (and
a pre-reading of this) will also influence this process, as will knowing that
your core items will be tables which are lists of entities. Decide which
types of entities which get listed and what the real world
relationships/linkages are between these entities, realizing that the
linkages themselves are a type of entity. Create, write down and follow
clear definitions for any words that you use.

Then design a table structure to implement what you clarified / decided.
Entities which are something more than just a linkage become tables of that
type of entity.

- Information which is tied to the entity on a one-to-one basis is generally
an "attribute" of that entity and generally become fields in that entity's
table. -

- Information entities which have a "many to one" (="one to many")
relationship between each other are put into seperate tables and then linked.
Linking is a three step process. Step 1 Create fields in the tables
needed to implement step 2. This will be a Primary Key (PK) field in the
table on the "one" side (usualy automatically loaded via an autonumber) and a
Foreign Key (FK) in field the table on the "many" side. Step 2 happens
when entering data. To link two records, enter the "one" side record's
primary key content into the intended FK field in the "many" side record.
Step 3 is "drawing the link line" between the fields to be linked. There are
many ways to do this,(table design lines, queries, sql statements) most take
just a few seconds to do and most can even be done just at the moment that
the data is used.

-When information entities which have a "many to many" relationship, they
are linked by creating a (junction) table which is list of instances of those
links. Then create 2 links from the junction table record to the other 2
records in the normal "one to many" fashion, in both cases the junction table
record is on the "many" side.

Write down and follow definitions for what constitutes each type of record,
field and linkage. The definitions for your FK fields wil constitute
definiions of links.

Norm

"How" depends on "what". You'll need to provide a bit more description
about your data/situation.

For example, how are your tables structured to reflect the relationship you
described between "product" and "multiple fund sources"? Do you have three
tables? [Products], [Funds], [ProductFundSource]?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Norm" wrote in message
...
Fred,

I am basically new to Access with less than 2 months of experience. I
think
I have most of your suggestions already built into my database, but I can
not
figure out how I should design my "Sales" table that includes multiple
product sales with each product have multiple funds sources (other bank
checks). That is where I am at. I would appreciate more help in this
area.

thank you,
Norm

"Fred" wrote:

You are sort of at the general starting point of a database design which
is
big topic. Below is what I give the people i our company who are at your
stage, sort of trying to put a whole book into a 1/2 page via
"oversimplifications" etc.

Once you are about 1/4 of the way through, (at least to the point of
defining the terms that you use) you'll be able to easily get a lot of
useful
help in this forum.

Hope that helps a little.


Fred




The first step is to develop an organized picture of the
information/entities/process that you wish to database. Your end goals
(and
a pre-reading of this) will also influence this process, as will knowing
that
your core items will be tables which are lists of entities. Decide
which
types of entities which get listed and what the real world
relationships/linkages are between these entities, realizing that the
linkages themselves are a type of entity. Create, write down and follow
clear definitions for any words that you use.

Then design a table structure to implement what you clarified / decided.
Entities which are something more than just a linkage become tables of
that
type of entity.

- Information which is tied to the entity on a one-to-one basis is
generally
an "attribute" of that entity and generally become fields in that
entity's
table. -

- Information entities which have a "many to one" (="one to many")
relationship between each other are put into seperate tables and then
linked.
Linking is a three step process. Step 1 Create fields in the tables
needed to implement step 2. This will be a Primary Key (PK) field in the
table on the "one" side (usualy automatically loaded via an autonumber)
and a
Foreign Key (FK) in field the table on the "many" side. Step 2
happens
when entering data. To link two records, enter the "one" side record's
primary key content into the intended FK field in the "many" side record.
Step 3 is "drawing the link line" between the fields to be linked. There
are
many ways to do this,(table design lines, queries, sql statements) most
take
just a few seconds to do and most can even be done just at the moment
that
the data is used.

-When information entities which have a "many to many" relationship, they
are linked by creating a (junction) table which is list of instances of
those
links. Then create 2 links from the junction table record to the other
2
records in the normal "one to many" fashion, in both cases the junction
table
record is on the "many" side.

Write down and follow definitions for what constitutes each type of
record,
field and linkage. The definitions for your FK fields wil constitute
definiions of links.