Think I messed up

[Jack Sheet]

Hi all, Access 97

I changed some relationships and deleted a field that I no longer required,
and it has caused no end of problems summarised below:

Position at outset:
T_Clients.ID_Clients has one to many relationship with T_Tasks.ID_Clients
Q_Tasks_with_name collects various fields from T_Clients and T_Tasks
including T_Clients.ID_Clients

Changes made by me:
1) Relationship as above deleted.
2) New relationship created T_Clients.Ref one-to-many with T_Tasks.Ref
3) Field T_Tasks.ID_Clients deleted.
4) Column in Q_Tasks_with_name that refers to ID_Clients deleted.

Symptoms of problem:
When I run Q_Tasks_with_name, in every record it only selects the first
record from T_Clients in each field that refers to T_Clients.
When I open Q_Tasks_with_name in design view, the tables T_Clients and
T_Tasks are NOT linked by a line between the fields "Ref", despite that when
I open the relationships window it Is shown with a 1 to infinity line.

I tried running repair database in tools and problem persists. The
underlying tables themselves do not appear to be corrupt, but the Query
still generates the above. Any way to extricate myself from this mess?

 

[Tom Wickerath]

Hi Jack,

Any way to extricate myself from this mess?

Sure....restore from your latest backup. That would certainly be the
quickest and easiest method. Are you wanting to restore the original schema?

Did you create the Ref field in each table, or was this an existing field?
What is the data type for this field. Do all records in both tables have a
value entered in this field? When you open the relationships view and
double-click on the join line, is the relationship shown as one-to-many, or
is it shown as indeterminant?


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________


:

Hi all, Access 97

I changed some relationships and deleted a field that I no longer required,
and it has caused no end of problems summarised below:

Position at outset:
T_Clients.ID_Clients has one to many relationship with T_Tasks.ID_Clients
Q_Tasks_with_name collects various fields from T_Clients and T_Tasks
including T_Clients.ID_Clients

Changes made by me:
1) Relationship as above deleted.
2) New relationship created T_Clients.Ref one-to-many with T_Tasks.Ref
3) Field T_Tasks.ID_Clients deleted.
4) Column in Q_Tasks_with_name that refers to ID_Clients deleted.

Symptoms of problem:
When I run Q_Tasks_with_name, in every record it only selects the first
record from T_Clients in each field that refers to T_Clients.
When I open Q_Tasks_with_name in design view, the tables T_Clients and
T_Tasks are NOT linked by a line between the fields "Ref", despite that when
I open the relationships window it Is shown with a 1 to infinity line.

I tried running repair database in tools and problem persists. The
underlying tables themselves do not appear to be corrupt, but the Query
still generates the above. Any way to extricate myself from this mess?

 

[Jack Sheet]

Thanks Tom

The ref fields already existed (data type Text), all records completed with
no Null entries.

Somehow I seem to have solved the problem, by creating the missing
one-to-many relationship by dragging the word Ref from the T_Clients table
to the T_Tasks table as displayed in the query design view. Why that
relationship was not shown at the outset, when it is there in the
relationships window, I do not know, but as problem seems to be solved for
now I shall just cross my fingers and hope for the best.

 

[Tom Wickerath]

Hi Jack,

Yes, I just read your earlier post, (Subject: Relationships problems), where
you indicated that the Ref field was text. In answer to one part of Problem 2
that you had in that post:

I then tried to drag ID_Clients from T_Clients to Ref in T_Clients_1,
enforce referential integrity, and encountered two problems:
(snipped 1st problem)
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...

You were trying to create a relationship between a numeric field and a text
field. Both fields in a relationship must be the same data type.

Personally, I would not have eliminated the autonumber primary key and long
integer foreign key, especially since you stated that you wanted to allow the
user the ability to change the values in the Ref field. A primary key should
never need to be changed. If you use a meaningless key, such as an
autonumber, there should never be a reason to change it. You could have set a
unique index on the Ref field in the T_Clients table, in order to ensure that
it is not duplicated. As it is, you will need to enable the Cascade Update
option in order to allow someone to change the value. You might want to read
what Access MVP Tony Toews has to say on this subject. See the following page:

http://www.granite.ab.ca/access/cascadeupdatedelete.htm



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________


:

Thanks Tom

The ref fields already existed (data type Text), all records completed with
no Null entries.

Somehow I seem to have solved the problem, by creating the missing
one-to-many relationship by dragging the word Ref from the T_Clients table
to the T_Tasks table as displayed in the query design view. Why that
relationship was not shown at the outset, when it is there in the
relationships window, I do not know, but as problem seems to be solved for
now I shall just cross my fingers and hope for the best.

 

[Jack Sheet]

Thanks Tom.

Just to clarify - I never deleted a primary key. ID_Clients (autonumber)
was and remains the primary key of T_Clients. What I deleted was a field
T_Tasks.ID_Clients (long integer) which had a many-to-one relationship with
T_Clients.ID_Clients.
Anyway, I think I am getting there, with the help of all of you lot.

 

[Tom Wickerath]

Hi Jack,

I understood that you had only deleted the foreign key field
T_Tasks.ID_Clients. My advice is to add this field back into the table, and
then run the appropriate update query to populate the field so that you can
establish the relationship again.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________


:

Thanks Tom.

Just to clarify - I never deleted a primary key. ID_Clients (autonumber)
was and remains the primary key of T_Clients. What I deleted was a field
T_Tasks.ID_Clients (long integer) which had a many-to-one relationship with
T_Clients.ID_Clients.
Anyway, I think I am getting there, with the help of all of you lot.