Please Help With Second Table

[CEVisker]

I have created my first table(Clients) that has the client info, and
form to input information in the table. Primary key is there SSAN.

My second table is going to be Illness and Injury information, whic
will have just 4 feilds: [auto number], [SSAN], [date], and [details]
Every time a client has an illness or injury, I will use a form t
input the date and a brief description of the issue. The details fiel
will be a memo field.

The SSAN field in each table will be the relationship. The auto numbe
is used because obviously each client will have multiple records i
this table.

Questions: Am I doing this right? How do I create a form to enter thi
info into the table? What I would like is a form where I can select th
clients name from a dropdown list and then enter info about the ne
illness or injury. There are approximately 120 clients. In time, I ca
see this table having 10's of thousands of records. Will this be a
issue?

I appreciate any help, this is my first database.

Thank You,

Cha

 

[Gary Miller]

This is what a subform is designed for. Do a little reading
in Help on them or look at the sample Northwinds.mdb that
came with Access.

In a nutshell you need to create a small simple form to
enter the I&I info. You need to have the autonumber and SSAN
in the recordset, but not on the form itself. On your main
form, turn your control wizard on and drop a subform control
in the detail section of your Clients form. The wizard will
ask which form to use ( the new one ) and then what are the
Master/Child linking fields (SSAN) and then it will set the
form up for you. You will probably need to play with sizing
the subform a bit.

Now drop a combobox control in your form header and the
wizard will help you build a combobox that will find records
in your form based on the selection.

Good Luck!

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[CEVisker]

I am not sure if this is exactly what I want to do. I will be addin
more tables in the future that will hold different information abou
the clients. From what I understand you are telling me, is that I woul
use the same form to input all the information with sub-forms? Am
understaning this right?

Thanks for your response Gary,

Cha

 

[Gary Miller]

Chad,

It is very common to have tables that have varying
relationships to other tables. In fact, it is fundamental to
good database design. It is also fairly normal to have more
than one subform on a given form or to pull in related items
through the use of combo and list boxes.

I think that you are understanding me correctly. If you have
a main form that relates to 'child records', ie... an Order
with many line item OrderDetails, it is very normal to have
a main form for the order and then a subform that displays
and allows you to add additional line items ( Order
Details ) as needed. This is a Parent/Child relationship
with the Order being the Parent and the Details being the
Children.

With Access, one of the tremendous advantages of using a
subform is that when you create a new record in the child
records in the subform, Access will take care of entering in
that crucial key linking field, which is known as a Foreign
key in the child table. This foreign key is actually the
Primary Key of the Parent table. This is the real crux of a
relational database system.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________