database design

[Shell]

I need to design a database that will store set customer details, but that i
can change weekly to record sales figures. i need to keep record of past
dates and figures, without having to key in all customer details weekly.
i have the general jist of what to do but am getting stuck on how to set up
date, sales fields etc that i can modify weekly while still seeing a record
of past figures & not change customer details !!

 

[John Vinson]

I need to design a database that will store set customer details, but that i
can change weekly to record sales figures. i need to keep record of past
dates and figures, without having to key in all customer details weekly.
i have the general jist of what to do but am getting stuck on how to set up
date, sales fields etc that i can modify weekly while still seeing a record
of past figures & not change customer details !!

You're using a relational database. USE IT RELATIONALLY! It's NOT a
spreadsheet.

You should have (at least) TWO tables: a table of Customers, in which
you will enter the customer information once (and once only). This
would have a unique CustomerID (perhaps an autonumber - but *not* the
customer name which may not be unique) as a Primary Key.

Customer transactions would be stored in a *different* table, related
one-to-many to this table. It would have its own primary key, but also
a CustomerID field linked to the Customers table CustomerID. You can
use a Form with a Subform to keep these two tables in synch.

Take a look at the Northwind sample database that came with your
Access disk - it does just this.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps