Hi there,

I haven't used FM for a while and I am now supposed to design a
database that records information each time a student visits an office
and the reasons for the visits.

I am not sure how to do this...as I need to keep track of the number of
visits but I also want to avoid creating a new record for the student
each time they come in, can someone point me in the right direction?
Would I need to use relationships to make this work properly?

I can't seem to make relationships work. I created 2 tables one which
held all the student info. and the other which held both the student
info. and the visit information. I was hoping that I could
auto-populate the student info. in the second table if I typed in a
student id that already existed in the database. But it won't even let
me enter a student value at all in the second table. I keep getting an
error saying I must first enter a valid student id. ???

Thanks for your time,
chris

marshachristy (AT) gmail (DOT) com wrote:

The key to relationships is to have one field in each related table that
will contain the same unique identifier. A "key" if you will.

Automatically creating a related record from another table is a matter of
first defining the relationship (e.g., Student::ID = Visits::ID), making
sure that you can create records in Visits from Studen.

Place a portal to the Visits table on a Students layout, with the
appropriate fields in the portal.

Now you find a student's record, and all visits are shown in the portal.
When you make a new visit entry in the last line of the portal,your
related record are created in the Visits table. No need to go to the
Visits layout.

Matt

The fields in the Student table would be eg.
Student ID
Student Name
etc.

The fields in the Visits table are
Student ID (no other student info)
Reason For Visit
Date
etc.

Create a relationship from the Student file to the Visits file using
the fields "Student ID" in both files.
On the "Visits" side of the relationship checkmark the box "Allow
creation of records in this table via this relationship".

Put a portal on the Student layout showing the Visits using the above
mentioned relationship. You can now type into the last row in the
portal which will create the Visit record for you and auto enter the
student ID in that Visit record.

In article <3OsOe.2111$IG2.1700@trndny01>, "Matt Wills" <I'm (AT) Witz (DOT) end>
wrote:

Matt haas given a good answer. I will follow with some related
recommendations (no pun intended)

In each table you create, make a primary key field that is a serial
number automatically created by the computer whenever a new record is
created. This will be very useful for any future relationships you want
to that table. The field should be defined to validate that it is not
emty and is unique, and to prevent the user from altering the content of
the field. This makes a useful record ID that is never edited by the
user, so that relationships based on that field will remain unbroken.

For another table that you want to have related to the first table,
create a foreign key field that will hold the relationship key, using
the primary key of the master table. Which table is the Master and which
is the related table depends on the logic of how you want to use the
relationship. The terms "Parent" and "Child" are also used.

In your case, the Student record will be the master or parent, and the
Visit record will be the related or child record. The primary key in the
Student table, defined as an automatically generated serial number that
is unique and can not be changed, will form one end of the relationship.
Call that field kpStudentID. The Foreign Key field in the Visit record
will just be a number field to hold the Student ID number. Call it
kfStudentID. Then the relationship would be defined as

Student:kpStudentID = Visit:kfStudentID,

where the notation Student:kpStudentID means the kpStudentID field in
the Student table, and Visit:kfStudentID means the kfStudentID field in
the Visit table.

You should also define the relationship to allow creation of a record in
the Visit table by means of the relationship.

Then when you do what Matt said, and put a portal of Visits in a layout
of the Student table, you will be able to see all the visits of that
student, and will be able to create new visits.

The fields that you put in the portal should include VisitDate and any
fields you want for data about the visit, all from the Visits table. You
do not need to put the field Visit:kfStudentID in the portal, as that
value will be assigned automatically via the relationship when you
create a new record in the portal.

It is also good practice to include in each table date fields that are
automatically filled with Creation Date and Modification Date whenever a
record is created or modified. You should also define these fields to
prevent user modification of the contents. This is useful for
diagnostics and checking for data entry errors.

Bill Collins

--
For email, remove invalid.

On 22 Aug 2005 14:47:20 -0700, marshachristy (AT) gmail (DOT) com wrote:

I'm a hairdresser and I've created a database for my clients that
shows their info and a portal for entering each of their visits.
You can have it, if you want, and change it to suit your needs.

Erik

It looks like your main question has been answered, but I did note that you
said:

I created 2 tables one which
Unless you need to keep a record of what the student's data was *at the time
of the visit*, don't store the student information in the second table, as
it's redundant. You can show the student data "live" on layouts of the
second table, via relationship. If you, in effect, copy the data to the
second table ("auto-populate", via lookup or auto-enter calcs), then the
student data in the second table won't be updated when the student's master
record is updated, so I would imagine that duplicate data to be of limited
use.

--

Chris Cain
Extensitech, Inc.
ccain (AT) extensitech (DOT) com


<marshachristy (AT) gmail (DOT) com> wrote

Hi there,

wow, I didn't really expect such a response so quick. You guys are
awesome. Erik, if you don't mind sharing I would really appreciate it.
It would be nice to have another one to look at while at the very
least.

Thanks a lot everyone, I will get started right away and let you know
how I do!!

chris