New Database

[Judy Martin]

I am a novice to Access!

I have to start a database for the publishing company
that I work for. We sell books via subscription ie
clients pay a nominal deposit to secure an order on a
book and have their name on a subscribers page in the
book and then pay the balance about 1 month before
publication. The book is sight unseen when they pay. The
databse headings I need are as follows:

CLIENT ID; TITLE; INITIAL; SURNAME; SALUATION; ADDRESS1;
ADDRESS2; ADDRESS3; ADDRESS4; ADDRESS5; POST CODE;
COUNTRY; TELEPHONE; EMAIL

PAYMENT METHOD; CC TYPE; CC NUMBER; CC EXPIRY; QUANTITY;
AMOUNT; DEPOSIT; BALANCE; SHIPPING COST

SUBSCRIBER NO; SUBSCRIBER BOOK TITLE; SUBSCRIBER FIRST
NAME; SUBSCRIBER SURNAME; SUBSCRIBER LOCATION; DELIVERY
NAME; DELIVERY ADDRESS1; DELIVERY ADDRESS2; DELIVERY
ADDRESS3; DELIVERY ADDRESS4; DELIVERY ADDRESS5; DELIVERY
POST CODE; DELIVERY COUNTRY; SHIPPING

SOURCE; ENTRY DATE; FREE GIFT; COMMENTS; SALES HISTORY;
VETTED ORG

Should they be, as grouped above, in 4 tables? How do I
linked them together so that if I click on a certain
client (using Client ID presumably) all the data from the
other tables will be visable.

Is it possible to have toggle links on the form of the
Clients Database, first of the above groups, so that I
could see other data related to any one Client? Or would
it be better to have 4 Tab forms?

Sorry that this is so long winded, I did say at the
beginning that I am new to this.

Thanks to anyone who might be in a position to help me. I
would be eternally grateful.

Jude

 

[Rolls]

First you need to understand that Access is a SQL database and that SQL uses
an Entity-Relationship model. You'll have two types of tables 1) Entity, 2)
relationship. For Example tblPeople is an entity table. tblBooks is an
entity table. Since many people can buy many books you'll need a
relationship table to show which people buy which books. The PK fields in
tblPeople and tblBooks become FK fields in tbl People-Books. You link the
tables by means of queries and show the information in your database via
forms. Where there is a one : many relationship between two tables you use
a form : subform combination to be able to "drill down" so you can see all
related information.

The key fields are Autonumber fields in the entity tables and Long Integer
fields in the FK fields. Don't show the users these fields but do show the
related descriptive fields. Use combo boxes in the subforms to be able to
add and change data. My preference is to use a yes-no field for "deletes"
making a delete a two step process. First flag for delete, then
periodically do a physical delete query run by the administrator. This way
the users can't claim that "the database deleted all my data!".

You need to understand that in a SQL database there should be no duplicate
information stored anywhere. The subject that deals with table structure is
called data normalization. Study the 1st, 2nd, and 3rd normal forms (1NF,
2NF, 3NF). This will jump start you and get you going in the right
direction. Learn the rules first before you decide to break them.

 

[Lisa Reber]

Hi - I'm a novice too, so you might be ahead of me
already. I have a number of posts below about my
(partially-inherited) poor table design. Here's a couple
thing I'd do over - don't put any spaces in your field
names, just in the captions. Second, check out MS
Knowledgebase article 100139 on Database Normalization
Basics (will help with Rolls' reply). Third, when you
create your database objects, name them according to
object type, like tblClient, tblSubscriber, frmOrders,
etc. It's supposed to be good practice, and it does (or it
would have) help on those days when my brain is
overloaded. Finally, and it's what sent me here in the
first place, be sure you have good/correct relationships
set up before entering any permanent data! Rolls sent me a
good reply on a question I had, but it took me a while to
figure out that PK = Primary Key, and FK = foreigh key.
Like I said, maybe you're ahead of me so this is redundant.

Good luck! - Lisa

 

[Rolls]

For instance, you have tblState which is an entity table containing 50
records. You see:

1) 1, AL, Alabama
2) 2, AK, Alaska
..
..
..
50) 50, Wyoming

Another table with addresses would let the user select the appropriate state
from a combo box that would use tblState as the control source. The user
would see "Alabama" and the control would save "1" as the key field (FK)
into any other tables requiring a state.

 

[John Vinson]

For instance, you have tblState which is an entity table containing 50
records. You see:

1) 1, AL, Alabama
2) 2, AK, Alaska
.
.
.
50) 50, Wyoming

Another table with addresses would let the user select the appropriate state
from a combo box that would use tblState as the control source. The user
would see "Alabama" and the control would save "1" as the key field (FK)
into any other tables requiring a state.

Or, (to continue a pseudo-religious argument) remove the numeric ID
(which is a 4 byte field requiring its own index) and use the two-byte
state code as the Primary Key, and store *it* in the address table -
where it can be printed directly rather than needing a join.