editable query without primary key (1 Viewer)

eshai

Registered User
Joined
Jul 14, 2015
Messages
159
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

Access MVP
Joined
May 21, 2018
Messages
2,336
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
Joined
Jul 14, 2015
Messages
159
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

Banned
Joined
Oct 6, 2019
Messages
1,974
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....
 

eshai

Registered User
Joined
Jul 14, 2015
Messages
159
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
 

MajP

Access MVP
Joined
May 21, 2018
Messages
2,336
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

Access MVP
Joined
May 21, 2018
Messages
2,336
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
Joined
Jul 14, 2015
Messages
159
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

Banned
Joined
Oct 6, 2019
Messages
1,974

MajP

Access MVP
Joined
May 21, 2018
Messages
2,336
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
Joined
Jul 14, 2015
Messages
159
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

Access MVP
Joined
May 21, 2018
Messages
2,336
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
Joined
Oct 29, 2018
Messages
7,632
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
Joined
Jul 14, 2015
Messages
159
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

Banned
Joined
Oct 6, 2019
Messages
1,974
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
Joined
Jul 14, 2015
Messages
159
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
Joined
Jan 11, 2013
Messages
1,054
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

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom