editable query without primary key (1 Viewer)

eshai · Jan 7, 2020

I created a query from two tables, one of which does not have a primary key because of the need for duplicates. I know that such a query cannot be edited what I am used to do is to create a subform and then create the fields on the main form

Code:

 form![subform]![fieldname]

so i can see it in spreadsheet view.
now I have a problem The field shows an error and only when i open the plus sign data Appears
I would also love to hear about more ways

theDBguy · Jan 7, 2020

Hi. I think we'll need to see what you're looking at. Joining two tables in a query doesn't necessarily means it cannot be updated. Are you able to post a demo version of your db?

vba_php · Jan 7, 2020

eshai said:
The field shows an error and only when i open the plus sign data Appears

are you talking about the plus icon that appears at the left side of a parent record when you are looking at the data in the actual table? or are you talking about pressing the "+" icon in the same place, when you see it while in datasheet view of an actual subform object?

eshai · Jan 7, 2020

Unfortunately, I can't upload demo of the database

i'm talking about the plus icon that appears at the left side of a parent record in spreadsheet view

theDBguy · Jan 7, 2020

eshai said:
Unfortunately, I can't upload demo of the database

i'm talking about the plus icon that appears at the left side of a parent record in spreadsheet view

Unfortunately too, it would be hard to help you if we can't understand what you did to get this problem. Are you able to post screenshots at least?

Micron · Jan 7, 2020

one of which does not have a primary key because of the need for duplicates.

Then the duplicates should be a foreign key, which enables you to have a primary key, so that claim doesn't really fly. Besides, even if a PK wouldn't help, your post indicates a table design issue as there should be at least a FK field that's related to a PK field elsewhere.

You might find some help here as well
http://allenbrowne.com/ser-61.html

eshai · Jan 7, 2020

theDBguy said:
Unfortunately too, it would be hard to help you if we can't understand what you did to get this problem. Are you able to post screenshots at least?

il try to explain in more details
i have 2 tables with same field called "Sid" now i build a query to show data from one table that match the "Sid" of the second table
one table have primary key on "Sid" the second doesn't have because duplicate are needed. now such a qure wont let me edit data in the query fields(access rules)
so i used a form and subform

eshai · Jan 7, 2020

Micron said:
Then the duplicates should be a foreign key, which enables you to have a primary key, so that claim doesn't really fly. Besides, even if a PK wouldn't help, your post indicates a table design issue as there should be at least a FK field that's related to a PK field elsewhere.

You might find some help here as well
http://allenbrowne.com/ser-61.html

i have foreign key doesn't work

MajP · Jan 7, 2020

Unfortunately, I can't upload demo of the database

i'm talking about the plus icon that appears at the left side of a parent record in spreadsheet view

Can you post a screen shot instead. It sounds like you have a sub datasheet. If you can please remove them from the table in table design.

theDBguy · Jan 7, 2020

eshai said:
il try to explain in more details
i have 2 tables with same field called "Sid" now i build a query to show data from one table that match the "Sid" of the second table
one table have primary key on "Sid" the second doesn't have because duplicate are needed. now such a qure wont let me edit data in the query fields(access rules)
so i used a form and subform

Hi. I do the same thing. Create two tables with the same field: one primary key and the other not. I create a query to join the two tables and was able to update the query. Probably not what I would actually do in a real database, but I didn't have any problems updating it, so it should work for you as well. No Access rules violated here...

eshai · Jan 7, 2020

https://access-programmers.co.uk/fo...ntid=78329&stc=1&d=1578439875c=1&d=1578439858

the to records showing data after i press the plus sign
i hope that halp

MajP · Jan 7, 2020

I see a composite key in table students and the same composite key in table student notes. Unfortunately I cannot read the field names.

Why do you need a composite key in students? Can you just have a StudentID and then in student notes a FK to student ID.

The_Doc_Man · Jan 7, 2020

Well, the simplest way to do that is to add a primary key even if you won't use it for much. Add an autonumber key to the table that has no PK. Make it the PK. Then in the JOIN query, even though it isn't a participant in the linkage, include the PK. Odds are that you would then make the query updateable because now Access can tell which record you wanted to edit. The trick is merely that when Access wants to write back a record, it has to be absolutely unequivocal as to which one it wants to write back. Having the PK, even though it has no other function, would make the choice unequivocal. Follow my idea?

eshai · Jan 7, 2020

MajP said:
I see a composite key in table students and the same composite key in table student notes. Unfortunately I cannot read the field names.

Why do you need a composite key in students? Can you just have a StudentID and then in student notes a FK to student ID.

did that as well doesn't work

MajP · Jan 7, 2020

did that as well doesn't work

I do not know what that means. Can you explain how the tables are related and why you need a composite key? Since I cannot read your fields I am just guessing from the table names. Normally this is a very simple relation. 1 student has many notes, but that is just a guess.

eshai · Jan 7, 2020

MajP said:
I do not know what that means. Can you explain how the tables are related and why you need a composite key? Since I cannot read your fields I am just guessing from the table names. Normally this is a very simple relation. 1 student has many notes, but that is just a guess.

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/article/edit-data-in-a-query-6ca3edfc-6d66-4d90-8219-c2b258d5bed7#bmcan

all explain in here

eshai · Jan 7, 2020

The_Doc_Man said:
Well, the simplest way to do that is to add a primary key even if you won't use it for much. Add an autonumber key to the table that has no PK. Make it the PK. Then in the JOIN query, even though it isn't a participant in the linkage, include the PK. Odds are that you would then make the query updateable because now Access can tell which record you wanted to edit. The trick is merely that when Access wants to write back a record, it has to be absolutely unequivocal as to which one it wants to write back. Having the PK, even though it has no other function, would make the choice unequivocal. Follow my idea?

did that to doesn't work
btw im using office 2016 if its have a meaning

MajP · Jan 8, 2020

Code:

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 wor

DOES NOT WORK it not a thing. It does not mean anything to anyone: error code, no data, data base explodes, database locks up, etc.

Explain to me why the Students tables cannot have a single PK and the notes table a single FK. There may be a reason, but you need to explain. Why a composite key in both tables? That is not standard.

eshai · Jan 8, 2020

MajP said:
Code:

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 wor

DOES NOT WORK it not a thing. It does not mean anything to anyone: error code, no data, data base explodes, database locks up, etc.

Explain to me why the Students tables cannot have a single PK and the notes table a single FK. There may be a reason, but you need to explain. Why a composite key in both tables? That is not standard.

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

for the FK i just trayed couple of method with without so on

MajP · Jan 8, 2020

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

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.

editable query without primary key (1 Viewer)

Registered User.

I’m here to help

Forum Troll

Registered User.

I’m here to help

AWF VIP

Registered User.

Registered User.

You've got your good things, and you've got mine.

I’m here to help

Registered User.

Attachments

You've got your good things, and you've got mine.

Immoderate Moderator

Registered User.

You've got your good things, and you've got mine.

Registered User.

Registered User.

You've got your good things, and you've got mine.

Registered User.

You've got your good things, and you've got mine.

Similar threads

Users who are viewing this thread