Beginner - help with two tables and the relationship

[Simon Lang]

I know this will all fall into place at some point. I have a "contacts" table
and form and a "contact notes" table and form. I want to place the "notes"
form as a sub form (datasheet) on the "contacts' form so people can make ad
hoc notes about the client etc. this table is just a date and text field. The
two tables have the auto id as the primary key. This relationship is one
(client) to many (clients notes)?. and is the auto id sufficient. I seem not
to be able to keep the data together or it doesn't save. I'd greatly
appreciate a simple walkthrough because quite frankly, I'm doing something
wrong and it's doing my head in, many thanks in advance

 

[golfinray]

An autonumber ID gives you no way of connecting the two tables. Auto ID 41
may be Robert jones in one table and Carrie Smith in another. You probably
need to have a contact ID # in both tables to match the contacts. Then your
subform would link by contact id#.

 

[Wayne-I-M]

Hi Simon

You need 2 tables (as you have)
tblContacts and tblNotes (both of these need a unique primary field (as you
have ID)

Open the Contacts Table in design view and add a new field call it NotesID
(format should be number)

Open the relationships window and drag the ID field from tblContact into
tblNotes and place it over the new field you have just created. This will
create the relationship for you.

Next, just an idea, use the form wizard to create a form for you (just so
you can see how it all works). Create the form (in the wizard) with all
fields from both tables. You will see the option to create a subform.

Open the form in design view and you will be able to see how it's linked (on
the Contact primary field)

Good luck

 

[Wayne-I-M]

ooops

Sorry - should be the other way around (am getting too old for this).

You need to add another field to the Notes table - not the contacts. So
that each note is linked to a contact (one to many - one contact can have
many notes). So, open the NOTES table in design view and add a new field
called ContactID - number format - not the other way around like I said to
start with.

ooops

 

[Simon Lang]

Thanks Wayne and Milton this is greatly appreciated, it's falling into place
thank god!

to clarify:

contacts table has auto id/primary key

Notes table has new field "notes id"

related the two together as one to many

cool thank you

just a note, if I make the new "notes id" a primary key, it only gives me a
one to one, is this to be expected

cheers
Simon

 

[golfinray]

If you have only one not per contact you would get one-to-one. You should
probably try to get a one-to-many so that many notes could be added to any
one contact. Good luck, Milt

 

[John W. Vinson]

Thanks Wayne and Milton this is greatly appreciated, it's falling into place
thank god!

to clarify:

contacts table has auto id/primary key

Notes table has new field "notes id"

related the two together as one to many

cool thank you

just a note, if I make the new "notes id" a primary key, it only gives me a
one to one, is this to be expected

You need *TWO* fields in the Notes table. Reread Wayne's second message!

The Notes table should have a NotesID (which could be an Autonumber), as its
Primary Key, to uniquely identify each note.

The Notes table should ALSO have a ContactID field, a Long Integer, to
identify the contact for whom this note was created.

Your Form/Subform would use the ContactID as the Master/Child link field to
display multiple notes for each contact.

It's handy to also include a Date/Time field in the Notes table, with a
Default Value of =Now(), to timestamp each note at the moment it's created.