Inventory Control

[Don]

Using A2003 I am working on a Sales and Purchasing db for my company. Sales
are reasonably straight forward as I've adapted some of the Northwind objects
to suit. Purchases, however are a different matter.
I've created the Orders and OrderDetails tables with appropriate fields.
What sometimes happens is that a Supplier may not be able to fill an order
completely. That is, some items may be placed on backorder for supply at a
later date.
EG: ordered Widget x 5 but only receive 3, backorder is 2.
What I want to see happen is when the backordered items do arrive, I pull up
the original order that only has the backorder showing and receive those
widgets then.
I'm thinking an Append query to move any backorders to a "temporary" table
that the purchase order would call from when I receive the backorder/s. Once
that backorder has been filled run a Delete query to remove the backorder
from the "temp" table.
Any thoughts??
Don, Sydney Australia

 

[Damian S]

Hi Don,

Could you simply have an Ordered and Received field on your order form, and
you know that if you ordered 5 and received 3, there must be 2 on backorder?
That way, you would instantly be able to tell what orders were outstanding,
as Ordered and Received wouldn't equal... Simply update your received field
to the correct value once the items on backorder appear!!

This method may be too simple for your requirements, but it's certainly one
way to achieve it without worrying about keeping disparate tables in synch -
especially if you aren't worried about keeping historical data (we ordered 5
on this date, received 3 on this date and the other 2 on another date) -
since you said you were going to delete the item from your temp table!!

Hope this helps.

Damian.

 

[Don]

Damian, thanks for the speedy reply.
yeah, we will need historical data: 3 received on date A, 2 received on date
B etc.
thats why i'm thinking of a separate table of outstanding orders to call from.

 

[Don]

Damian, thanks for the speedy reply.
yeah we will need historical data here.
3 received on date A, 2 received on dateB etc. thats why i was thinking of a
table of outstanding orders to call from when the orderes are recieved into
store.

 

[Tony Toews [MVP]]

Using A2003 I am working on a Sales and Purchasing db for my company. Sales
are reasonably straight forward as I've adapted some of the Northwind objects
to suit. Purchases, however are a different matter.
I've created the Orders and OrderDetails tables with appropriate fields.

FWIW I would create a TransactionHeader and TransactionDetails table.
In the TransactionHeader table I would put a field indicating this is
a Purchase Order to vendor, Inventory Receipt, Sales Order to
customer, Inventory Adjustment, etc. In the TransactionDetails table
I would have Qty On Order and Qty fields. If it's a sale then put a
negative qty in the field.

You would also have a field linking this transaction header record to
the original. So inventory receipts would have the PO transaction
header record in that field.

EG: ordered Widget x 5 but only receive 3, backorder is 2.
What I want to see happen is when the backordered items do arrive, I pull up
the original order that only has the backorder showing and receive those
widgets then.
I'm thinking an Append query to move any backorders to a "temporary" table
that the purchase order would call from when I receive the backorder/s. Once
that backorder has been filled run a Delete query to remove the backorder
from the "temp" table.

With the above two tables in place you would then create a new receipt
header and transaction records for each of the PO items with product
ID and null quantities. Then the user fills in the quantities
received on this receipt. Note that I wouldn't just put in the
outstanding items because screwups happen and you may receive
duplicates of items already received but that's your decision.

Tony


--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Don]

Gentlemen, I have worked a partial solution here with queries. Firstly, I
created an Append query to move the order details to an Outstanding orders
table thus saving the original data and using the Outstanding data to draw
from.
Secondly, When I enter the qty received along with invoice number, date etc,
my command button saves the record and then runs an Update query. This qry
updates the qtyordered to the previous qtybackordered (>0), qtyreceived to
0, and the backordered qty to 0. This seems to be working well,
except,.................!! I need to have a new invoice number, date etc for
each instance the supplier sends me product against an order. This is where
I'm getting stuck. I can get the renewed data to display as I want it, but it
is still using the original invoice number, date etc I entered in the first
instance.
I think I may have tables a little skewed?!?

any thoughts?