I have a table that is in the format;

Name: Bob Smith
Contact: July 1 2003
Issue: gardening
---------------------------
Name: Sue Brown
Contact: April 12 2003
Issue: Fabric
---------------------------
Name: Bob Smith
Contact : August 21 2003
Issue: decorating
---------------------------

There might be several contacts for Bob Smith that would contain the same
name but a different date and issue. Each seperate contact forms a new
record that includes the name, contact and issue fields.

What I want to do is move the various contacts fields into one Record in the
following format

Name: Bob Smith
Contact 1: July 1 2003
Issue 1: Gardening
Contact 2: August 21 2003
Issue 2: decorating
--------------------------------

Hi Dan,

This is the reason for using a "relational" database model. What you
propose to do would limit your number of contacts to the number you
decided you should add to your table design. I mean by that that
having the contact fields in your customer records would mean you
couldnt record more contacts then you had fields predesigned for, and
that unused fields would be wasted space in your database.

The proper way to do this in access, or any relational database, is to
have seperate tables for your customer information and your
servicecall specific information.

tblCustomer (holds customer contact information)
-----------
Cust_ID; AutoNumber
FName; Text, 30
LName; Text, 30
Phone1; Text, 13
Phone2; Text, 13
Street; Text, 40
Street2; Text, 40
.... (etc)

tblIncidents (Holds incident specific information)
------------
Incident_ID; AutoNumber
Customer_ID; Number (Foriegn key to tblCustomers.Customer_ID)
IncidentDate; Date/Time
IncidentReason; Text 50
IncidentNotes; Text 255

You can now build a form based on each of these tables,
frmCustomers would have tblCustomers as its control source and would
display 1 record per form.

and sfrmIncidents would have tblIncidents as its control source and
would display multiple records per form. This form is to be used as a
subform in frmCustomers.

Once you've got the layout you can go to frmCustomers and use the
Control toolbox to add a subform to frmCustomers. use sfrmIncidents,
of course. Because the field Customer_ID is in both tables the subform
wizard will offer to link the 2 forms on that key. let it. You are
pretty much done, just move things around till they are pretty

Note: letting the subform wizard establish the link is analogous to
establishing a relationship between the 2 tables. Customer_ID needs to
be the primary key in tblCustomers, Incident_ID is the primary key in
its table, and in order to establish the link Customer_ID in
tblIncidents needs to be a Long Integer Number so that it matches the
AutoNumber variable type.

regards,

John
jobrien AT acscience DOT com



"Daniel R McGee" <dmcgee (AT) talkworks (DOT) bc.ca> wrote

You're moving in the right direction for making good use of a relational
database. Your earlier format could well have been done in Excel.

You'll need two tables, tblPerson and tblContact

Using the format you've given, tblPerson could have the following fields:

PersonID, Autonumber; LastName;text; FirstName, text; Note,text

tblContact could have the following fields:

ContactID,autonumber, PersonID;number|LongInteger; ContactDate,date;
ContactIssue,text; ContactNote,text

Once you have the tables designed as above you can design a Form based on
each table and then convert those forms into a Form/Subform set - there is
ample information in Help on how to do that and Access has built-in plenty
of assistance. You'll want your subform to show multiple records which you
can sort into date order/

HTH
--
-Larry-
--

"Daniel R McGee" <dmcgee (AT) talkworks (DOT) bc.ca> wrote