Relationships problems

[Jack Sheet]

Hi all

Access 97



I have two tables: T_Clients and T_Tasks.

They share fields ID_Clients and Ref

ID_Clients is the primary key in T_Clients, and is formatted as autonumber
in T_Clients

In T_Tasks, ID_Clients is formatted as a long integer.

There is a one to many relationship between T_Clients.ID_Clients (one) and
T_Tasks.ID_Clients (many)



T_Tasks.ID_Tasks is the primary key field of T_Tasks formatted as autonumber
(unlikely to be relevant to the remainder of this post).



The field "Ref" is formatted as text (in each table).



The values of T_Clients.Ref are unique in each record of T_Clients. Indeed
T_Clients.Ref could have been the primary key of T_Clients were it not for
the fact that ID_Clients is the primary key.



The main reason that I have a separate field ID_Clients designated as
primary key is because I want to grant the user the option to change the
value of T_Clients.Ref but I don't want them messing with the primary key.



Problem 1: If I use a form to amend the value of T_Clients.Ref in a record,
how do I prevent it from accepting a duplicate entry, ie if some other
record in T_Clients already contains the same value of Ref as that attempted
to be selected by the user?



There is in reality a one-to-one relationship between T_Clients.ID_Clients
and T_Clients.Ref, since both fields contain unique values within the table.

Problem 2: I have not been able to set up that one-to-one relationship
within Access. I opened the Relationships window, then opened two versions
of T_Clients table within it, so that it shows T_Clients and T_Clients_1. I
then tried to drag ID_Clients from T_Clients to Ref in T_Clients_1, enforce
referential integrity, and encountered two problems: One was that it
claimed that I was trying to create a one-to-many relationship (I have
tested T_Clients and confirmed that there are no duplicate entries of either
ID_Clients or Ref). The other is that it displayed error 3368, advising
that they must be formatted as the same data type. I cannot understand why
this is necessary, but given that it is necessary, I do not know how to
overcome it.



Any help with either of these would be appreciated.



Thanks

 

[Douglas J. Steele]

For question 1, you can create a unique index on T_Clients.Ref (as opposed
to making it the primary key)

For question 2, fields in a table don't have relationships to one another.
Relationships are strictly between two tables.

 

[Jack Sheet]

Thanks Douglas

Re. Question 2, how do you reconcile your response with that of Karl Dewey
dated 16 November 2005 at 14:18 in the thread "Create a relationship between
a table and itself"?

 

[Douglas J Steele]

You can create a relationship between a table and itself, but that's for the
purpose of linking one row in the table to other rows in the same table, not
to link one field in a row to another field in the same row. (In actual
fact, it's a violation of database normalization theory to have a field that
can be completely derived from other fields in the same row).

Common examples of a self-join would be an employee table, where you could
have a relationship to the employee table representing the employee's
manager, or family trees, where you can have a relationship for "Father" and
another for "Mother"

If you look in the Northwind database that came with Access, the Employee
table has a "Reports To" field. Realistically, they didn't implement it
properly. Rather than having

1 Davolio, Nancy Fuller, Andrew
2 Fuller, Andrew
3 Leverling, Janet Fuller, Andrew
4 Peacock, Margaret Fuller, Andrew
5 Buchanan, Steven Fuller, Andrew
6 Suyama, Michael Buchanan, Steven
7 King, Robert Buchanan, Steven
8 Callahan, Laura Fuller, Andrew
9 Dodsworth, Anne Buchanan, Steven

(where the three columns represents the existing EmployeeId field, a
concatenation of the LastName and FirstName fields and the existing
ReportsTo field), they should have used ManagerId (which would hold the
EmployeeId of the manger):

1 Davolio, Nancy 2
2 Fuller, Andrew
3 Leverling, Janet 2
4 Peacock, Margaret 2
5 Buchanan, Steven 2
6 Suyama, Michael 5
7 King, Robert 5
8 Callahan, Laura 2
9 Dodsworth, Anne 5

Then, you could join the Employees table to itself to ensure that no one is
set up as a Manager who isn't an Employee. (Hopefully the company wouldn't
want Cascade Delete set up on that relationship, or else all employees of a
manager could end up being deleted when the manager got fired! <g>)

 

[Jack Sheet]

Thanks for that. It clarifies matters.

The reason I was getting into a pickle is that I had included ID_Clients as
a field in T_Tasks (as a long integer). ID_Clients is the (autonumber)
primary key in T_Clients.
This appeared to cause a problem because when I create a new task record in
T_Tasks, I entered the client reference (field "Ref") but the ID_Clients
field was not updated, and since I had enforced referential integrity it
would not save the new record. VLOOKUP function in Excel would have
identified the ID_Clients field from the Ref field by looking up the
corresponding value in T_Clients table. But I am just migrating from Excel
and clearly got the approach wrong. I had intuitively figured that had I
created a one-to-one relationship between the T_Clients.Ref field and
T_Clients.ID_Clients field, then by simply entering a value in T_Tasks.Ref
so the program would autopopulate the value of T_Tasks.ID_Clients by reason
of the one-to-many relationship between T_Clients.Ref and T_Tasks.Ref in
conjunction with the the one-to-one relationship between C_Clients.Ref and
T_Clients.ID_Clients. Obviously I was barking up completely the wrong tree.
In a desert. Without an oasis. On Pluto.