ffisher said:
Hi Larry
Would you clarify this for me please. Your Quote:
"then the system will take care of writing in the Foreign Key value for
you."
At what point can I expect then to see the value of the PK from the Parent
table showing in the FK of the Child table?
My understanding is that one should first create tables, add fiields,create
the PKs, then duplicate PK names for fields in the child tables (they become
the FKs) then link then via the relationship window.
Sort of. You can use the Relationships window to SPECIFY that certain
fields be linked, but the actual linking takes place when you add
records to the Tables.
All this done BEFOR adding data.
Some of it is done before adding data. Creating the keys and copying
the values, however, is usually done at the same time as data are added.
Data can then be added either via direct table entry OR via a form.
OR via a Query. Of these, Forms are the most versatile, but they take a
bit of extra effort to set up. (There is a Form Wizard available to
help with that.)
My advice (warning: not everyone here agrees with me) is to set up a
Query for each primary key that you plan to use as a foreign key. For
example, suppose you have the following Tables:
[Parent] Table:
ParentID Mother Father
---------- ------ ----------
1521310868 Mary Joe
1947344352 Zelda Murgatroyd
[Child] Table:
ChildID Name ParentID
----------- ------ -----------
1188153275 Susan 1947344352
724657378 Jim 1521310868
-223986171 John 1521310868
Then you can define a lookup Query on the [ParentID] field that might
have this SQL:
[Q_LookupParent]
SELECT Parent.ParentID,
[Parent]![Mother] & " and "
& [Parent]![Father] AS Parents
FROM Parent
ORDER BY Parent.Father;
In Query Datasheet View, it would look like this:
ParentID Parents
----------- ---------------------
1521310868 Mary and Joe
1947344352 Zelda and Murgatroyd
In the [Child] Table, you could then set the properties of the foreign
key [ParentID] as follows (after selecting the Lookup tab):
Display Control: List Box
Row Source: Q_LookupParent
Column Count: 2
Column Widths: 0;2
Having done that, the appearance of [Child] in Table Datasheet View
would look like this:
[Child]
ChildID Name ParentID
---------- ------ --------------------
1188153275 Susan Zelda and Murgatroyd
724657378 Jim Mary and Joe
-223986171 John Mary and Joe
.... and I would be inclined to hide the primary key fields of both
Tables ([Child].[ChildID] and [Parent].[ParentID]), since the key values
are meaningless to human beings. Incidentally, the underlying data are
not changed ([Child].[ParentID] still contains those long numbers); only
the appearance has changed to make reading & data entry easier.
If you link the two [ParentID] fields in the Relationships window,
Access will likely set up a subdatasheet in the [Parent] Table (small +
sign at the left end of each record) allowing you to quickly display the
linked records in [Child].
With primary keys hidden in both Tables and with subdatasheets enabled
and expanded, the result might look a bit like this:
Mother Father
------ ------
Mary Joe
Name
-----
John
Jim
Zelda Murgatroyd
Name
-----
Susan
When should I start seeing the values in the FK during the above process?
ffisher
If you do as I suggested, you'd see them when you click on the (empty)
[ParentID] field in a new record in [Child]. A list will appear from
which you can choose the desired value, or you may type the name (not
the key number) there.
As I mentioned above, once you have this working, I suggest setting up a
Form to facilitate your data entry, as that will give you more freedom
in specifying how data can be entered. But entering directly into a
Table will give you a quick-and-dirty basic capability, and sometimes
that's all you'll need.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
