Inventory control

[Shop Girl]

I have a db based on the original Inventory control template. I've modified it to do all most all that I need thanks to reading many of the posts on this website.

I have stock products that are all contained in the Products table. All the transactions are recorded in the Inventory Transactions table. And here is where my problem lies. Some of my stock products can be used as stock products for a specific club and because they have a logo they are a different product no.

What currently happens is I take off the stock of the product that is going to be printed with the logo of the club (via the shrinkage field). Then I have to go to the Club's product no and add it on there via the ordered and (when it comes back from print) received fields.

Has anyone got an idea how I can automate this? It can take an age to complete a print order!

I should also point out that the products are sold via a shop - and the shop uses the database to create the receipts for the customers using product ID. I think this is why I'm nervous about attempting trying to change the current set up.

 

[ElsVanMiert]

I am too stupid to understand your problem, but I have the impression that something is wrong with the database architecture.

 

[Shop Girl]

Yes the db will need restructuring - all my products are currently in one table and all their transactions in another - so I know I'll have to split them from each other.

I'm just struggling to get my head round how to do the take it from one and adding it to another bit.

Also when doing receipts where the operator has to enter the products sold - if the products are in seperate transactions tables how could I do it? - would I have to have two sub forms on my receipt form - one for plain stock and one for printed stock?

Sorry for being really dizzy - normally I can work my way round these things but I've totally lost the plot this week!

 

[Rich]

Stock is stock, printed or plain, why do you want separate tables for each?

 

[Shop Girl]

Ok - I've been up half the night thinking and I've come up with this.

If I have a field in my Products table called Base Stock ID for me to put the plain stock ID number against the printed stock item.

Could I then attach code to the # Ordered field in the transaction sub form on my purchase order form that does this:

If [Base Stock ID] is not null then create new entry in the Inventory Transaction table for Product ID (the code will then pick this number from the [Base Stock ID]. Then enter in the [Shrinkage] field the number contained in the [#Ordered] field and also enter the transaction description from the purchase order part of the form.

Do you think this would work? If so can you please help me out with the code.

Thanks

 

[Rich]

Have you looked at the samples provided by Access, Northwind and Orders, or even the Inventory control db set up by the wizard?
Doesn't shrinkage refer to "lost" stock, as opposed to sold stock?

 

[Len Boorman]

Some time ago I was involved in the implementation and running of a multi environment MRPII system. We were also taking a standard product, printing on it and then returning to stock.

Firstly it is a different identity when it is printed. This you cannot get away from.
Secondly when you take it out of stock to print it then you must reduce your stock of the unprinted item
Finally when it is printed it must return to stock the new identity.

These are fundamentals that you cannot get away from.

I would eb very wary when changing the system.

If you have a large number of print orders then examine the way in which you handle these.

Firstly when raising a print order look at automatically deducting the stock

Then when receiving the order back in automatically update the stock.

What you may need is a table that gives the structure of the printed item i.e.
one Printed shirt type Fred is made of One White Shirt Standard

Sounds a little odd but this is the manner in which an MRPII system handles this type of situation. You then have the necessary information to deduct from stock the item you are sending to the printer.

Be prepared also that a standard item could be used to make many different printed items each with their own identity.

Good luck

Len B

 

[Shop Girl]

In answer to Rich - sold stock is true when sold to the general public - we use the shrinkage field to reduce the plain stock item that is going to the printers it then takes on the printed stock item product ID and becomes sold when its sold - if we didn't use the shrinkage field we'd be selling it twice! Also my database was started from the Inventory control wizard!

In answer to Len - what you detail is exactly how the system should work - I have also worked in the manufacturing sector and understand completely what you say however - what I am stuck on is the how to do it! I have had no experience of using what I would call component stock items and simply do not know where to begin - given that the database as it stands now works beautifully and the only thing that is letting in down is this currently long winded procedure we have for taken plain stock off and adding it to print stock.

For minimum disruption to the current design - I would like to be able to do it via the print purchase order form.

Does anyone know if I would be able to do it via code as I asked earlier?

 

[Len Boorman]

I think that you have some work to do but feel there is a solution there. If you are generating a print order then you must have some sort of demand for the printed item. If you know what unprinted item you need to take from stock to make the printed item then you could use a query to calculate the total amount required to be deducted from stock and thus update the stock of unprinted items.
Similarly receipt of the printed item could also generate a stock update.

This would certainly take a little time to flesh out but the way I see it is perhaps:
Printed item requirements are stored in a table of UNRELEASED PRINT ORDERS, link this table to a structure table. (1 off printed item is made of 1 off unprinted item). From this you can generate a stock removal requirements.

When you generate the print orders onto paper i.e. they are now released and sent to supplier you can fire an update of the stock of unprinted items.

Printed items returned I think would actually be easier to action.

I think that is the process you have to mimic,
I would work on a copy of the database to finalise the process and be certain that it works accurately.

I suggest you sketck out the precise process that you wish to mimic and then set about emulating each part of the process in sequence. Small steps and refine as you go.

I do think that you will need some intermediate stage of data collation between the demand for a printed item and the actual printing of a print order.
I think the process could be refined to the point whereby the firing of the necessary queries could be sequenced such that it is literally an online process.

Not a simple task but one that I think is achieveable if approached logically and in small steps.

HTH

Len B

 

[Shop Girl]

Thanks for your help Len.

I will follow your thinking and see if I can get it to work.

I may be back....

Thanks once again