Project problem..........

[davidgordon]

Hi,

In our ERP system (MS Access), when new customers place orders, their
ID is linked to a product group.

We are a company who has say 60 products, but various customer have the
products packed differently, i.e. some with cables, some without, some
with manuals, some without and so on....

I set up a range of products to exist in each group.
i.e.

Group 1 called.... company X (UK), Our product range for this group
will have a UK Power Cord

Group 2 called... Customer X, Our product range will have no manuals
or cables

Each product regardless of which group it is in has 2 name fields, 1 is
the customers own product code and the other is ours.

Product Name = Customers Product Code Equivalent
Product Group = Our product code

So, when the customer is created, he is set to a specific group which
matches the product packing requirements.

When we enter an order for that customer, the products selected in a
dropdown are sourced from the product group they were set to.

THE PROBLEM:

We hold a certain amount of regular stock for each of our products.
When we select a product for a specific customer, I want the system to
display how many units are in stock of that product, regardless of the
customer.

There may be multiple customers each using their own variation of the
same product code, i.e.

There may be 6 different products called 'Product X' but all with
different packing requirements.

How can I set a stock quantity to 'Product X' in the system, so that
whatever 'Product X' is ordered via the customer it belongs to, there
is only 1 stock qty displayed for which I can update when the order is
placed.

I hope this makes sense.

Basically we hold stock, say
Product X - 50
Product Y - 12
Product Z - 62

Yet we might have orders for Product X from 3 different customers.
Each of the variants of Product X are seperate products with seperate
PRODUCT IDs, so I cannot assign the stock qty to each one, I need a
central stock level some how ?
Appreciate your assistance.
Best Regards

David

[John Sheppard]

You sound horribly confused.

I had a real good stab at deciphering your problem but I cant come up with
much.

Im not sure how your system works, but you are generally best off not
storing a qty on hand value anywhere in your table, this should be
calculated on the fly from purchase invoices - sale invoices. On Allen
Browne's website there is a demonstration of this for your more
straightforward stock control.

Im a bit confused, but it seams to me like you are trying to do something
that your table strucuture doesnt support. Why is it that the customers have
different order codes if they are the same product......alternativly if they
are different product why do they have the same internal ordercode, Thats
very odd either way you look at it..

Do you keep track of the stocklevels for the parts that make up the product?
That would make more sense to me.

I didnt help but maybe that might help someone else clarify what you mean.
John
<da*********@scene-double.co.uk> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

Hi,

In our ERP system (MS Access), when new customers place orders, their
ID is linked to a product group.

We are a company who has say 60 products, but various customer have the
products packed differently, i.e. some with cables, some without, some
with manuals, some without and so on....

I set up a range of products to exist in each group.
i.e.

Group 1 called.... company X (UK), Our product range for this group
will have a UK Power Cord

Group 2 called... Customer X, Our product range will have no manuals
or cables

Each product regardless of which group it is in has 2 name fields, 1 is
the customers own product code and the other is ours.

Product Name = Customers Product Code Equivalent
Product Group = Our product code

So, when the customer is created, he is set to a specific group which
matches the product packing requirements.

When we enter an order for that customer, the products selected in a
dropdown are sourced from the product group they were set to.

THE PROBLEM:

We hold a certain amount of regular stock for each of our products.
When we select a product for a specific customer, I want the system to
display how many units are in stock of that product, regardless of the
customer.

There may be multiple customers each using their own variation of the
same product code, i.e.

There may be 6 different products called 'Product X' but all with
different packing requirements.

How can I set a stock quantity to 'Product X' in the system, so that
whatever 'Product X' is ordered via the customer it belongs to, there
is only 1 stock qty displayed for which I can update when the order is
placed.

I hope this makes sense.

Basically we hold stock, say
Product X - 50
Product Y - 12
Product Z - 62

Yet we might have orders for Product X from 3 different customers.
Each of the variants of Product X are seperate products with seperate
PRODUCT IDs, so I cannot assign the stock qty to each one, I need a
central stock level some how ?
Appreciate your assistance.
Best Regards

David