Problem with duplicates on a Update Query

[Lhoj]

I have this form in my inventory database that shows on a number of datasheet subforms all the info of the computers, peripherals and so on assigned to a workstation. A number of buttons allow the user to remove the selected item from the workstation or add a new one. Every peripheral and component is linked to a workstation through a FK linked to the workstation's PK. There's a Workstation ID'd "stock" where the components get assigned when they're not assigned to any workstation and are ready to use or assign somewhere else.

So, the "Remove from workstation" SQL is pretty simple:

UPDATE Whatevertablethecomponentisin SET WorkstationID = 'Stock' Where ComponentId=WhateverIdTheSelectedcomponentHas

The selected Id is always the right one because it gets printed on the message to confirm the operation. In fact, it works for every component I have tried except for one. For that particular one it trhows a "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship" error. The only restrictions in the table from that item (in this case the Computers table) is that the ID (which doesn't get changed) must be unique and the Computer name (which gets erased) must be unique too. The name is not the problem since any other computer with a name removed from a workstation is taken out alright. There's no other computer with the same ID on the table (obviously, since the restriction makes it so). But still I get a duplicate error.

I've tried compacting and repairing the DB to no avail. I've removed the component from the DB, created a new one with the same Id (which ironically gives no error), got the same error when removing the component from any workstation I assign it to. But if I go to the computers table I can manually change its workstation FK and remove its name without problem.



I know it's hard to track the error without access to the DB, and I can't share it for this since it's a problem caused by a particular record with 'confidential' data, but has anyone experienced any similar problem and knows what could be causing it?

[CJ_London]

check your indexes for the table for duplicates not allowed - not all are visible in the field properties so in table design view, click on the indexes button on the ribbon

[Lhoj]

Just the component Id and the computer name as unique (no duplicates) indexes. As mentioned, the SQL works ok for every other component (the ones I've tried at least), and editing the same fields the SQL updates manually for that particular one works ok too, but the Update SQL query fails.

Update: Found a couple more 'faulty' components. Thing is they have nothing different to the majority of them that work. And even removing the record and adding it to the DB again doesn't fix the problem, as if the Id was somehow corrupted.

[CJ_London]

as you say

'it's hard to track the error without access to the DB'

the error is self explanatory so only other thing I can suggest is to try doing it manually, might give you a clue

Some general comments

is Whatevertablethecomponentisin really the name of a table? If it is a general description and you change it to different tables depending on something, then this implies your db is not constructed correctly. Implication being what is actually happening is not what you think is happening
if you are using reserved words (common ones are date, name, description, desc) they can cause misleading errors - google 'access reserved words' to find a full list

good luck with your project, with nothing to go on there is nothing I can add

[orange]

I suggest you show readers your relationships window (assuming you have same). Make sure all tables are expanded to show all fields. Also specific sql example of an update that relates to the problem you're having.
I realize you are saying the info is confidential -we don't want your real data necessarily, but if you can provide some mock-up data or test data that exhibits the problem, that would be most helpful for analysis. Even a separate database with only a few "concocted records" would help.

[Lhoj]

is Whatevertablethecomponentisin really the name of a table? If it is a general description and you change it to different tables depending on something, then this implies your db is not constructed correctly

Each component type (or almost) has its own table, mostly because they have different attributes and some of them have a slightly different behaviour (meaning i.e that they are not linked to a workstation but to a location and some as the HDDs are linked to a computer and not to the workstation directly). This is because of the way items are handled specially while in stock. So there's a table for computers, a table for screens, another for printers etc... and each one is linked with the appropiate table (generally a workstation but otherwise a location in general or whatever).

By the way, found the cause of the error, but now I can't explain not why was it failing, but why was it working for most of the computers. Basically the "computer_name" field, which is only present in the computers table (that's why it only happened with laptops and desktop computers and not with peripherals) had a no duplicate restriction, but for some foolish reason I decided to remove the name of the computers I retired from a workstation by setting them to "", which obviously produces a duplicate error. Now, the DB is still on developement and there hasn't been much movements in terms of computers on the current workstations (mostly just installing new ones) but I've made multiple trials in my local copy of the DB and most of them went through when they shouldn't have.

Anyway changing the ""s to nulls resolved the problem, so problem solved. Ty all very much.

[CJ_London]

Each component type (or almost) has its own table, mostly because they have different attributes

probably not the way I would handle it. My instinct would be to have four 'primary' tables - locations, components, component types and attributes plus a linking table listing attributes per component type and another one providing attribute values for each component. Easy to add new attributes, new component types without design changes. Your method requires a redesign if a new component type comes along

[Lhoj]

Ok, I can't picture how your suggestion would work. You mean having all the different fields for any component (like screen size, disk capacity, computer name, ip...) which can amount to over a hundred in the same table? Wouldn't that lead to multiple blank fields considering each component type won't have more than 5-6 exclusive fields? I'm probably missing something obvious but I can't envision your design. Please bear in mind I'm a total beginner regarding access and DBs, this is just a side project to turn the already preexisting non-relational DB i found (with the current table structure for the components) into a fully relational one.

[CJ_London]

I'll try to knock up an example

[CJ_London]

see attached -not clear whether each your components have a serial number so are unique, but easy enough to add another field or a table of serial numbers and link locations to that instead of components

[Lhoj]

Ty very much for your time, Ajax. I'll take a look at it and see if I can make some alterations easily to my DB. Main goal is learning after all!

[Lhoj]

Thanks for your help, Ajax, but I don't understand how that solves the problem of having to link some component to a location, others to a computer and others to a workstation, just to name some examples. It would have to be done as a fk in one attribute linked to the type of component or something and I don't see how to do that. Meaning, if the components are all included in the same table, how can I link each to a different table or even to other components in the same table? Yes, I can add an attribute named connectedToWorkstation and add the ID of the workstation, but how do I do that value be a fk linked to the pk of the workstations table while the attribute connectedToComputer be a fk to the pk of the components table (and only of a certain type) and the plain attributes like screen size not be linked to anything?

[CJ_London]

I was just demonstrating how to avoid multiple component tables. A location could be anything - a country, an address, a building, a workstation or a computer or peripheral if you are supplying replacement parts. So perhaps you need a location type table and only link components types that can only be 'supplied' to that location type.

Up to you, but multiple tables ultimately are not the way forward

Since you are unable to provide any example data I'm not sure I can help any further