See what you can do with what I suggested in #9. If you do not add the serial number to the items table, then pretty much EVERYTHING has to change and you end up doing everything twice. Or in your case, hundreds of times because of all the related objects you will need to change. Once for non-serialized items and again for serialized items. I also mentioned this in other posts to you.
The only extra logic is when you buy/sell items. Serialized items always, only have a quantity of 1. When you sell it, it is gone. For items kept by quantity, you sell 1 and you have 27 left.
now its just too much to change all the column names.
You are depressing yourself into not fixing your foundation. You have been living with it for a long time but you don't understand how much extra effort it takes to manage a poorly designed schema. I'm pretty sure you don't look forward to your programming tasks. I wouldn't either. But it is a little like hoarding. Cleaning out the debris is going to make you feel ever so much better
Do you understand what I meant by mutually exclusive? Mutually exclusive means that only one of the group will contain a value. For example the seven instances of Check_Number in Transactions. Only ONE of them will ever have a value on any given row. Therefore, you would replace them with Two columns. One bound to a combo that lets you select the store and the second will contain the check number. That means that whether you have 7 locations or thousands, you only ever need two columns. One to pick the store, the other to enter the value. Do you think Macy's could support this structure? How many columns would they need? How many other objects have to change if they open/close a store? Instead of separate fields for cash, check, paypal, you should have one field with a type code of Cash, Check, CC, OnlinePayment (paypal is only one of many). Then you have a field for the check number, and one for the CC number (if you choose to store it) or the ID for the online payment method. You also have these broken down by store and by In or out. You have 59!!! fields in this multi-level repeating group and you should have closer to 5 or may be 6 to handle ALL types of transactions for ALL stores. I'm guessing that just fixing these two repeating groups will eliminate a large part of your excess objects, plus narrow down the width of the forms substantially.
I remarked on how you are keeping the spot prices of metals in some other thread. Now I see that not only are you storing the values incorrectly in their primary table but you are also duplicating the values in every single row of the transaction table. So you have 10 columns for the price of various metals, 9 of which have nothing to do with the coin in question, plus a date field. In a relational database, we don't repeat the same data over and over again, we join to the table that contains it and pick it up at that time. In this situation. isn't a coin only one primary metal? I know that many are alloys but the alloy wouldn't vary for the same coin issue. Instead of keeping the 11 fields in each transaction record, you should have one row per metal per date, maybe bid/ask prices if they work like stocks). Then the Transaction includes the date of the transaction and the ID of the row in the metal table for the correct metal at the price you used.
You can attack the repeating groups one at a time. The ones that are mutually exclusive are pretty easy to resolve but you also need to replace all the related objects. Instead of having one related object for each repeat in the group, you will have ONE query/report/form/whatever that might take a variable. ALWAYS make backups before embarking on a structural change. During the conversion process, just leave the old columns in the table. Maybe just move them all to the right edge to get them out of the way and add replacement columns. You have to change the column name anyway, so there is no reason to reuse a column that contains data at this time. When you think you have fixed the problem, rename the old columns with leading x's. Then test, test, and test again. After a couple of weeks, you can make a final backup and delete the replaced columns.
Also, we can't see the answers you are getting on the other forums and so unless you repost them here, we are essentially wasting our time.