editable query without primary key (1 Viewer)

eshai

Registered User.
Local time
Today, 15:22
Joined
Jul 14, 2015
Messages
193
Trust me I get it, I know how queries work. That really does not answer any questions. If you want help, please answer the questions instead of telling me how you think Access works.

WHY CAN YOU NOT HAVE A SINGLE PK IN THE STUDENTS TABLE AND A FK IN THE NOTES TABLE? What is the purpose of the composite keys.

ok now students have PK and studentsnotes have nothing no FK or PK
what's next
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:22
Joined
May 21, 2018
Messages
8,463
Again you are not answering any questions, just making statements. HOW ARE THESE TABLES RELATED? I would assume students have a StudentID and StudentNotes have a studentID_FK (a foreign key relating many notes to a student). Is that correct? It may not be, you could have a note related to many students. I am asking you to explain how the tables are related and the purpose of the composite key. If I have a one to many from students to studentnotes I would think I just need a studentID in the student table and a foreign key in the notes table. If it is many to many (a not can relate to many students) then you need a junction table instead.
 

eshai

Registered User.
Local time
Today, 15:22
Joined
Jul 14, 2015
Messages
193
Again you are not answering any questions, just making statements. HOW ARE THESE TABLES RELATED? I would assume students have a StudentID and StudentNotes have a studentID_FK (a foreign key relating many notes to a student). Is that correct? It may not be, you could have a note related to many students. I am asking you to explain how the tables are related and the purpose of the composite key. If I have a one to many from students to studentnotes I would think I just need a studentID in the student table and a foreign key in the notes table. If it is many to many (a not can relate to many students) then you need a junction table instead.

First of all I am not a big expert in Access so be gentle with me

now i have failed in student table call student id same in studentsnotes table
i have a form that base on student table with sub form of studentsnotes When the child fields are linked you edit a record in studentsnotes it gets the student id automatically. now i need a query that well add the first name and last name to a new form base on studetsnotes in datasheet view
for the FK i delete it i was just trying something
 

vba_php

Forum Troll
Local time
Today, 08:22
Joined
Oct 6, 2019
Messages
2,884
First of all I am not a big expert in Access so be gentle with me

now i have failed in student table call student id same in studentsnotes table
i have a form that base on student table with sub form of studentsnotes When the child fields are linked you edit a record in studentsnotes it gets the student id automatically. now i need a query that well add the first name and last name to a new form base on studetsnotes in datasheet view
for the FK i delete it i was just trying something
eshai,

I've been following all this. I think you are doing the right thing, or at least on the right track. Can you enlighten all of us that are reading this by answering these questions?

What is the purpose of a field that is also a foreign key? What is the difference between a primary key and foreign key?

If you can explain that to me and the others your answers to those 2 questions, this thread might get closer to finding an solution....
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:22
Joined
May 21, 2018
Messages
8,463
This is what I would expect. Does this make sense?
 

Attachments

  • Student Notes.accdb
    484 KB · Views: 54

eshai

Registered User.
Local time
Today, 15:22
Joined
Jul 14, 2015
Messages
193
eshai,

I've been following all this. I think you are doing the right thing, or at least on the right track. Can you enlighten all of us that are reading this by answering these questions?

What is the purpose of a field that is also a foreign key? What is the difference between a primary key and foreign key?

If you can explain that to me and the others your answers to those 2 questions, this thread might get closer to finding an solution....

the foreign key have no meaning i delete it just trying to make a kay for students id to bypass access rule
students table have PK on a failed called id(autonumber)
studentsnotes have no PK or FK as i explain 3 times
the image was a mistake
there is no relationship between the 2 tables
 

vba_php

Forum Troll
Local time
Today, 08:22
Joined
Oct 6, 2019
Messages
2,884
This is what I would expect. Does this make sense?
eshai,

that database is *exactly* the way you should be doing what you're doing. :) I would follow that example explicitly.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:22
Joined
May 21, 2018
Messages
8,463
students table have PK on a failed called id(autonumber)
I would expect that the student tables have an ID and an autonumber is good

studentsnotes have no PK or FK as i explain 3 times
Even if you explained this, it makes no sense. I would assume student notes are related to students. If that is the case then a note should have an FK to the student table.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:22
Joined
May 21, 2018
Messages
8,463
Look at the query all my Changes
The query shows Students related to their notes as would be expected. Is there a question?
 

eshai

Registered User.
Local time
Today, 15:22
Joined
Jul 14, 2015
Messages
193
The query shows Students related to their notes as would be expected. Is there a question?

i want to edit the notes field its read only query:banghead:
what you gave me i already have
 

vba_php

Forum Troll
Local time
Today, 08:22
Joined
Oct 6, 2019
Messages
2,884

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:22
Joined
May 21, 2018
Messages
8,463
I am out of here, hopefully someone can help this person. I understand there may be a language issue, but the OP refuses to answer questions and refuses to listen to what I am saying. Not wasting anymore time.
 

eshai

Registered User.
Local time
Today, 15:22
Joined
Jul 14, 2015
Messages
193
eshai,

this db you uploaded does *not* have the relationships in it that MajP showed you in his sample! why did you delete them?? Maj, maybe this whole thing is similar to beating a dead horse? Shall we leave this gentlemen to figure out his issue on his own do you think?

take is first db and try to edit the notes field
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:22
Joined
May 21, 2018
Messages
8,463
take is first db and try to edit the notes field
You cannot edit it because you refuse to to what I suggest. Good luck.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:22
Joined
Oct 29, 2018
Messages
21,357
this is a rule by access you can't edit fields in query that build from 2 tables that doesn't have pk on the same fields the query become read only
I'm afraid I am not aware of this Access rule. It might be just a matter of semantics, but I have no problem creating an updatable query pulling data from two tables where neither table has a PK field. All I need to have is a unique index in one of the tables. Just saying...
 

eshai

Registered User.
Local time
Today, 15:22
Joined
Jul 14, 2015
Messages
193
You cannot edit it because you refuse to to what I suggest. Good luck.

that was my Q' how can i edit the field

what was your suggestion?

sorry for what im going to say
but you get a Q' and you look on it as your on db and what you would do
perfect if that was the scenario but you forgetting that is a different db not as you aspectet
 

vba_php

Forum Troll
Local time
Today, 08:22
Joined
Oct 6, 2019
Messages
2,884
I am out of here, hopefully someone can help this person. I understand there may be a language issue, but the OP refuses to answer questions and refuses to listen to what I am saying. Not wasting anymore time.
eshai,

Unfortunately, I agree with Majp on this one. I'm not sure we can help you. I'm terribly sorry about that. I'm sure you'll find a solution at some point. If you do fine one and you need help with something else, you're welcome to start another thread and ask another question. Good luck to you.
 

eshai

Registered User.
Local time
Today, 15:22
Joined
Jul 14, 2015
Messages
193
I'm afraid I am not aware of this Access rule. It might be just a matter of semantics, but I have no problem creating an updatable query pulling data from two tables where neither table has a PK field. All I need to have is a unique index in one of the tables. Just saying...

is not an update query
its a query build from 2 tables that when you open it its read only
its an access rule if you don't have PK on the same fields of the tables
 

moke123

AWF VIP
Local time
Today, 09:22
Joined
Jan 11, 2013
Messages
3,849
Eshai,

Your Student table looks like this



MajP's looks like this



MajP's is the correct way to set up this table. If there is some other Identifier you need for a student that should be another field and not a primary key. There is no reason to add your field "ID"

this is MajP's notes table



This is your Notes table



MajP's table is the correct way to set up the table. It should be easy to see the differences looking at these photos

Your query looks like



This is incorrect and results in a non- updateable query.

***Continued in next post as I've apparently max out attachments.
 

Attachments

  • E_studentTbl.JPG
    E_studentTbl.JPG
    17.5 KB · Views: 263
  • M_STudentTbl.JPG
    M_STudentTbl.JPG
    21.8 KB · Views: 257
  • E_Notes.JPG
    E_Notes.JPG
    24.4 KB · Views: 263
  • M_Notes.JPG
    M_Notes.JPG
    23.4 KB · Views: 254
  • E_Q.JPG
    E_Q.JPG
    36.1 KB · Views: 258

Users who are viewing this thread

Top Bottom