Data Entry Using inner join query 1

[BrenoAguiar]

I have a form that it has been working fine to ADD data to the table "cleanup". BUt if by mistake the user types something over the field "computercode", it will mess up another table "units" that it should NOT! here is the SQL:

SELECT Cleanup.ComputerCode, Cleanup.UnitCurrentGrade, Cleanup.UnitpreviousGrade, Units.BuildingID
FROM Cleanup INNER JOIN Units ON Cleanup.ComputerCode = Units.ComputerCode;

Is there a way that If the User Edit the "computercode" field, it will only change the "cleanup" table?

Thanks a lot!

 

[MajP]

If I understand this right, I think that you have established a relationship between Cleanup.ComputerCode and Units.ComputerCode and selected cascade update. So when you change the primary key Cleanup.ComputerCode it updates the foriegn key Units.computerCode. You could stop this by going to the relationship window and unchecking cascade_updates. However, if this is the case, I can not see a reason why you would not want this to happen.

 

[BrenoAguiar]

Well, The cascade option is unchecked. But the primary key is in the UNITS table and also in the CLEANUP table as the COmputerCode field.

 

[MajP]

If computer code is a primary key then, it should only be a primary key in one of the tables. My guess is that it is a primary key in Cleanup and a foriegn key in the Units table. So you should definately see the field in both tables. If it is a primary key in both tables, I would think that there is a big problem with normalization.

 

[BrenoAguiar]

That was the problem. I removed the primary key from the Cleanup table and left only in the units table and it took care of the problem. I have to Preserve the data in the UNITS table, so I removed it from the Cleanup table. Added an autonumber field as a Primary key though. Thanks a lot!