Automatic date for single column modification

[CryoJim]

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.

 

[SkipVought]

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,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[CryoJim]

It is for one model of machine that my company produces for sale. It has a standard template but several optional extras. Most systems are built to order with the buyers choice of options but we also want to maintain a small stock of standard units.

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

 

[SkipVought]

WOW! Your company has no inventory system?

Do they have any sort of MRP system?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[CryoJim]

It's a small company. There is an inventory system for materials and standard components that are bought in but each system that is built is in some way different to the others and an inventory of machined components is unneccesary because they are machined to order for each job. The exception is one particular model of machine which is becoming more standardised and is looked after by a small team which I have just joined. This database is separate from the main company inventory system. We're getting off topic though. Any thoughts on the date column?

 

[SkipVought]

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,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[CryoJim]

Interesting, thanks. The system they have here seems to work pretty well though. They've been going for a few decades and have more or less stabilised in terms of size. Without going into the particulars of how the company is set up (which I'm still only learning myself) it's hard to convey, but my team has very little overlap with most of the others and the data that does overlap is already well integrated with the main inventory system.

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?

 

[SkipVought]

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,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]