Update query does not update properly

[MarRyb]

Hello everyone,

At the moment I’m fighting with an update query in my database (I’m new to MS Access), I tried to search for helpful information everywhere but with no luck.

My DB description:

The goal of this database is to keep information about my projects. Every project has a list of assemblies and every assembly has list of components (lowest level). So I have 5 main tables (forms and subforms for lists as well):
ComponentsT->ComponentListT(list of components for assembly-as continuous form-it is shown as a subform on Assembly Main form)->AssembliesT->AssemblyListT (list of assembliess for project-as continuous form-it is shown as a subform on Project Main form)->ProjectsT. I also have table CLRSListT which I want to use for append and update query. The point of this is to have button on Project Main from to append all data (for project) into CLRSListT. But if I want to change something (quantity of components in the assembly), I would like to have an update button to update data in CLRSListT table. To make sure that always I have latest data.

Attached is copy of my DB.

My problem is as follows:
Append query works fine for me. All data are correct and stored in the CLRSListT. However, I struggle with update query, to update CLRSListT (with fields – CLRSListID=PK,ProjectID,AssemblyID,AssyListID,asslistQty,ComponentID and comlistQty).

Update query:

UPDATE ComponentListT INNER JOIN CLRSListT ON ComponentListT.ComponentID = CLRSListT.ComponentID SET CLRSListT.comlistQty = [ComponentListT]![comListQty];

It does not update all data, and number of updated rows is incorrect. For example, I changed quantity of some components in one assembly (ComponentListT). When I run update query, data in my CLRSListT table are simply not reflecting information stored in ComponentListT. Please see image attached.

I appreciate any help in this matter as I don’t have any idea what I’m doing wrong :banghead:. Thank you very much in advance for any kind of help.

Regards,
Martin

 

[MarkK]

This is not a recommended strategy in working with data, that you should have multiple locations for storing the same data. In good table design this data will have a single authoritative location with no need to copy it between tables.

 

[pbaldy]

FYI, both posts were moderated, now approved.

 

[MarRyb]

Thank you very much.

MarkK can you please give me some recommendations on how to change my table design in a smarter "correct" way. This design works fine for me except that update query. But the fact that I have one extra table where I store the same data should not be the reason why my update query is not working or maybe I'm wrong.

 

[MarkK]

A table typically represents a type of object and a field in a table typically represents an attribute of that type. So to use a real-world example, you might have a tblPerson table, which has a DOB (date of birth) field. In this case the DOB belongs inextricably to the row in which it appears; it is directly an attribute of the person (the Type object) that the row represents.

This should be a similar story with all your data, although in some cases the arrow of "ownership" may not be so distinct, but lets look at your field name, which is comListQty. To me, this suggests that you should have a table called ComList which should have a field called Qty, in which case, why prefix Qty with ComList? My DOB, above, is not prefixed with PersonDOB, it is already in the Person table, and DOB is clearly the DOB of the person.

So if the ComList object has a Quantity attribute, how is it also correct to assign that same value to the ClrsList.Quantity attribute? Why doesn't a ClrsList object have its own Quantity attribute? How is it valid that these different objects in different tables can share a value as specific and as integral as quantity?

See what I'm getting at?

 

[MarkK]

Paul, thanks for the tip on the moderated/approval step. I was not aware of that. Do you know how that happens?

 

[MarRyb]

Yeah thanks. I will try to rethink my table design and field names.

 

[pbaldy]

Paul, thanks for the tip on the moderated/approval step. I was not aware of that. Do you know how that happens?

Sometimes the forum software moderates a post and I can't tell offhand why. Perhaps it was the attachments here. In any case, if you reply to a moderated post, your reply will also be moderated, as happened here. I'm about to approve a reply as well, so not sure why the forum is moderating these posts.