Relationships....please advise

[Guest]

I have three tables
Clients, Products and Suppliers.
The Clients Primary key is ClientID
The Products and Suppliers have the Clients table "ClientID" as a Foreign Key.
I have established a One to Many relationship from....
Clients to Products and
Clients to Suppliers.

Question 1
When I enter data into an Autoform for each table The Client ID does not
seem to be shown in either the Products table or Suppliers table......I
though this would occur by creating the relationship.....please advise.

Question 2
When I try to enter data into each table through separate ayutoforms, I get
a message telling me I can't enter data into the underlying tables because
they are related!!! Please, can anyone let me know what I am overlokking here?
Thanks
Dermot

 

[John Vinson]

I have three tables
Clients, Products and Suppliers.
The Clients Primary key is ClientID
The Products and Suppliers have the Clients table "ClientID" as a Foreign Key.
I have established a One to Many relationship from....
Clients to Products and
Clients to Suppliers.

Do you really have a one to many relationship? That would imply that
each Client may (purchase? use? sell?) many Products, but that each
Product is related to one and only one client. Is that in fact the
case? It would be far more common to have a many to many relationship,
in which each Client could buy (I'll assume it's buy) many Products,
and each Product can be bought by many Clients. For this you need *a
third table* to resolve the many to many relationship.

Question 1
When I enter data into an Autoform for each table The Client ID does not
seem to be shown in either the Products table or Suppliers table......I
though this would occur by creating the relationship.....please advise.

That's NOT what relationships are for. They don't automagically fill
in data in any table - they do the OPPOSITE, in that they *prevent*
you from entering data which would create an "orphan" record.

Question 2
When I try to enter data into each table through separate ayutoforms, I get
a message telling me I can't enter data into the underlying tables because
they are related!!! Please, can anyone let me know what I am overlokking here?
Thanks
Dermot

You're overlooking - or misunderstanding - how relationships work.

I'd suggest you take a look at some of the resources at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html,
especially the Database Design 101 links.

In short, you'll almost surely need two new tables (with ClientID and
ProductID as the joint Primary Key for one, and ClientID and
SupplierID as the joint Primary Key for the other); and you'll want to
use a Form with two Subforms, not an Autoform, to enter the data.

John W. Vinson[MVP]

 

[Guest]

Dear John,

Thank you for you patience.

I have explained myself very badly. It was late and..........no excuses!!!!
I do understand the relationships, but used a very poor example....stupid
actually.

My question was more about how do I enter data into related tables?
I think you answered that by saying I need to create an entry form with two
subforms.

Can you explain a little further the difference in the mechanics of entering
data into a single table and that of entering data into related tables that
would help me understand why it has to be done using subforms and not
autoforms?

Thanks in advance
Dermot

 

[John Vinson]

Can you explain a little further the difference in the mechanics of entering
data into a single table and that of entering data into related tables that
would help me understand why it has to be done using subforms and not
autoforms?

Well... it doesn't HAVE to be done using subforms. But doing so makes
it a WHOLE LOT easier.

The desired end result in a typical one to many relationship is that
you can enter a new record into the "one" side table, assigning a
Primary Key value which uniquely identifies that record; then you
would enter one or more records into the "many" side table. Each of
these records must have the value just entered as the one side table's
Primary Key into the child table's Foreign Key field - the field upon
which the tables are linked.

A Subform in Access handles assigning the foreign key for you
automatically. The Subform Control has a "Master Link Field" which
would be the Primary Key of the "one" side table; and a "Child Link
Field" which would contain the name of the related field in the
subform's table. If the mainform has (say) an EmployeeID field as the
primary key of its table, and the subform is based on an Hours table
in which you're entering the hours worked by that employee, the
EmployeeID would be the master and child link fields; when you enter a
number of hours into the subform, it will automatically fill in the
current EmployeeID.

If you try to enter this information using standalone "autoforms",
then you must remember and retype the employeeID for each record in
the second table. You can do it, of course, but it's a hassle and very
prone to error!

John W. Vinson[MVP]

 

[Guest]

Dear John
Thanks for that thorough explanation.
I have had a look at the link you supplied.....nice link thanks.
Have a good day
Dermot