adding records

T

Ted

I'm having difficulty building a db. I bought Office
2000 9 in 1 for Dummies and I'm still too dumb to figure
this out.
I understand how to build a table and a form. What I
don't understand is how I add records when the table
won't accept a field that has already been used. ie.: I
use a customer # to identify each record and I assumed
that this would be the relational field. How else can I
link information from one table to another?

Specifically: I have a business where I perform a
service on a periodical basis. I have no problem
entering the info in the table for the first service, but
when I try entering the next service (using the customer
# as the common field) the table rejects my data because
the field allready exhists. Do I have to build a new
table each time I perform the service?
 
J

John Vinson

Specifically: I have a business where I perform a
service on a periodical basis. I have no problem
entering the info in the table for the first service, but
when I try entering the next service (using the customer
# as the common field) the table rejects my data because
the field allready exhists. Do I have to build a new
table each time I perform the service?

No. You need three tables: a table of Customers, with CustomerID (I'd
suggest not using # in fieldnames) as its Primary Key, with one record
per CustomerID; a table of Services (just the list of services that
you offer); and a third table of ServicesProvided.

This table would probably have at least three fields:

ServiceProvidedID <autonumber, primary key>
CustomerID <Long Integer, link to Customers>
ServiceID <Long Integer, link to Services>

I think your problem is that you either have only one table, or that
you're using Customer# as the Primary Key of the Services table. It
can't be the primary key, which must be unique, if you want more than
one service per customer!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top