Which field to include?

J

Jack Sheet

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.

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.
 
V

Vincent Johns

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.
 
J

John Vinson

I am setting up a simple query table Q_Tasks that collects all of the fields
from T_Clients and from T_Tasks

Don't.

Such a query will be awkward to use - it will show duplicate values
for each Client who has multiple Tasks, for instance; and it will be
awkward to update.

Instead, use the tools that Access provides - Forms. Create a Form
based on T_Clients, and a subform based on T_Tasks, linked by the
client ID.

This will let you enter a new client on the mainform, and a new task
or tasks for that client on the subform.

John W. Vinson[MVP]
 
J

Jack Sheet

John Vinson said:
Don't.

Such a query will be awkward to use - it will show duplicate values
for each Client who has multiple Tasks, for instance; and it will be
awkward to update.

Instead, use the tools that Access provides - Forms. Create a Form
based on T_Clients, and a subform based on T_Tasks, linked by the
client ID.

This will let you enter a new client on the mainform, and a new task
or tasks for that client on the subform.

John W. Vinson[MVP]

Thanks. Am going down that route now.
 
J

Jack Sheet

Vincent Johns said:
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.

Thanks for the suggestions, and it will take me a while to digest it all,
but in the meantime I just wanted to comment on the above paragraph.

Throughout our organisation we have historically identified clients by an
alphanumeric reference code. This is used in several systems, both manual
and computerised (albeit not in Access). I am loath to depart from that
method of identifying clients within the Access database that I am now
constructing, for two reasons:
1) For ease of cross-referencing Access records with other systems within
the office that are based on the existing alphanumeric reference system, and
2) We have some clients who share the same name (and even initials), so we
need some unique identifier.
I am sure that you will agree that it would not be sensible to ask users to
identify unique clients by reference to the meaningless, unmemorable,
autonumber primary key ID_Clients, and the received wisdom appears to be to
hide the autonumber field from users generally.
That is why in my T_Clients database I have two uniquely indexed fields
ID_Clients and ClientRef, the former being the autonumber primary key field,
and the latter being used by users to select and distinguish clients in
forms and reports.

Thanks
 
V

Vincent Johns

Jack said:
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.


Thanks for the suggestions, and it will take me a while to digest it all,
but in the meantime I just wanted to comment on the above paragraph.

Throughout our organisation we have historically identified clients by an
alphanumeric reference code. This is used in several systems, both manual
and computerised (albeit not in Access). I am loath to depart from that
method of identifying clients within the Access database that I am now
constructing, for two reasons:
1) For ease of cross-referencing Access records with other systems within
the office that are based on the existing alphanumeric reference system, and
2) We have some clients who share the same name (and even initials), so we
need some unique identifier.
I am sure that you will agree that it would not be sensible to ask users to
identify unique clients by reference to the meaningless, unmemorable,
autonumber primary key ID_Clients, and the received wisdom appears to be to
hide the autonumber field from users generally.
That is why in my T_Clients database I have two uniquely indexed fields
ID_Clients and ClientRef, the former being the autonumber primary key field,
and the latter being used by users to select and distinguish clients in
forms and reports.

Thanks

I think that makes excellent sense. I had a discussion recently with
someone who argued for defining as a Primary Key a field (or set of
fields) which had some intrinsic meaning. Well, OK, I suppose it saves
space, but my contention was that I like to use a separate field for
linking and let the meaningful fields do their other useful work. (This
allows one to edit the meaningful field, if necessary, without goofing
up the links and having to edit them, too.)

For your example, I figured that there were probably several other
fields that I didn't care about for now, so I just deleted those that
were irrelevant to me for the moment. That doesn't mean I thought there
was anything wrong with them, just that I didn't need them for the
example, and I'm sorry to have suggested that you didn't need them, either.

However, it appears that, if your Subform is linked to your main Form by
some other field, and you need some automatic way to set a value for
[ClientRef] in your primary Table, you might need to write some code
(Update Query or VBA or something) to set the value. I think you
indicated that users are likely to work on the Subform records without
having any knowledge of the primary Table's record to which they are
linked, and I hope you can find a way to avoid that -- you probably want
all the links to be valid as much as possible.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
V

Vincent Johns

Sorry, I just answered this, but another thought occurred to me...

Jack said:
That is why in my T_Clients database I have two uniquely indexed fields
ID_Clients and ClientRef, the former being the autonumber primary key field,
and the latter being used by users to select and distinguish clients in
forms and reports.

It makes sense to me that you have [ClientRef] in the [T_Clients] Table,
but why keep a copy of that same field anywhere else? It's always
available to a [T_Tasks] record via the [ID_Clients] foreign key, so
[T_Tasks].[CLientRef] seems to be redundant, and therefore a source of
unnecessary extra work in maintaining your Tables. Normalizing your
Tables (by zapping redundant data), at least to a moderate extent, can
save you time & effort later, as you will have to add, delete, or update
information only one place instead of two or more.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
J

Jack Sheet

Vincent Johns said:
Sorry, I just answered this, but another thought occurred to me...

Jack said:
That is why in my T_Clients database I have two uniquely indexed fields
ID_Clients and ClientRef, the former being the autonumber primary key
field, and the latter being used by users to select and distinguish
clients in forms and reports.

It makes sense to me that you have [ClientRef] in the [T_Clients] Table,
but why keep a copy of that same field anywhere else? It's always
available to a [T_Tasks] record via the [ID_Clients] foreign key, so
[T_Tasks].[CLientRef] seems to be redundant, and therefore a source of
unnecessary extra work in maintaining your Tables. Normalizing your
Tables (by zapping redundant data), at least to a moderate extent, can
save you time & effort later, as you will have to add, delete, or update
information only one place instead of two or more.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Thanks.

I think that the only reason why I duplicate the ClientRef field in the
T_Tasks table is that I want queries, reports and comboboxes based on
T_Tasks to be capable of being sorted by ClientRef. It may still be
unnecessary, but I am having a bit of bother with the combo boxes at
present, which will be subject of a separate post :)
 
J

John Vinson

I think that the only reason why I duplicate the ClientRef field in the
T_Tasks table is that I want queries, reports and comboboxes based on
T_Tasks to be capable of being sorted by ClientRef. It may still be
unnecessary, but I am having a bit of bother with the combo boxes at
present, which will be subject of a separate post :)

If your ClientRef is a) Unique, b) Stable, and c) reasonably small, I
see no reason to use an Autonumber ID at all. Simply use IT as the
Primary Key and as the foreign key to other tables. You can still use
it in Combo Boxes; in fact it's easier since you need one fewer
columns in the combo!

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query produces unpredicted result 1
Update query syntax 2
Action query - help please 10
Subform problem 8
Relationships problems 4
Think I messed up 5
Update query syntax help 2
Append query syntax requested 3

Top