Table Relationship Help Required!

[REAPER_110]

Hi,

Just wondered if anyone can give me the solution to my problem?

I have tables as set below:-

tblPeople
pkPeopleID


Rest of fields below

tblClientInterviews
pkClientInterviewID
dteInterview
fkPeopleID
AdvisorID

tblEvalE1
pkEvaluationE1ID
fkClientInterviewID
Rest of fields below

I have relationships between:-
tblPeople:pkPeopleID and tblClientInterviews:fkPeopleID
tblClientInterviews:pkClientInterviewID and tblEvalE1:fkClientInterviewID.

The problem I am having is that for some reason when completing the form for tblEvalE1 the clientinterviewID is not transfering across into tblEvalE1.

I have tried different settings and just cannot get it to work! Been looking at this problem for a few days now, any help would be greatly appreciated.

Kind Regards,
REAPER_110

[blazerboy6]

Are pkEvaluationE1ID & fkClientInterviewID a one to one, one to many, or many to many relationship? if they're a one to one relationship, you might be better off combining tblClientInterviews & tblEvalE1 into one table, unless there are not evaluations for every interview. Can you put up a screen shot of your relationships? In 2007 it's under Database Tools/Relationships/Relationship Report.

The relationships established are basically to prepare the way for Queries and make life simple, they do not automatically fill in your forms unless you create a combo box on the form you want to bring in the information to.

Place the new combo box in the space where you want tblEvalE1:fkClientInterviewID to be on your evaluation form, base the combo box on tblClientInterviews (ie... Look up the values in a table or query) and tell the combo box to select pkClientInterviewID and to store that value in the fkClientInterviewID field... You should now be able to select ClientInterviewID's from a drop down menu on the form from tblClientInterviews: pkClientInterviewID and have that data get stored in tblEvalE1:fkClientInterviewID...

hope this helps,

[REAPER_110]

Hi,

I have attached a screenshot.

I am finding that I can enter data in the tblPeople table no problem. I enter data into tblClientInterviews no problem. Then when it comes to adding data referring to the interview in tblEvalE1 it comes up with a message stating something to do with cannot add the data as relevant data needs to be entered into tblClientInteviews, even though we have already entered it.

The way it should work is...
-Client data is entered into tblPeople.
-Interview details are entered into tblClientInterviews
-tblEvalE1 and tblEvalC1 etc are completed and relate to the particular interview for that particular client.

I just can't seem to get it to do what I want and its beginning to get very frustrating! lol.

Look forward to any help to get this sorted,

Kind Regards,
REAPER_110

[jzwp11]

In tblEvalE1 and tblEvalC1 you have a series of sequentially numbered fields (C1_1, C1_2..,E1_1, E1_2 etc.). Sequentially numbered fields are generally considered as repeating groups which is an indication that your tables are not normalized properly. The same can be said for tblCapGRFAJ... Additionally, if the fkAdvisorID field in the client interview table refers to a person, the person should be included in tblPeople and a join made via that table. You can add a field in tblPeople to distinguish the type of person (advisor, candidate, etc.)

[blazerboy6]

First things first, double check the order of your forms, if you have all this data entering done on one form and referential integrity enforced, the data won't update until after you "enter the data" on the first form (tblPeople, then tblClientInterviews, then finally to tblEvalE1/C1) establishing the record on which to base the other forms and tables. It looks like pkClientInterviewID & fkClientInterviewID (both) are in a 1 to 1 relationship. right click on the line and bring up 'Edit Relationship' to open the dialog box and check if "Enforce Referential Integrity" is checked. If it is, the table will force you not to create an entry under tblEvalE1 or tblEvalC1 for fkClientInterviewID until a pkClientInterviewID is entered AND SAVED under tblClientInterviews...

I'm assuming that the fields under tblEvalE1 and tblEvalC1 labeled 1-13 and 1-18 are specific questions on the form, ie, question 1, question 2 etc... If that's not the case, you might want to consider normalizing it.

Make sure that the field types in tblClientInterviews: pkClientInterviewID and tblEvalE1:fkClientInterviewID are appropriate (ie, both 'number' types in data type under design view on each table).

All this is assuming that you are entering your data though forms, and not directly into the table... If you are entering it directly into the table, make sure you press the Enter Key after you've filled out the fields for one entry (the whole line) and close the first table before entering data into the second table. when entering data directly into the table, MSAccess doesn't officially save the record until you click away from that line. That's why most people do data entry through forms.

[boblarson]

MSAccess doesn't officially save the record until you click away from that line. That's why most people do data entry through forms.

No, that's not the reason why most people do data entry through forms. Most people do data entry through forms because you have more control over what they can and can't do whereas if you are letting them enter data into tables directly, they can do a lot that you have no control over and it isn't a good thing for that. So, if people aren't using forms for that reason it is the one reason for which they really should be using them.

The behavior you said is why they use forms instead of tables still occurs in the form. Until you leave it, or move to another record, the record is not saved. When using subforms, in conjunction with a main form, the saving will happen just from moving between the two forms.

Most professional programmers will use the form's BEFORE UPDATE event to validate the data and decide whether to let the record save or not. You can't do that with tables. You can use very rudimentary validation rules on tables but you can do so much more with forms.

[blazerboy6]

No, that's not the reason why most people do data entry through forms. Most people do data entry through forms because you have more control over what they can and can't do whereas if you are letting them enter data into tables directly, they can do a lot that you have no control over and it isn't a good thing for that. So, if people aren't using forms for that reason it is the one reason for which they really should be using them.

The behavior you said is why they use forms instead of tables still occurs in the form. Until you leave it, or move to another record, the record is not saved. When using subforms, in conjunction with a main form, the saving will happen just from moving between the two forms.

Most professional programmers will use the form's BEFORE UPDATE event to validate the data and decide whether to let the record save or not. You can't do that with tables. You can use very rudimentary validation rules on tables but you can do so much more with forms.

Very true Bob, my point was just that they might not be saving the first record before they try to add related records in the other tables. I did not mean to imply that was the only reason to use forms, but it is certainly one of the reasons I do. I love having a little Form Control Button on each form with a

DoCmd.RunCommand
acCmdSaveRecord

in the On Click Event Procedure and give it the caption of "&Save". So non-Access users can easily save the data on the form before moving on.

This looks like it might be a database thrown together to evaluate data for analytic purposes and so I was wondering if they skipped form entry all together and were just inputting data directly into the tables, which could definitely cause them problems such as the

cannot add the data as relevant data needs to be entered into tblClientInteviews

error message they recieved. If the data has not been saved for tblClientInterviews and Referential Integrity is Enforced for the relationships (as it should be) it could cause this issue.