Reservations & Stock Quantities (1 Viewer)

[hassanlabban]

Hello Friends
i am not a newbie but i am facing a small issue and seems not able to get around it.
i have a database (warehouse management system) which i developed. now the need risen to create a kind of reservations table or module for stocks which are actually required to be reserved or put on hold, but still are not out of the total inventory count.
i tried creating 2 tables (tblTransactions & tblReservations), and things are going very well when it comes to registering a reserve, and then converting the reserve to a transaction.
the only problem am facing is when i want the query to show me the current stock on hand from the tblTransactions and in the second column to see the quantity reserved from the tblReservations.
i tried the Union and Union All queries, but am getting a multiplied number of data which is giving me wrong totals in the end result.
any ideas on how to get this solved or getting around with it? specially that this database is having more than 50,000 records so far registered on it and its increasing, and changing the database engineering is not an option at the moment.
i would really appreciate your help and guidance and ideas in this

 

[jdraw]

Show us a jpg of your tables and relationships.

Please overview the business who, what, where, when, how much, and how often sort of thing.

--you must have Customers and/or Suppliers
Orders, OrderDetails Products/Items----

I would not have a transaction table as such, calculate based on Products In and Products Out via query as needed.

Seems like your Orders fall generally into
-Real Orders and Reserved/Potential Orders

You could experiment (in your TEST Database) with a ReserveOrder with a IsReserveYN flag, and ReservedProducts with similar flag.

You can include/exclude the flagged values as per your rules.
When a Reserved Order is "firmed/real", then change the flag (remove the Y value).

Just some quick thoughts for consideration.

You know your environment and we don't so experiment. I suggest you set up ome realistic test data and work through some "mocked-up scenarios" and let us know what you find/need.

Good luck.

 

[CJ_London]

is there any reason why your tblTransactions cannot have a 'reserved' flag in it. Then you calculate current stock to either ignore or include the quantity when calculating current stock depending on the flag setting.

 

[hassanlabban]

Hi again all
the flag seems a good idea, but i was thinking of keeping it on a separate table. am not sure if this is better than flagging or no.
incase i went with flagging, can this logically be changed through an update query to change the flag from the reserved to the movement type of transaction?

thanks
Hassan

 

[CJ_London]

I suppose it is a matter of choice, but I have all transactions in one table, be they purchases, sales, stock adjustments, stock takes, reservations, goods ordered etc

If goods are reserved, then at the time of shipping, change the flag. You could use an update query, but the user would be updating on a form.

Typically I use a shipped date field or deliverynoteID field as a flag. In many of my clients, customers order multiple items, but not all are in stock so decision is made to ship as one order or do multiple shipments. In either event, a blank shipped date or deliverynoteID means it is reserved - when shipped, the field is populated by the user completing the date or a delivery note.

It's a bit more complicated where customers order 10 items, only 5 are in stock, so these are shipped and the balance reserved

And simpler if customers are ordering a specific item (i.e. a specific item with a specific serial number).

All depends on how your db is structured and your business rules. But still say overall it is simpler to keep all transactions in one table.

 

[hassanlabban]

CJ thanks a million buddy.
You gave me an insight of what i might be facing. In fact using a form to update is the best option as i have as well same cases like your where some items are shipped and others not.
Looks like i found my way.
Thanks again to all ya guys