Automatic date for single column modification
Automatic date for single column modification
(OP)
Hello,
I'm new to this website and to MS Access (and my job too, but that's less relevant). I'm trying to create a database for several hundred components of a machine for tracking which components are in stock, which need to be made, what they're made of, technical drawings etc etc.
My question is:
If I have a column for "Number in stock" and one for "Stock updated", can I automatically fill in the latter with the date that the former is updated?
I know there is a way to automatically date stamp when any of the columns for an entry are modified, but I want to just track when the stock number column is changed.
Any advice would be much appreciated.
I'm new to this website and to MS Access (and my job too, but that's less relevant). I'm trying to create a database for several hundred components of a machine for tracking which components are in stock, which need to be made, what they're made of, technical drawings etc etc.
My question is:
If I have a column for "Number in stock" and one for "Stock updated", can I automatically fill in the latter with the date that the former is updated?
I know there is a way to automatically date stamp when any of the columns for an entry are modified, but I want to just track when the stock number column is changed.
Any advice would be much appreciated.
RE: Automatic date for single column modification
hi,
Are you only keeping track of components for ONE machine?
Is this machine a company capital asset or a product that gets produced for sale?
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Automatic date for single column modification
The database will make it easier to keep track of the parts we have and which we need to send to the machine shop for manufacture, but it will be good to know how up to date the stock list is.
Cheers
RE: Automatic date for single column modification
WOW! Your company has no inventory system?
Do they have any sort of MRP system?
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Automatic date for single column modification
RE: Automatic date for single column modification
It is not at all off topic. Your small company is proceeding down a very dangerous path by isolating various data repositories that would benefit the company tremendously if they were better inetgrated. As you grow, it will become more aparent that this isolation of data will inhibit your company from competing in your target market.
Someone in your company ought to explore integrating your data/systems.
Are you using a FORM as the user interface to update your table? That is where the date can be assigned when a change occurs.
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Automatic date for single column modification
I'm sure there's always room for improvement, like anywhere, and they're probably thinking about it but it's not a priority at the moment. Perhaps when I've been here longer I can bring it up.
Anyway, I was going to use a form to update the table, yes. Should I use an expression?
RE: Automatic date for single column modification
You can just assign Now() to that row's date stamp when a change occurs.
However, when I do analysis on an inventory system, I often want to know ALL the dates when things happened to a particular inventory item. This can be done using a TRANSACTION table, where you record individual transactions with a designation to tell you what kind of transaction it is, the quantity associated with that transaction and the date/time of the transaction, maybe an inventory location. The inventory balance is then CALCULATED each time you run a query against the table.
For instance first you have an Inventory Adjustment to assign the starting value. Then you Issue a quantity to a job, Your Receive a quantity from a vendor. You Issue a quantity to another location and you Receive the quantity into a location, etc.
Skip,
Just traded in my old subtlety...
for a NUANCE!