Updating Stock Quantites

[chriscook]

I am relatively new to Access and am having to learn a lot on the job.

I have been asked to Create a "Stock Database"

This will be used by primarly 2 people, the storesperson and a sales person.

We need to to show Part number, description, quantity in stock, latest stock check (which i have added to a table called Stock List)

The storesperson will need to add stock to the database when goods are received, and adjust them out if anyone takes any items to use.

What is the best way of doing this? We want to track any movements in the stock so we can look back to previous adjustments if required

I thought of creating a new Table? With the Part Number (from look up of the table Stock List), Date/Time, Adjustment Type (booking in or taking stock out) and Quantity.

Is this the right way to do this? Then create a form based on this table?

I do not know how to adjust the stock ( + or - ) in the table Stock List based on the figures entered in the Stock transactions Table/Form.

Thanks in advance for your help.

Please advise if you need any more information.

 

[plog]

I do not know how to adjust the stock ( + or - ) in the table Stock List

You don't. Calculated values shouldn't be stored, they should be calculated. So that means you have a table that logs all your transactions (into inventory, from inventory, spoilage, etc.) and then you have a query that adds those up and tells you how much of each you have.

 

[chriscook]

Thanks for your quick response.

So i should not have a quantity in stock value in my Stock List table?

 

[plog]

Correct. The quantity in stock will be derived from that new table you envision. You would use a totals query (https://support.office.com/en-gb/ar...-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a) and add up the Quantity field.

I would also advise you to take up some naming rules: 1. Avoid special characters in field and table names. That means only alphanumeric characters and underscores. So I would eliminate spaces in any table/field names you have ("Stock List" = "StockList"). 2. Prefix generically named fields with what they represent. Do not have a field just called "Date" or "Quantity" or "Type". Prefix them with what they represent, "SalesDate", "StockQuantity", "InventoryType". This will help you avoid reserved words and make it more intuitive which field you are working with when building queries composed of more than 1 data source.

 

[Frothingslosh]

HERE is a very good description of why you want naming conventions, along with the basics of one possibility.

 

[chriscook]

Brilliant, think i understand it.

Thanks for your help, i will crack on and be back if i hit any stumbling blocks

 

[chriscook]

Is it possible to have two fields in my StockList table:

1. Quantity In
2. Quantity Out

I then create a query to give the sum of each field.

Is there any way i can subtract the sum of Quantity Out from Quantity In?

 

[Frothingslosh]

Q In and Q Out are redundant and break normalization.

Use positives and negatives instead. Or else a transaction type (Receive, Sale, or Adjustment) and a number.

 

[gemma-the-husky]

you are just trying to manage stock quantities, and not values, aren't you?

 

[Solo712]

Is it possible to have two fields in my StockList table:

1. Quantity In
2. Quantity Out

I then create a query to give the sum of each field.

Is there any way i can subtract the sum of Quantity Out from Quantity In?

Chris, the stock movements (qty in/qty out) shoud not be in the same table (stocklist). You have a stock list and when people take stuff out or put stuff in you record the actions in a separate table. This is the way to keep the database normal. See why ? When you do something that changes quantity of the item in stock, you are not changing any of the item's attributes. It will always be the same part no, same description, same manufacturer. If you were to keep this info together with the quantities in and out you would be creating a really messy duplications.

Best,
Jiri

 

[Frothingslosh]

Man, I completely missed that he had them in his StockList table.

Guess I rolled a 1 on that spot check.