Append Query & Null Values

[The_Doc_Man]

Why is ANYTHING moving?

Location, being an attribute of items, properly depends on the PK of the individual item entries. You can have records for a "lot" (="batch") of items for which there is a location code. If you have split lots of the same thing with different location codes, this is fine. But an APPEND query? Only if you are appending a record that represents moved parts.

NO repeat NO parts should "move" if the count is zero. If there ARE parts that are zero, you have records that don't represent anything. How do you move "nothing" ? How do you grasp it to pick it up and move it into the bins on the other side of the building?

I strongly suspect that your database is not properly normalized.

Why don't you publish an overview of your structure showing relevant table names including the PK of each - and any fields in that table that are FK to another table.

 

[David Eagar]

Doc

Will heed your advice as always, but to clarify, I am moving a portion of inventory from 1 location to another (but you are right, if there is ni inventory, there is nothing to move and the record would not exist in the table anyway)

When i move some valid inventory, I need to move any allocation it has against it. In the 'real' world, if I have no outstanding sales orders, I have no allocations, so value will be 0

Is my databse normalised? With the help of the this forum I have been working my way through it, & while MUCH better than it was, I still suspect there is some fine tuning to do, and this problem may well be one of them

Just thinking through this response, I suspect the answer may well be drop the allocation from the table & calculate on the fly when required

Thanks for your input

 

[The_Doc_Man]

My question is more directed.

Of what value is it to know that there is an allocation against something that is being moved? Do you disallow a draw from some stock?

Let's cut to the chase. Say you have 7 left-handed veeblefetzers in bin 12345 in the north end of the building and 5 left-handed veeblefetzers in bin 54321 in the south end of the build. So an allocation for 11 left-handed veeblefetzers come in. Does it really matter to your business from which bin you supply the order? I.e. other than knowing where to look for it, why do you CARE where something is when filling an order?

The point behind this question is to decide if there is a hidden business rule associated with stock relocation. If there is, for example, a priority of drawing against stock in location A before drawing from location B, that might be a business rule. But if one spot is just an overflow bin for another bin, then "location" is just a place to put things.

 

[David Eagar]

If I'm going to dig myself a hole, it's going to be a bloody deep one!!

My aim in all this is to apply LIFO rules to manage my inventory, so yes, if I have multiple orders for any item, the first in order will get it's allocation from the oldest stock

ps - this is purely a training exercise for me, my 'brilliant' db will never see the light of the real world, unless I plan to run my own business - and there is about as much chance of that happening as there is of Mars leaving our solar system!

 

[David Eagar]

mmmm pidgeons, and Oh my, a conviently placed cat - let's throw the cat & see what happens

Let's cut to the chase. Say you have 7 left-handed veeblefetzers in bin 12345 in the north end of the building and 5 left-handed veeblefetzers in bin 54321 in the south end of the build. So an allocation for 11 left-handed veeblefetzers come in. Does it really matter to your business from which bin you supply the order? I.e. other than knowing where to look for it, why do you CARE where something is when filling an order?

Real life scenario (tongue firmly in cheek)

The venerable (& venerated) BobLarson is currently in hospital undergoing treatment - the call comes down from the operating room - Get medicine XYZ STAT!

Pick request goes down to Stores and the storeman says 'Gee, I COULD walk the 600 metres to the North end of the warehouse, but there's some right in front of me here at the South end

Conviently ignoring the Expiry Date, medicine is sent to the theatre, Bob dies, Forum disintigrates into dissrepair, hospitial gets sued for billions, database designer, who thought, 'Gee LIFO's too hard, I'll ignore it' gets sued for squillions (and twenty years to boot) all becuase not enough attention to detail and delivering what is required

I jest, but depending on the business model, some things that seem trivial may be the most important part of the whole process

Doc - I have have seen you make many references to the business process (extremely well informed advice) and would offer the following:

Because you CAN do it in Access, SHOULD you?

If the above were real - would YOU admit yourself to a hospital that ran it's key software in Access (built by an ameteur like me!!) NOT LIKELY

 

[David Eagar]

I think deleting the allocation field from the table and make it a calculation will be the best method unless there are any other contributions?