By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

Automatically updated inventory

P: n/a
I have painstakingly created an excel 2000 workbook for the very complex
recipes I have to formulate. I have 2 sheets- 1 for configuring the recipe
and 1 that is like an inventory of all the raw materials and their
specifications. I have many lookup formulas on sheet1 that lookup the specs
on the inventory.
The problem is sheet 1 works really well but sheet 2 is not really
performing as an inventory database like I want it to.
The biggest problem I have had is keeping track of stock levels. I thought
that I would be able to have the inventory levels automatically updated when
I used them in the recipes on sheet 1. But it has turned out to be an
incredibly difficult exercise in VBA programming to achieve this on excel !
What should I do? I really need to have my inventory running live so
whenever I use it in recipes my stock is updated in inventory.
Can access do what I want?
If yes then do I need to start from scratch again or can I keep my recipes
on excel and inventory on access?
Any advice would be greatly appreciated as I know bugger all about access.

cheers
greg

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Some time ago I wrote a database program for industrial recipies for making
flavourings. It certainly handled recipes and stock control. Think it also
did buying raw materials and selling finished flavours. Also kept track of
batches. However it is pretty complex. If it is any use I can send it to
you, but it will require a lot of explanation.
"gregork" <gr*****@paradise.net.nz> wrote in message
news:aF********************@news02.tsnz.net...
I have painstakingly created an excel 2000 workbook for the very complex
recipes I have to formulate. I have 2 sheets- 1 for configuring the recipe
and 1 that is like an inventory of all the raw materials and their
specifications. I have many lookup formulas on sheet1 that lookup the specs on the inventory.
The problem is sheet 1 works really well but sheet 2 is not really
performing as an inventory database like I want it to.
The biggest problem I have had is keeping track of stock levels. I thought
that I would be able to have the inventory levels automatically updated when I used them in the recipes on sheet 1. But it has turned out to be an
incredibly difficult exercise in VBA programming to achieve this on excel ! What should I do? I really need to have my inventory running live so
whenever I use it in recipes my stock is updated in inventory.
Can access do what I want?
If yes then do I need to start from scratch again or can I keep my recipes
on excel and inventory on access?
Any advice would be greatly appreciated as I know bugger all about access.

cheers
greg

Nov 12 '05 #2

P: n/a
"gregork" <gr*****@paradise.net.nz> wrote in message news:<aF********************@news02.tsnz.net>...
I have painstakingly created an excel 2000 workbook for the very complex
recipes I have to formulate. I have 2 sheets- 1 for configuring the recipe
and 1 that is like an inventory of all the raw materials and their
specifications. I have many lookup formulas on sheet1 that lookup the specs
on the inventory.
The problem is sheet 1 works really well but sheet 2 is not really
performing as an inventory database like I want it to.
The biggest problem I have had is keeping track of stock levels. I thought
that I would be able to have the inventory levels automatically updated when
I used them in the recipes on sheet 1. But it has turned out to be an
incredibly difficult exercise in VBA programming to achieve this on excel !
What should I do? I really need to have my inventory running live so
whenever I use it in recipes my stock is updated in inventory.
Can access do what I want?
If yes then do I need to start from scratch again or can I keep my recipes
on excel and inventory on access?
Any advice would be greatly appreciated as I know bugger all about access.

cheers
greg


This is part of the reason MAS90 is so expensive. It does this. <g>.
There are a lot of postings on the subject of stock/inventory control.
See Allen Browne's website for an example database. There's a link
to his website at www.mvps.org/access One way of dealing with this,
which is what, I think, Allen recommends is having a table of
ingredients/items. Then you create purchase units and create
calculations that convert purchase units to "use" units. Your
in-stock quantities would be something like LastInventoryQty -
Sum(AmtUsedSinceLastInventory). So you'd write queries for that and
then you'd do a physical inventory periodically and then do your
adjustments. I think if you read Allen's example and download his
example, it should at least point you in the right direction.

HTH a little,
Pieter
Nov 12 '05 #3

P: n/a
If you can send it to me that would be great thanks Phil.

Kind Regards
Greg

"Phil Stanton" <di********@stantonfamily.co.uk> wrote in message
news:3f***********************@mercury.nildram.net ...
Some time ago I wrote a database program for industrial recipies for making flavourings. It certainly handled recipes and stock control. Think it also
did buying raw materials and selling finished flavours. Also kept track of
batches. However it is pretty complex. If it is any use I can send it to
you, but it will require a lot of explanation.
"gregork" <gr*****@paradise.net.nz> wrote in message
news:aF********************@news02.tsnz.net...
I have painstakingly created an excel 2000 workbook for the very complex
recipes I have to formulate. I have 2 sheets- 1 for configuring the recipe and 1 that is like an inventory of all the raw materials and their
specifications. I have many lookup formulas on sheet1 that lookup the specs
on the inventory.
The problem is sheet 1 works really well but sheet 2 is not really
performing as an inventory database like I want it to.
The biggest problem I have had is keeping track of stock levels. I thought that I would be able to have the inventory levels automatically updated

when
I used them in the recipes on sheet 1. But it has turned out to be an
incredibly difficult exercise in VBA programming to achieve this on excel !
What should I do? I really need to have my inventory running live so
whenever I use it in recipes my stock is updated in inventory.
Can access do what I want?
If yes then do I need to start from scratch again or can I keep my

recipes on excel and inventory on access?
Any advice would be greatly appreciated as I know bugger all about access.
cheers
greg


Nov 12 '05 #4

P: n/a
Many thanks for the link Pieter - I am slowly starting to digest the
information on Allen's website.
One thing I would like your (or anyone else's) opinion on is - whether or
not I should keep my formulation sheet on excel with my database on access-
It took a hell of a lot of work setting up my formulas for the recipe sheet
and I would not like to redo the whole thing again with access. Will an
inventory database work with an excel spreadsheet ?

Kind Regards
Greg

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
"gregork" <gr*****@paradise.net.nz> wrote in message

news:<aF********************@news02.tsnz.net>...
I have painstakingly created an excel 2000 workbook for the very complex
recipes I have to formulate. I have 2 sheets- 1 for configuring the recipe and 1 that is like an inventory of all the raw materials and their
specifications. I have many lookup formulas on sheet1 that lookup the specs on the inventory.
The problem is sheet 1 works really well but sheet 2 is not really
performing as an inventory database like I want it to.
The biggest problem I have had is keeping track of stock levels. I thought that I would be able to have the inventory levels automatically updated when I used them in the recipes on sheet 1. But it has turned out to be an
incredibly difficult exercise in VBA programming to achieve this on excel ! What should I do? I really need to have my inventory running live so
whenever I use it in recipes my stock is updated in inventory.
Can access do what I want?
If yes then do I need to start from scratch again or can I keep my recipes on excel and inventory on access?
Any advice would be greatly appreciated as I know bugger all about access.
cheers
greg


This is part of the reason MAS90 is so expensive. It does this. <g>.
There are a lot of postings on the subject of stock/inventory control.
See Allen Browne's website for an example database. There's a link
to his website at www.mvps.org/access One way of dealing with this,
which is what, I think, Allen recommends is having a table of
ingredients/items. Then you create purchase units and create
calculations that convert purchase units to "use" units. Your
in-stock quantities would be something like LastInventoryQty -
Sum(AmtUsedSinceLastInventory). So you'd write queries for that and
then you'd do a physical inventory periodically and then do your
adjustments. I think if you read Allen's example and download his
example, it should at least point you in the right direction.

HTH a little,
Pieter

Nov 12 '05 #5

P: n/a
"gregork" <gr*****@paradise.net.nz> wrote in message news:<28********************@news02.tsnz.net>...
Many thanks for the link Pieter - I am slowly starting to digest the
information on Allen's website.
One thing I would like your (or anyone else's) opinion on is - whether or
not I should keep my formulation sheet on excel with my database on access-
It took a hell of a lot of work setting up my formulas for the recipe sheet
and I would not like to redo the whole thing again with access. Will an
inventory database work with an excel spreadsheet ?

Kind Regards
Greg


Greg,
What is the structure of your formulation sheet? As I understand it,
it would be something like this in Access:

CREATE TABLE Recipe(
RecipeID AutoNumber,
RecipeName Text(50) NOT NULL,
RecipeSource Text(50),
....);

CREATE TABLE Ingredient(
IngredientID AutoNumber,
IngredientName Text(50),
....);

CREATE TABLE RecipeIngredient(
RecipeID LONG,
IngredientID LONG,
Qty INTEGER,
PRIMARY KEY(RecipeID,IngredientID));

If you had something like that or you could make it like that in
Access using MakeTable queries in Access by linking to your Excel
file, you should be ready to roll...
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.