setting relationship/or query

  • Thread starter Thread starter David Whitaker
  • Start date Start date
D

David Whitaker

I have got 2 tables that I am using a relationship of one to many on.

Problem
How would you assign an existing record of the many side to another record
on the one side

example
if you had in table1 in the fruits field
Turnips
which had multiple entries associated from table2 like
bushel
peck
quart

how would you move 1 of the 3 items from table2 without changing the others
to another record in fruits in table1?
Like
Turnips
bushel
peck
Oranges
quart

Confusing Day???
 
What you have is now a many-to-many relationship, since one fruit can have
many units, and a unit can be an attribute of many fruits.
You solve this with a junction table, having entries for both fruits and
units.

tblFruits
FruitID - primary key
FruitName

tblUnits
UnitID - primary key
UnitName

tblFruitUnit
FruitID - foreign key to tblFruits
UnitID - foreighn key to tblUnits

This table can either have a separate primary key, or you can define the key
based on BOTH fields. That ensures, amongst other things, that one fruit
cant have the same multiple unit attibutes.

Then for each Fruit which has a Unit, you put another entry in the
tblFruitUnit table, with the FruitID and the UnitID from the two defining
tables.
Then its easy to write queries joining tblFruit with tblFuitUnit to extract
the fruits and their multiple units. Same with the Units-FruitUnits side.


--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Hi David

Table 2 (the many side) must have a field which contains the value of the
primary key from the related record in Table 1. This field is called the
"foreign key" in the relationship. To point a "child" record to a different
"parent", you simply change the value of the foreign key field.

In your example, the "quart" record would have in its FK field the PK of the
"Turnips" record. Just change it to the PK of the "Oranges" record and
you're done.
 
That did it!!!!
Thanks

Graham Mandeno said:
Hi David

Table 2 (the many side) must have a field which contains the value of the
primary key from the related record in Table 1. This field is called the
"foreign key" in the relationship. To point a "child" record to a different
"parent", you simply change the value of the foreign key field.

In your example, the "quart" record would have in its FK field the PK of the
"Turnips" record. Just change it to the PK of the "Oranges" record and
you're done.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


David Whitaker said:
I have got 2 tables that I am using a relationship of one to many on.

Problem
How would you assign an existing record of the many side to another record
on the one side

example
if you had in table1 in the fruits field
Turnips
which had multiple entries associated from table2 like
bushel
peck
quart

how would you move 1 of the 3 items from table2 without changing the others
to another record in fruits in table1?
Like
Turnips
bushel
peck
Oranges
quart

Confusing Day???
Click to expand...
Click to expand...
 
Back
Top