Relationships

[TAWise]

My database tracks Projects. My main Project Table has a ID (autonumber) and
has fields such as Title, Customer, Project Manager, Project Number, etc. I
have this table connected to a Funding Table that has a FundingID
(autonumber), ID, Title, FY09 Probabilities, FY09 Projected, etc. In the
Relationships map I have linked the "ID" field from the two tables. From
these tables I have created a Project Form and a Funding Subform that I have
linked via the "ID" field. I have even tried using the "ID" as a filter.
Everything works fine unless I edit the "Title" field in the "Project Form"
which causes me to lose all of my funding data for that project in the
"Funding Subform". Can anyone please tell me what I am doing wrong?

 

[John W. Vinson]

My database tracks Projects. My main Project Table has a ID (autonumber) and
has fields such as Title, Customer, Project Manager, Project Number, etc. I
have this table connected to a Funding Table that has a FundingID
(autonumber), ID, Title, FY09 Probabilities, FY09 Projected, etc.

You're "committing spreadsheet", it appears. You should NEVER store data in
fieldnames; this table should have a FundingID, ID, FY (a Number field with
values 2009, 2010, etc.), Probability, Projected, etc., with one *record* per
fiscal year. The Funding table should not have a Title unless the title is
pertinent only to that funding year - it should certainly not have the same
title redundantly in the two tables.

In the
Relationships map I have linked the "ID" field from the two tables. From
these tables I have created a Project Form and a Funding Subform that I have
linked via the "ID" field. I have even tried using the "ID" as a filter.
Everything works fine unless I edit the "Title" field in the "Project Form"
which causes me to lose all of my funding data for that project in the
"Funding Subform". Can anyone please tell me what I am doing wrong?

Perhaps using the Title rather than the ID as the master/child link field?
What are the Recordsources for the main and subform? What are the Master and
Child Link Fields (should be ID)?

 

[Jeff Boyce]

Are you saying that you have the ID field in the [Project] table connected
to the ID field in the [Funding] table? By chance, are both of these Access
Autonumber fields?

If so, there's no way the numbers Access assigns in one table
(automatically) are related in any possible way to the numbers Access
assigns (automatically) in another table.

Take a look at Access HELP on the topic of "foreign keys" if you've been
trying to connect autonumber IDs.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Howard Burgman]

My database tracks Projects. My main Project Table has a ID (autonumber)
and
has fields such as Title, Customer, Project Manager, Project Number, etc.
I
have this table connected to a Funding Table that has a FundingID
(autonumber), ID, Title, FY09 Probabilities, FY09 Projected, etc. In the
Relationships map I have linked the "ID" field from the two tables. From
these tables I have created a Project Form and a Funding Subform that I
have
linked via the "ID" field. I have even tried using the "ID" as a filter.
Everything works fine unless I edit the "Title" field in the "Project
Form"
which causes me to lose all of my funding data for that project in the
"Funding Subform". Can anyone please tell me what I am doing wrong?