newbie needs help

[John]

I am trying to create a database that has 2 tables
table 1 has these fields first name,last name,phone
#,socsecurity #
table 2 has last name ,socsercurity #,doctor

I want to have a relationship between them so when
I create a form with a suform(the 2nd table)
I can enter the data and it will appear in the proper
tables

I hope I explained that correctly
thanks for any help

John(begging for help)

 

[Roxie Aho]

John,
The idea of a relational database is not to duplicate
information.

I suggest your Individual table have the following fields:
IndividualID (autonumber, primary key), first name, last
name, phone, social security. Both phone and social
security are text fields because you don't do any math
with them.

The Doctor table fields would be: DoctorID(primary,
autonumber), IndividualID (long integer, foreign key from
Individual table) and Doctor.

Create your Individual form based on the Individual
table. Create the Doctor form on the Doctor table. In
Design view, drag the Doctor form onto the Individual
form. With the Individual form open, select the subform.
Click the Link Child Fields on the Data properties tab,
click the Build Button and select IndividualID for master
and child.

The arrangement will give you a form that can show many
doctors for each individual.

Roxie Aho
(e-mail address removed)

 

[Pavel Romashkin]

I think it would be easier to help if we knew the purpose of the
database. I don't have enough information on how your tables should be
built to properly function together.
What data are you going to enter and what subjects do you have
(Patients, Doctors, Appointments for example)?

Pavel

 

[Guest]

ok I guess the main problem I am having is duplicate
enteries
I want to be able to add new people that have the same
doctor via a dropdown list
when I do it I get an error saying no duplicates
when I file out the info for form1(main)the socsecurtiy#
appears already filled in on the subform
then I want to select a doctor
some people have the same doc
how is this done
again thanks for any help

ps I am just doing this to learn

 

[Guest]

can anyone help me
please

 

[Pavel Romashkin]

I am still not clear on your data structure. It is essential to get that
correct in order for your forms to work.

Your Patient table needs to have Patient related information:

PatientID - primary key, Autonumber (PK, AN)
FirstName
LastName
SSN
PrimaryDoctor -> foreign key from Doctors table
PhoneNumber .... etc.

Doctors table:

DoctorID - PK, AN
FirstName
LastName
Clinic ... etc.

Appointments table - it will link the Patients table with Doctors table.

AppointmentID - PK, AN
PatientID - FK
DoctorID - FK

Base your form on Appointments table. Use a combo box to select a
Patient and another combo box to select a doctor. You will not have
duplicate entry problems.

Pavel