Jack said:
Hi all, Access 97
I have two tables, T_Clients and T_Tasks.
T_Clients has an autonumber primary key field ID_Clients
T_Clients also has an indexed field ClientRef (text) (no duplicates allowed)
T_Tasks has an autonumber primary key field ID_Tasks
T_Tasks also has a field CLientRef (text).
There is a one to many relationship between T_Clients.ClientRef and
T_Tasks.ClientRef.
I am setting up a simple query table Q_Tasks that collects all of the fields
from T_Clients and from T_Tasks (with the possible exception of
T_Clients.ID_Clients) and all of the records from T_Tasks.
I am undecided on which of T_Clients.ClientRef or T_Tasks.ClientRef to
include in the query table (given that duplication appears to be
superfluous) or even
whether it is likely to matter. If the answer is "it depends" then I should
be grateful for some pointers as to the criteria affecting this decision.
I initially set up Tables as you described, but finding no value to
having duplicate keys [T_Clients] and [ClientRef], and preferring the
name [T_Clients] as being more suggestive of the Table whose primary key
it it, I deleted [ClientRef]. You might have other purposes for it, but
for this message I wanted to concentrate on the linkages.
So, here's my version of your Tables (which, BTW, I had no trouble at
all in populating, except when I tried to enter a task for a nonexistent
client, and I'm not sure why you want to do that):
[T_Clients] Table Datasheet View:
ID_Clients Name
----------- ------
-1909701337 Casey
-1770059631 John
-1428168597 Herc
[T_Tasks] Table Datasheet View:
ID_Tasks Description ID_Clients
----------- ------------------------ -----------
-1968623140 Reaching station on time -1909701337
-1920373843 Getting new hammer -1770059631
-1522088066 Beating steam drill -1770059631
1891275984 Cleaning Aegean stables -1428168597
I linked these via the [ID_Clients] field and specified enforcing
referential integrity. (I did this before entering any data, but the
values I listed here should give no trouble.)
Since it's kinda ugly to have to look at raw Autonumber key values,
which I usually use only for linking Tables, I defined a Lookup Query as
follows:
[QL_Clients] SQL:
SELECT T_Clients.ID_Clients,
(Abs([T_Clients]![ID_Clients]) Mod 10)
& " " & [Name] AS LName
FROM T_Clients
ORDER BY T_Clients.Name;
This lists all the names. The displayed names in a Lookup Query should
be unique, so that you know if you've selected the correct record, but
they should also be brief to save real estate on the screen. In this
case, I added a digit from the key value to make the displayed value
unique; you might also add parts of other fields from the primary Table.
(I almost invariably define Lookup Fields for all my foreign keys,
such as [T_Tasks].[ID_Clients].)
[QL_Clients] Query Datasheet View:
ID_Clients LName
----------- --------
-1909701337 7 Casey
-1428168597 7 Herc
-1770059631 1 John
There's only one foreign key, [T_Tasks].[ID_Clients], and to apply the
Lookup Query to it, we open [T_Tasks] in Table Design View, select
[ID_Clients], select the Lookup tab, and change the following properties:
Display Control = List Box
Row Source = QL_Clients
Column Count = 2
Column Widths = 0;1
Now the appearance of [T_Tasks] makes more sense:
[T_Tasks] Table Datasheet View:
ID_Tasks Description ID_Clients
----------- ------------------------ ----------
-1968623140 Reaching station on time 7 Casey
-1920373843 Getting new hammer 1 John
-1522088066 Beating steam drill 1 John
1891275984 Cleaning Aegean stables 7 Herc
At this point, it makes sense to define a Form (perhaps via the Form
Wizard) for [T_Clients] with a linked Subform for [T_Tasks]. Having
done that, I hid the fields that display key values, trusting Access to
keep track. If you have to edit key values manually, there might be
something wrong with your database design, not just the data.
Now let's display the Table contents.
[Q_Tasks] SQL:
SELECT T_Clients.*, T_Tasks.ID_Tasks,
Left$([Description],15) & "..." AS [Short Descr],
T_Tasks.ID_Clients
FROM T_Clients INNER JOIN T_Tasks
ON T_Clients.ID_Clients = T_Tasks.ID_Clients
ORDER BY T_Clients.Name, T_Tasks.Description;
[Q_Tasks] Query Datasheet View:
T_Clients.ID Name ID_Tasks Short Descr T_Tasks.ID
_Clients _Clients
------------ ----- ----------- ------------------ ----------
-1909701337 Casey -1968623140 Reaching statio... 7 Casey
-1428168597 Herc 1891275984 Cleaning Aegean... 7 Herc
-1770059631 John -1522088066 Beating steam d... 1 John
-1770059631 John -1920373843 Getting new ham... 1 John
Notice that both versions of [ID_Clients] are visible here, one (the
foreign key) using the lookup value, the other (the primary key) showing
the raw Autonumber key value.
An even shorter version of this Query, referring only to the [T_Tasks]
Table, displays the lookup values, avoiding those annoying key numbers:
[Q_Tasks_Short] SQL:
SELECT [T_Tasks].[Description],
[T_Tasks].[ID_Clients]
FROM T_Tasks
ORDER BY [T_Tasks].[Description];
[Q_Tasks_Short]
Description ID_Clients
------------------------ ----------
Beating steam drill 1 John
Cleaning Aegean stables 7 Herc
Getting new hammer 1 John
Reaching station on time 7 Casey
In your Subform, which you can base on [Q_Tasks_Short] if you wish, the
format of the foreign key [Q_Tasks_Short].[ID_Clients] is irrelevant,
since it's the field that links the Subform to the Form displaying
[T_Clients] data and is hidden.
Incidentally, I don't know whether this is correct, and it may be irrelevant
to the above question, but my reason for excluding T_Clients.ID_Clients from
the query is that I shall want the user to have the power to add records to
T_Tasks and my first attempt at this failed because it would not permit me
to add records without completing the value for ID_Clients whose value I
could not know although it should theoretically be derivable from the
user-entered ClientRef value.
Thanks.
I suggest that either you NOT allow users to enter records into
[T_Tasks] without first specifying some client in [T_Clients], or you
remove the Referential Integrity requirement. (But if you take the 2nd
option, you'll have some extra maintenance, such as having to figure out
where to attach those anonymous [T_Tasks] records.)
An intermediate measure, which you might find useful, is to provide a
special client named "(Other)" or "(Unknown)" or "(To Be Determined)",
to whose records you allow your users to attach any [T_Tasks] records
that you can't figure out where else to put. You'll still have
maintenance problems, but at least you'd be able to maintain Referential
Integrity for this particular link.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.