Challenge...Complex Code/Task!

[james_IT]

Hi guys,

I'm still working on my carpet/flooring stock control system.

- On arrival some carpet is cut into standard room sizes and sold (no problem, its just logged as individual pieces of stock)

- On arrival of some stock, however, the whole roll is put on display to allow customers to cut however much they need.


Want i want is an automated system/code that

1) Unticks the box (In Stock? Yes/No) for the original stock piece.
2) RecordsUses values for length/width required from a customer purchase form
3) Creates two new pieces of stock 1) The customers sold piece with the required length/width 2) the remaining piece of carpet.
4) Creates an audit trail/history of the original piece of capret so employees can trace how it has been cut.

I'm guessing stock numbers should be related e.g. EMP10a, EMP10b, EMP10c

If anyone thinks they can give it a go i have attatched a sample of a simplified version of the database. If anyone could remake some of the database in the correct way or tell me how i could do it. Also, if anyone has got any ideas on what would make the database good please let me know.

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=15300&stc=1&d=1161959191
db1.zip

Thanks in advance for any help you can offer!

 

[gemma-the-husky]

Can you confirm - You have a roll of a certain length . Therefore when a user has say, 6 metres, he ALWAYS takes a full width slice.

eg you have a 50 metre roll, you sell 6 metres, you are left with 44. - you are not cutting strange shapes.

if so, don't try to book out and book in the carpet. Store the issues keyed against the original roll. That way you can identify everyone who purchased from that roll, and identify how mch has been sold in total, to get at the remaining length.

ie purchased roll no 123 50 metres

sales 22/9/06 sales no 1234 6 metres roll 123
sales 28/9/06 sales no 1239 8 metres roll 123
sales 11/10/06 sales no 1248 5 metres roll 123

sales to date 19 metres
length remainnig 31 metres

 

[gemma-the-husky]

Further thoughts - The same principle probably still applies to your pre-cut rools - I presume they are precut to fit existing orders, not in case someone wants a 6metre piece, and someone wants an 8 metre piece etc.

It seems somewhat perverse to split a roll, and treat as several pieces of stock.

Hope that makes sense.

 

[james_IT]

yes you would be right in thinking that it is only length that is a variable. width remains either 4 metres or 2 metres (depending on type of carpet) but it is always the length that changes, never the width.

Also, sometimes carpet is cut into standard room sizes or pre ordered sizes. This is the way the business has run for many years and proves to be quite successful.

I kind of get where you are coming from, i just dont undertsand how i can encorporate it into my database. bit confused...

does it need another table. are my tables/relationships set up wrong?

thanks for your help

 

[gemma-the-husky]

its hard to tell with an empty database - you really need to try and use it to see friendly it is. Design is often a process of stepwise refinement - the first go is not quite right, so you build on that with an improved version.

However, in principle, I really don't think you want to be creating an individual stock record for each cut piece, unless there really is a special reason for doing it. It seems counter-intuitive to me.

I would have thought you just need to link the cutomer's purchase order, which would include the quantity purchased, to the roll you are using to fulfil the order, which immediately gives you all the pieces of information you required.

 

[james_IT]

no, i agree, i just cant think how to successfully join the tables or how to arrange the tables. do you have any pointers to how YOU think it would best be organised? thanks