Integrity (1 Viewer)

Hi, I have a stock database and a order database and they are related by the stock number. I now want to change my stock numbers. If I change the stock number it doesnt change the order form. It wont let me change the integrity of the database

any ideas would be most welcome
thank you

Nick

Please tell us more--remember we know nothing of your set up or requirement.
Are these really databases or tables in the same database?

Do you need to know what the prior stock numbers used to be, a change history if you will.

Also, what is meant by “It won’t let me change the integrity of the database”?

Are you saying you have referential integrity enforced and it wont let you change a number because there are related records in another table?

It is a moderate bet that because of your low number of threads, you are either new to Access or you are not comfortable with Access - maybe an "uneasy truce" situation? Your threads DO go back to 2014, so we've seen you before. I get the impression in viewing the past threads that the last couple of times you tried to get help, things didn't go quite right. In any case, we would love to help - but your problem description is lacking in necessary details.

DON'T try to explain it to us in jargon, but DO try to tell us your current setup in terms how many database files we are discussing, how many tables (and if multiple files, which ones are in which files). Tell us specific error messages (and error numbers if it gives one) - and what step you attempted that triggered the message.

I SUSPECT that I know your problem (as do my fellow forum members), but it sounds like something we should not touch until we understand it a bit better. We need a little bit of a road-map. Or, if you think of us emergency responders, we need X-rays of the injured area. THEN we have a good chance of helping you.

If StockNumber is an autonumber, you cannot change them. You would have to change the StockNumber data type from autonumber to Long Integer. That would also remove the automatic assignment as you add a new row so you would have to take care of that also.

Primary keys should not be changed. That is why most tables would have an autonumber that is used as the primary key. They might also have an additional identifier that is defined as unique. For example in a payroll system, you might include SSN as a unique identifier even though you would NEVER use it as the PK. In the case of SSN, there are legal reasons for not using it as a PK also. Or you might have an application that interfaces with your human resources system. You might have your own employee table because they won't let you link to "theirs". In this case, you would again use an autonumber as the PK to the employee table but you would include EmployeeNum (from the other application) as a unique identifier to prevent the same employee from being added more than once.

So now we get to what happens if you decide to ignore our advice and go with something that actually might change. In that case, you would specify CascadeUpdate = Yes when you define the relationship. That means that if you change the PK in the parent table from "abc" to "def", the value change will cascade to the FK in all related sub tables. If you change the FK in a sub table to a different but valid value, that "moves" the child record to a different "parent".

The OP seems to be MIA.

Are you sure? He only started the thread yesterday. Not everyone even works on the weekend.

True enough, but after Doc’s post I looked into his prior posts. He hasn’t had the best of experiences in this forum.

you ought to use a surrogate key, and use your surrogate key to manage your relationships.

Then add fields to your product table to supply the different info you need. That way you can change the data without worrying about other tables. Needs a bit of work, but worth it in the long run.

Ok Guys first of all thank you to everyone who has replied, secondly ……. Let me start again
I have one database that I have made from scratch, with little experience of Access, I programmed Lotus Approach in the Nineties and tbh I preferred it but alas is no more.
In this database I have many tables, and I know if I had my time again I would do it differently to how it is, I know it is no where near perfect.
Two tables I am having difficulty with is the stock table and the order detail. When I raise a customer order it uses the Order table, and within in that uses a sub form in the Order Detail table, this keeps track of the items sold (from the stock table) colour, size and price amongst other things.
Now my company has grown somewhat I need to add more stock items and would like things to be kept organised. In my current stock table stock numbers were allocated willy nilly and sort of in numeric order, now I would like a bit more organisation, so for example all t shirts will begin with 804xx stock number, and babywear will start with 803xx. There are then certain stock numbers that will overlap, in the new regime I might have to use a stock number that was used previously by something else.
At the moment if I change the stock number in the stock table, I lose all the Order Detail information in the Order Detail Table. Of course what I would like to happen is that I change the stock number in the stock table and all the stock numbers change in the Order Detail Table.
I have looked into how I set up the relationship and see that they are linked by the stock number, why is it that I lose the Order Detail Information? When I check the data integrity properties, there is no data integrity. If I then change it to enforce the data integrity I get the error message saying that there are related records in another table.
When I run a query to check the related records I see that there is a record for each stock record in each Order Detail Record giving some 2.7 billion records
The StockNumber field is neither a Primary Key or a Autonumber. They are unique so I do not get 2 StockNumbers the same.
I am not too sure what other information I can give you and I dont know if you know what I am trying to explain. Having used Access for a number of years now (about 4) I am toying with the idea of starting all over again and then export/import the data. I am very busy though so may end up paying someone to redesign it for me.
Thanks once again for your answers and hopefully I have done gud in explaining this time

gemma-the-husky said:

you ought to use a surrogate key, and use your surrogate key to manage your relationships.

Then add fields to your product table to supply the different info you need. That way you can change the data without worrying about other tables. Needs a bit of work, but worth it in the long run.

Click to expand...

I have no idea what a surrogate key is but when I have a little time I will investigate, thank you

NauticalGent said:

The OP seems to be MIA.

Click to expand...

To be honest I didnt expect replies so quickly and didnt check

Pat Hartman said:

If StockNumber is an autonumber, you cannot change them. You would have to change the StockNumber data type from autonumber to Long Integer. That would also remove the automatic assignment as you add a new row so you would have to take care of that also.

Primary keys should not be changed. That is why most tables would have an autonumber that is used as the primary key. They might also have an additional identifier that is defined as unique. For example in a payroll system, you might include SSN as a unique identifier even though you would NEVER use it as the PK. In the case of SSN, there are legal reasons for not using it as a PK also. Or you might have an application that interfaces with your human resources system. You might have your own employee table because they won't let you link to "theirs". In this case, you would again use an autonumber as the PK to the employee table but you would include EmployeeNum (from the other application) as a unique identifier to prevent the same employee from being added more than once.

So now we get to what happens if you decide to ignore our advice and go with something that actually might change. In that case, you would specify CascadeUpdate = Yes when you define the relationship. That means that if you change the PK in the parent table from "abc" to "def", the value change will cascade to the FK in all related sub tables. If you change the FK in a sub table to a different but valid value, that "moves" the child record to a different "parent".

Click to expand...

To show you my level of experience most of this went above my head and I googled some of your abbreviations

moke123 said:

Are you saying you have referential integrity enforced and it wont let you change a number because there are related records in another table?

Click to expand...

Yes, it wont let me change the integrity becasue of that very reason

NauticalGent said:

Do you need to know what the prior stock numbers used to be, a change history if you will.

Also, what is meant by “It won’t let me change the integrity of the database”?

Click to expand...

I dont need to know the old stock number if the Order Detail table is changed but I did add a field for the old stock number in the Stock table

Take a backup.
Take another backup.

In another 3rd offline copy of your data make a new table. Import all your existing Stock Numbers. Add a column call it NewStockNumber.

Fill it out. Put all your shiny new stock numbers against the existing stock numbers.

Now the money shot. If you have referential integrity enabled in your table relationships you will have to remove it.
Run 2 Update queries. First one will be to take any order details stock numbers and replace them with the new stock number.
Second update query will be to do the same to your Stock Table.

If it all goes Pete Tong you have many, many backups to go back to and get it right.

Once it's right - job done. Hopefully.

(This is effectively creating the surrogate key table Paul was referring to above)

Pete Tong?

NauticalGent said:

Pete Tong?

Click to expand...

https://www.urbandictionary.com/define.php?term=it's all gone pete tong

And there ya have it...