hi,

We have a mail order system developed in ms access which handles 100
orders/day (about 8% return rate).
It follows standard design patterns:

orders table(name, address1, address2 etc)
order_details table(primary_key,order_id, product_id, qty, cost,
shipped_date, shipped_qty)
payment_table(payment_id, order_id, amount)
inventory table(product_id, qty(negative or positive), value)

all standard stuff!

We manage stock by doing sum queries on the inventory table.

How can we implement a returns system in this database?  In the past
we have tried creating order_detail lines to reflect returned items
and added manual stock transactions.  We are fully capable of doing DB
development, but need a sensible database way of representing returns
for simple stock management, reporting etc.  It needs to be sensible
and easily implementable in our database.

Are there any guides available for this sort of thing, or can you
suggest a variety of techniques which might be used to solve this in
our access database.

Can someone possibly compare & contrast possible solutions taking into
account aims of the returns system:

- efficient reverse logistics operation (from customers to us)
- low IT staff involvement of stock management issues
- ease of working with the system (staff understanding & training
required)
- reporting
- ease of development & maintenance
- any other relevant areas that are important

Thank you

Returns
-------------------------------------------------------------
| ReturnID | OrderID | ProductID | | Restockable | Comments |
-------------------------------------------------------------

Add a returns table, do a count on the returns table for each
ProductID in inventory, then check if the item is in restockable
condition (I would do an INT field and have 0 for no 1 for yes) if its
restockable add the returned product back to the total inventory for
that ProductID.

This is probably not something you want to do every time someone views
how many of a particular product are in inventory, as it would produce
unecessary server load... I would update the inventory list maybe once
per day or something so this query wont run all that often.  However
with 100 orders a day you could probably get away with a lot... and
you are using access as it is, so I am guessing performance is not a
great concern.