Relationships problems

  • Thread starter Thread starter Jack Sheet
  • Start date Start date
J

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
 
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.
 
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"?
 
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>)
 
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.
 
Back
Top