Join Key of table in recordset

[Rohit0012]

hi im struggling my problem basically is when i create a form for my database and im creating it using 3 different tables so when ive finished it comes up with the error message cannot add record join key of TrackingRecord table not in recordset
please help
The Tables and field Names are listed below
Students
StudentNo(Primary Key)
Surname
FirstName
TutorGroup
TrackingPoint
TrackingrecordID

Concern
ConcernNo (Primary Key)
StudentNo (Primary Key
ClassCode
Date


Concern?
Solution
Actionby?
SSA?
ReviewTeacher

TrackingRecord
StudentNo(Primary Key)
TrackingRecordID (Primary Key)
TrackingPoint
CWG
MTG

Thanks in advance

[RuralGuy]

Are you using MultiField PrimaryKeys (PK) simply to keep the two values together unique? There are other ways to do that without making them the PK of the table.

[Rohit0012]

yes i am what is the other way please explain

[RuralGuy]

Joining tables in an RDBMS prefers just single field PrimaryKey and ForeignKey fields. You can do it as you have it but it takes a lot more work. You should really use a form to eliminate duplicates but you can also just create an index (not a Primary Key) on those two fields with no duplicates allowed. Then you would have the single field PK and the unique combination of the two fields.

[Rohit0012]

please can you explain to me how to do that thanks

[Rainlover]

Just to help out Allan who keeps himself rather busy.

To set up a compound index in a table click on (little lightning bolt thingy at the top) of your table while in design view. Or View Indexes.

In the 1st empty row of the form that opens, name the index in the left hand column. I would use something like XXX, or whatever you like.

In the middle column of the same row, select the first field.

At the bottom left of the form, change 'Unique' to Yes.

In the row directly under XXX, leave the left hand column empty, and select the second field in the middle column.

You can add a third and fourth etc if you want

Close the Indexes form

You have created a composite index that will not allow duplication of the same fields.

[RuralGuy]

Thanks for the assist Rain. Des isn't it?

[Rainlover]

Yes It is.

[Rohit0012]

ive tried that and it still doesnt work any other ideas please

[RuralGuy]

Are you saying that the suggestion Rain gave in post #6 does *not* keep those combined field values unique?

[Rainlover]

Try again.

I believe what I posted is correct.

If you fail again let me know and I will create you a sample database.

[pdadosky]

We are working on a database for a juvenile detention center and we can add records to every form except the "main" form, which we call the "Admission form/Personal history form". This form is connected to two tables - Admission and Detention. We get the error message "Join key of table 'Admission' not in record set". Can anyone out there look at this database and suggest how we can correct the problem?

I have attached the entire database in a zip file.

Paul

[Rainlover]

I am not real conversant with 2007 but I can suggest that your Primary key should not be number. It should br autonumber.

Also look at the Record Source in query design view. The query is not updateable. Most likely because of your relationships and Primary/ Foregin Key problems.

Suggest you read up onn Normalisation.

[pdadosky]

Thank you Rain. I'm going to take another look at the relationships. I built one-to-one relationships between the Admission table and two others, and a one-to-many relationship between Admission and Detention. We tried making Admission just one table to include the other two with which it has a one-to-one relationship, but the table was too big for Access to handle. Perhaps if we delete the relationships and make a few other adjustments will the people at the detention center be able to enter their information. Thanks for the feedback!

Paul

[Rainlover]

Paul

Deleting One Table at a time from your query is an excellent way to find where your problem lies.

Also check the relationship type in the query. It should not be choice Number One. Both Fields are Equal.

Try choice three. The bottom one.

Mother and Father should be just the one Table, but you need to understand Normalisation a little better to do this.