editable query without primary key (1 Viewer)

[moke123]

Here's what the query should look like and it IS updateable

I hope the photos make it easier to see the differences. Your tables must be set up correctly or you will have many problems going forward.

 

[eshai]

Here's what the query should look like and it IS updateable

I hope the photos make it easier to see the differences. Your tables must be set up correctly or you will have many problems going forward.

tnx
like i explained students table don't have PK on student id because i need duplicate values
so without PK on one of the studentid it will be read only

 

[eshai]

https://access-programmers.co.uk/forums/attachment.php?attachmentid=78344&stc=1&d=1578452403

now if someone can fix frmStudentNotes
when you press the plus data is fill up

 

[deletedT]

eshai,

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

Adam,
No offense, but I think it takes a hundred years for me to understand you. Your posts #24 & #27 is insisting the OP is doing the correct way and in #38 you're saying MajP is correct.

And what was more strange for me was the way you used Unfortunately to say MajP is correct. Are you disappointed?

 

[theDBguy]

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

Hi. Sorry, I just couldn't let this misinformation to keep going. I wasn't talking about an UPDATE query. I am talking about a query that is not read-only. In other words, the query can be updated or it is updatable or the data can be change manually by the user when they open that query.


In the link you posted earlier, you said it states this rule, but I didn't see anything in there about it.

yes and the notes table have the student id with a relationship to student id in student table(the relationship is on the query) without pk it wont work

https://support.office.com/en-us/ar...ry-6ca3edfc-6d66-4d90-8219-c2b258d5bed7#bmcan

all explain in here

All I found in there was this quote:

When can I not edit data in a query?

You can never edit the data in a query when:

• The query is a crosstab query.
• The query is a SQL-specific query.
• The field you are trying to edit is a calculated field. In this case, you may be able to edit the other fields.
• The query is based on three or more tables and there is a many-to-one-to-many relationship.
  Note: Although you cannot edit the query datasheet in this case, you can edit the data in a form when the RecordsetType property of the form is set to Dynaset (Inconsistent Updates).
• The query contains a GROUP BY clause.

Nowhere was requiring a Primary Key mentioned in the above. So, I don't know where you got the idea that this is a "rule" in Access. If you have a better link, or if the "rule" is written somewhere else on that page that I just didn't see, please let us know.

 

[eshai]

You have not indexed the JOIN fields correctly: These fields do not consist of any unique index or primary key.
http://allenbrowne.com/ser-61.html

 

[eshai]

Adam,
No offense, but I think it takes a hundred years for me to understand you. Your posts #24 & #27 is insisting the OP is doing the correct way and in #38 you're saying MajP is correct.

And what was more strange for me was the way you used Unfortunately to say MajP is correct. Are you disappointed?

yes im disappointed
look at my Q' title
editable query without primary key

first he understood me then he took st to is way

 

[theDBguy]

You have not indexed the JOIN fields correctly: These fields do not consist of any unique index or primary key.
http://allenbrowne.com/ser-61.html

Hi. If that was directed at me, I would like to direct your attention to post #36 where I specifically said: All I need to have is a unique index in one of the tables.


That's exactly what Allen was saying as well. You can use a PK, but it's not the only option. You can also just use a unique index. In post #36, I also mentioned it was probably "just a matter of semantics," and I think this clarifies why I said that. Meaning, you keep saying "primary key" when you really mean "primary key OR unique index."

 

[vba_php]

Adam,
No offense, but I think it takes a hundred years for me to understand you. Your posts #24 & #27 is insisting the OP is doing the correct way and in #38 you're saying MajP is correct.

And what was more strange for me was the way you used Unfortunately to say MajP is correct. Are you disappointed?

Tera,

I was trying to give this gentleman encouragement that he actually CAN do this stuff. Maj obviously knows what he's doing. If you look at Maj's example, that is *exactly* the way this dude should be conducting his work. He's obviously confused about the setup he should have. Nothing he has said so far has made any sense, and I have a sneaking suspicion that if he understood the database concept in general and how it is suppose to make things easy for him, he would've understood the example the Maj posted.

 

[eshai]

Hi. If that was directed at me, I would like to direct your attention to post #36 where I specifically said: All I need to have is a unique index in one of the tables.


That's exactly what Allen was saying as well. You can use a PK, but it's not the only option. You can also just use a unique index. In post #36, I also mentioned it was probably "just a matter of semantics," and I think this clarifies why I said that. Meaning, you keep saying "primary key" when you really mean "primary key OR unique index."

primary key we'll always be a unique index without duplicates
and what do you do if you need index with duplicates you lost the PK

 

[theDBguy]

primary key we'll always be a unique index without duplicates
and what do you do if you need index with duplicates you lost the PK

Again, this is just a matter of semantics. A primary key requires a unique index, but a unique index does not have to be a primary key. For example, you can only have one primary key per table (it can be composed of multiple fields), but you can have multiple unique indices in one table. I just want to make it clear to future readers of this thread that joining two tables without a primary key will not be read only if you at least have a unique index on one of joined fields.

Now, I appreciate that your specific situation precludes you from creating a unique index because of the duplicate values. In that case, your solution is to add a third data source with a unique index (or primary key). But please, stop saying not having a primary key is an Access rule that makes a query read only.

Thank you.

 

[moke123]

primary key we'll always be a unique index without duplicates
and what do you do if you need index with duplicates you lost the PK

A foreign key can still be indexed and allow duplicates

 

[deletedT]

Tera,
I was trying to give this gentleman encouragement that he actually CAN do this stuff. ............ Nothing he has said so far has made any sense

That is exactly what I couldn't understand about you. How can you encourage something that is wrong?

PS: Don't reply to this please. We are hijacking the thread.

 

[eshai]

ok solve it
i add FK to the 2 tables "students" & stundentsnotes to the studentid field
in the students table it's a unique index no duplicates and in the studentsnotes its index with duplicates
from there i did a query with a join for "studentid" and now i can edit the data on every field

I have a lot of criticism for the answers here but I'll keep it to myself

 

[vba_php]

I have a lot of criticism for the answers here but I'll keep it to myself

I'm sorry, but I seriously have to laugh at this. No offense to anyone. eshai, I'm really glad you solved your own problem! Way to go!

 

[moke123]

I have a lot of criticism for the answers here but I'll keep it to myself

Sorry you feel that way. Most of us try our best to provide clear solutions and it can often be frustrating for us when the OP doesn't understand. It usually isnt easy to judge the knowledge levels of posters. Somethings that are common knowledge to us is unknown to the poster and its just a matter of explaining it over and over until you have that "Aha" moment.

Glad you have your solution and good luck with your project going forward.

 

[vba_php]

Glad you have your solution and good luck with your project going forward.

hey that's a great positive response, moke! maybe things are changing around here for the better.....that is, if I don't post anything further regarding religion....

 

[MajP]

I have a lot of criticism for the answers here but I'll keep it to myself

That is funny, because I do have lots of criticism for the OP. Hopefully if Eshai comes back this will help. You come here for help but you come across very rude and arrogant. This may be a language or cultural thing, but you seem more interested in telling others they are wrong instead of listening and explaining your issue.

1. At no time did you ever reply nicely saying thank you. When I go to forums looking for help and people spend their time trying to help me I always say "Thank you". Even if English is not your primary language, I would think you could be more appreciative. How about "Thank you for your suggestion, I tried it but it did not work for me." Instead it was always "Your answer does not work".

2. You actually have the nerve to criticize people who are trying to help you is several posts. That will not endear you to many people.

3. When asked direct simple questions, you tend not to answer but provide random information that is not relevant. No one can help if you refuse to answer their questions. I asked directly in about five threads for you to explain the relationship between Students and student notes, each time you either provided an unrelated statement or asked another question. I provided a sample database and asked if the relationship made sense. I did not suggest it as a solution. Instead of answering my question you modified it so that it did not work and said it did not work.

I get that English is not your first language, but we help lots of beginners in that same situation all the time. They just seem a lot more appreciative and less stubborn. If you come back recommend you act more polite and appreciative. Listen to what others are saying instead of just hearing your own voice.

 

[vba_php]

You come here for help but you come across very rude and arrogant.

I don't think we wanna compare him to me, do we? I don't think he's gonna like that long-winded thing you just posted, Maj.

 

[deletedT]

I don't think he's gonna like that long-winded thing you just posted, Maj.

It doesn't matter he likes it or not. It's the truth. It would be his problem to face the facts or not.