Adding Inventory

[Stephanie53]

Ok here is the issue..

I have an inventory query that has one field called Qty On Hand and the code for the field is as follows:

Qty On Hand: [Qty Purchased]-[Qty Sold]

now here is my issue..

I already have a list that tells me what is on hand at this moment. and being that we are staring out on this new DB we want to make sure that this shows this amount and be able to deduct it from that amount instead of creating a Purchase order for each and every item. I know how to create a table/query that has this amount on hand but as we go along when this dwindles down i want to remove this table/query and let it go back to the code above.

so how will i code this to where this can do that?

ask me direct questions if you are not sure if i am explaining this correctly



TIA

Stephanie

[rpeare]

Do you have a table that stores the original inventory levels of all of your items (i.e. the beginning of 2013)
Do you have a table that stores all additions to inventory?
Do you have a table that stores all removals from inventory?
Do you have a table (or method) to make adjustments to your inventory level to account for things like damage, scrap, testing, etc.?

If you have all of these (or are able to track them through your tables) you shouldn't need to create a table to do temporary calculations, you should be able to add all receipts of the item over your history and remove your 'losses' and come up with an accurate number.

I am suggesting this purely so you have a method to audit your inventory AND MOST IMPORTANTLY, be able to re-create reports should you need to go back in time

If you are able to track any addition to/removal from inventory of your items BY DATE you can recreate any report you ever ran if necessary.

I've got databases that have starting balances that are almost 10 years old and we're able to reprint anything accurately since then.

[Stephanie53]

This is what the purchasing/inventory dept sent me ...

ProductsTemp.zip

they also gave me the spread sheet of all the vendors they have to which in this DB gives the option to choose more than one vendor for these items

they just want to start off with what this says at this time. right now they are using Peachtree as their source of record for right now. Eventually they will have this DB as their source of records and will have this completely the way Peachtree is

i believe i have it updated already to be removed from the Qty On Hand but as we do the beta test this week we will find out. as it stands right now the query states this:

Inventory On Hand

and that is in relation to the table posted here

and on the form i have it referring to this at this time - i know i will have to update this daily until everything catches up with each other

as of right now the DB is just a way to make it faster for the Sales dept to be able to create quotes and orders then production scheduling and shipping for tracking purposes

i will mark this solved at this time and reopen if this doesnt work

Thank you for your time in this and helping me out

Stephanie