Creating Database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a database that will track service calls made in the field. I
want to know how can I create a customer and track all the dates I've
serviced that customer. What i've been doing is having multiple records of
one customer. I just want certain information updated each time such as when
serviced and next service date. How can I update just this information? and
still keep track of previous service calls?
 
Calvin said:
I am creating a database that will track service calls made in the field. I
want to know how can I create a customer and track all the dates I've
serviced that customer. What i've been doing is having multiple records of
one customer. I just want certain information updated each time such as
when
serviced and next service date. How can I update just this information?
and
still keep track of previous service calls?
Click to expand...

Customer
CustomerID PK Autonumber
FirstName
LastName
etc.

Appointments
AppointmentID PK AutoNumber
CustomerID FK to Customer
AppointmentDate
AppointmentKept (YesNo tells you if you actually went to this appointment)
etc.

To find out when the last appointment that was kept was, you could use
something like:

SELECT Top 1 AppointmentDate FROM Appointment WHERE AppointmentKept AND
AppointmentDate <= Date() AND CustomerID = [Enter customer ID] ORDER BY
AppointmentDate;

To find out when the next appointment is, something like:

SELECT Top 1 AppointmentDate FROM Appointment WHERE AppointmentKept=False
AND AppointmentDate >= Date() AND CustomerID = [Enter customer ID] ORDER BY
AppointmentDate Desc;

Note you SHOULD have multiple records for each customer in your Appointments
table. That is how relational databases work.

HTH;

Amy
 
Use two related tables:

tblCustomer - name, address, etc.
CustID Autonumber Primary Key
CustName text -full customer name
CustAddr1 text -street address
CustAddr2 text -apt, mail stop,??
CustCity text
CustState text
CustZip text
CustNotes memo

tblService
ServiceID Autonumber Primary Key
CustID Long Integer -
Foreign Key = PK of parent table
ServiceDone datetime -date service was performed
ServiceNature text -fields here to suit your purposes
ServiceNote text -change to memo if necessary
ServiceSched datetime - this could be in tblCustomer

The tables are related one-to-many from tblCustomer to tblService.
When you have your tables laid out along the lines above, go to the
Relationships window and establish the relationship.

Now you will have one record per Customer. There will be several
records in tblService that relate back to each customer.

The usual way of displaying and using the data in the two tables is
the Form/Subform paradigm. Access Help is pretty good in explaining
what you have to do to get it all going. If you get the tables
designed and start having problems, post back with your current issue.

The above will just get you started along the path you want to travel.
There are still lots of things to work out such as the scheduling
algorithym, reports and other higher level considerations.

There are lots of books on getting started using Access. Check out
your local and online book stores. A couple of newsgroups I commend
to beginners:

microsoft.public.access.gettingstarted
microsoft.public.access..tablesdesign

There is also an outstanding resource at

www.mvps.org/access

MVP John Vinson recommends an online tutorial. You can google these
Access newsgroups on John's name and the word "tutorial" to find it if
you'd like.

Welcome to the world of Access development.

HTH
 
Back
Top