Discuss "How might this be done?" in the Microsoft Access forum.

I have a table that has:

ProductID, DateCreated, Qty, DateModified, and various other fields unimportant here.

I go to a form and adjust the inventory value either by subtracting or adding and now the table from above looks the same with only the Datemodified and Qty fields having changed.

How do I change a table like this so only these two fields are changed? No new record is created. If I subtract the inventory so the qty is Zero then the record is deleted. I can't figure out how to do this as the database I am using is locked down someway so I cannot see the querys and code behind it if it is even there at all. I think this whole database was written in Access but it looks just like a regular program on the front end (it starts with splash screen, then a cmd type window pops up for a few seconds, then the program begins with a logon prompt).

You need to re think your logic imo,.

For example you are edditing a original value,. on the original record. What happens if a mistake is made? How will you know the value to correct it to?

Then even worse, if the record is deleted when qty reaches zero reconciliation will be impossible.

__________________
If i helped you, make me famous by clicking the

This is a table for a proprietary program we have purchased to work with Quickbooks. I cannot see anything but the tables in the mdb file so I really don't know what the logic is... but I do know that when I make an inventory change to the program we logon to... the InventoryHistory table gets new records added to it and the Inventory table gets updated. There must be an export table that gets built as well to transfer info to Quickbooks but I cannot tell as all the export tables have no entrys.

So I would say that the InventoryHistory table is taking care of the problems you mentioned.

Actually, an export table isn't required. For example, to subtract inventory, just put a negative number for the quantity, to add, use a positive number. I did this on the fly, I would probably break this down to two tables, mainly because of repeating create dates. Also, if this was going to be a realistic database, I would group these items together in their own table (tbl_ItemType): ItemNumber, CreateDate, and CurrentPrice. The related table (tbl_ItemOnHand) would have: ItemNumber, Qty, DateModified. This would be related one to many: One tbl_ItemType to Many tbl_ItemOnHand.

The following example db, shows how a basic inventory database stores the info. As you sell items, you would make the qty negative. As you add inventory, you would make the qty positive. Now, down the line, you will have probably 10's of thousands of transactions, which is why you want to store as little as possible there.... TransactionNumber, ItemNumber, Qty, DateModified.

Also, check out AOG's Inventory Lite DB for a practical example

Attached Files

Exampledb.zip (10.2 KB, 4 views)

I would say there is nothing you can do in that situation. It's a proprietary program that could have been written in VB or C++ or who knows what, using an .mdb to store the data. You can't just start changing things. What would you change? If you change the table, you will most likely completely disable the program that uses it. If you can't see the code, and don't even know what language it's written in, there's nothing that you can do, other than go back to the person or company that wrote the program.