Simple Warehouse Tracking

[monkeyharris]

Hi all,

I've looked around the internet and posts for a template to record stock in and stock out of a warehouse. All of these have far too much data. I'm only interested in the following but cannot seem to pull it together.


Product Code
Product Description
Number of items received or despatched
Location the product is being stored in or taken from
Total "On Hand" quantity.

Product code and description are linked and always unique.
I want to be able to put stock away and despatch stock from multiple warehouse locations. Example. I could have 100 tins of paint and i want to put 50 in AA01 and another 50 in BF01.
The On Hand quantity will show 100 once confirmed into stock.

Does anyone have an idea of where to start putting this together. Hope you can help.

 

[SpillerBD]

In ACCESS.
Use the Template for Inventory.
If you really want to use Excel as a database, you can, but the Form controls and data entry are better in Access.

 

[monkeyharris]

In ACCESS.
Use the Template for Inventory.
If you really want to use Excel as a database, you can, but the Form controls and data entry are better in Access.

Thanks SpillerBD. I'm not expert on Excel but my Access is terrible. I did try earlier but there are so many fields in the template i just don't need.

 

[Joe4]

Thanks SpillerBD. I'm not expert on Excel but my Access is terrible. I did try earlier but there are so many fields in the template i just don't need.

Can't you just ignore the fields that you don't need?

In Excel, you can probably hide those columns.
In Access, you can just remove them from your queries/forms/reports.

 

[SpillerBD]

Thanks SpillerBD. I'm not expert on Excel but my Access is terrible. I did try earlier but there are so many fields in the template i just don't need.

With Joe4, then.
Reporting of Current Levels could be done with Pivot Tables, but that also requires the Pivot Table update step.
Therefore, use the new Array functions such as UNIQUE to List your Products and then use a SUMIFS to calculate your totals. This will be live sums without having to do the PivotTable refresh. Check out Mike Girvin's YouTube Channel for the Array Functions.)

For your table, make it a Table with CTRL+T. This will help ensure you don't have blank rows. ie, you'll maintain a proper data set. A hidden function, FORM may be useful. You can add to your QAT.