not able to edit query result set

[Guest]

I have feeling that I can not update my result set of my query because of the
relationships between the tables. However not sure how I should link or join
the two tables.

Batch Ing. Table Prod Ingred. Table
Batch Ing ID(Primary) Prod Ing. ID
BatchId SemiCode (Primary)
Semicode Raw Mat Code (Primary)
Raw Mat Code Qty produced
Lot#

Linking semicode to semicode
even if I set the Prod. Ing. ID only to primary key still can not edit
result set of query.

Please help

 

[Edward G]

Hi Babs,
I am one of the less expert people posting on this board but as your post
has seen no responses in 8 hrs
I thought you should at least not feel you are being ignored if I respond.
It is hard for a person at my level of
expertise to understand what it is that you have there, but it looks to me
like your problems may run deeper
than having a query that isn't updatable.
First thing I notice looking at your post is the naming conventions you are
not utilizing. For instance, most
Access users would not have any spaces in the field names. So Raw Mat Code
would be RawMatCode or Raw_Mat_Code. Prod Ing. ID is not even a valid field
name with the period in the middle.
I can't imagine looking at these fieldnames what a SemiCode is but I am
really surprised that a table called Prod Ingred would have a field called
Prod Ing ID that wasn't also THE primary key. That just seems bizarre.
It is also hard to know (for me at least) what sort of datatypes you have
selected for these various fields and the effect
that might have on your query, if any.
I also think you may have some normalization problems. For instance, I am
sort of surprised to see a "Qty Produced" field in a table that looks for
all the world to be the equivalent of a BOM (bill of materials). It is also
surprising to see a
Lot# field in a table that also includes BatchId.
So, I guess I am wondering how far along you are in using this database. Is
it functioning or in the design stage?

Ed G

 

[Guest]

Hi,

You didn't say whether the Semicode is indexed (unique) in the first table.
If not, it would be a many to many join, which would not be updatable. You
may be able to get a half updatable query if you change to an outer join
between the tables.

You can find a lot of information on when queries are updatable by typing:

"when can i update data from a query"

in the answer wizard dialog in Access. Then look for the help topic with
the same title.

If there is something specific that you are trying to accomplish, you could
post back with more info on what you want to do and whether the semicode is
unique in your first table.

Some options that come to mind include using a form with a subform (linked
via semicode), or using query on only one table and inserting a subdatasheet
for the other based on the semicode.

HTH, Ted Allen