Stock Control and issuing database.

[Ducatisto]

Hello All,

I thought it best to ask the Access Sages on which would be the best way forward with my needs. I work for a company which issues workwear (approx 30 lines) to its colleagues (800+). What I am seeking is a database which can have inventory inputted (that is what was purchased and received into the system) and also able to cope with workwear issue to each colleague. For argumaents sake, Jim requsted a pair of boots and 2 pairs of trousers and that information was entered against his name with relevant time/date, and also subtracted those particular items from the inventory showing what is left for each item. There are so many attributes to this I got completely lost when trying to start something, as well as having those lines each line has many sizes (especially boots!). If anybody knows of a database which closely matches our needs I would be most grateful. Of course we would be more than happy to pay as it will streamline our archaic system we have in place now (pen and paper!).

Regards.

 

[georgedwilkinson]

Pay how much? There are tons of systems out there that can handle this. The issue is cost.

 

[Ducatisto]

Pay how much? There are tons of systems out there that can handle this. The issue is cost.

I do agree with that there are a lot out there, but so many deal with the actual purchasing and I do not need to have that element in it. But it is all about if it can handle what I want and how much? If I need to pay £50 for a simple database so be it, but I do not need a £500 database if it offers more than I need. It is all about reasonable cost.

Regards.

 

[georgedwilkinson]

Why not use the Northwind database as a starting point and modify it to handle the size?

 

[neileg]

Why not roll your own? The key to this is thinking about the hierarchy.

Suppose you have a size 10 Steelcapped boot. The lowest piece of data is the size so that dictates the level at which you record the data. It has the attribute of being a Steelcapped and this belongs to the family of boots. So your tables will look a bit like this:

tblItems (where you identify the items you need to track)
ItemID autonumber PK
ArticleID FK link to tblArticle
Size
Colour
SupplierCodeNo
etc

tblArticles (contains the model/range details of the clothing)
ArticleID autonumber PK
ArticleName
FamilyID FK link to tblFamily
SupplierID FK link to tblSupplier

tblFamily
FamilyID PK autonumber
FamilyDescription

Then you need a table to hold the transactions

tblTransactions
TransactionID PK autonumber
ItemID FK link to tblItem
TransactionType
TransQuantity -ve value if an issue to an employee, +ve if a receipt of stock
TransDate
EmployeeID FK link to tblEmployee

This is just a start but it's not that complex.

 

[Ducatisto]

Why not roll your own? The key to this is thinking about the hierarchy.

Suppose you have a size 10 Steelcapped boot. The lowest piece of data is the size so that dictates the level at which you record the data. It has the attribute of being a Steelcapped and this belongs to the family of boots. So your tables will look a bit like this:

tblItems (where you identify the items you need to track)
ItemID autonumber PK
ArticleID FK link to tblArticle
Size
Colour
SupplierCodeNo
etc

tblArticles (contains the model/range details of the clothing)
ArticleID autonumber PK
ArticleName
FamilyID FK link to tblFamily
SupplierID FK link to tblSupplier

tblFamily
FamilyID PK autonumber
FamilyDescription

Then you need a table to hold the transactions

tblTransactions
TransactionID PK autonumber
ItemID FK link to tblItem
TransactionType
TransQuantity -ve value if an issue to an employee, +ve if a receipt of stock
TransDate
EmployeeID FK link to tblEmployee

This is just a start but it's not that complex.

I thank you for your responses but the reason why I have posted here is the urgency needed to complete or acquire a database to suit our needs. I did start but the senior management want it done "yesterday" so not really putting undue pressure on me! I will re-start my database and will take in what you suggest but time is very much against me!

Regards.

 

[neileg]

If you really want to buy a system, this isn't really the right place to ask! A custom developed application isn't going to be £50 and even £500 isn't a lot of money.

 

[The_Doc_Man]

Were it not that they are required to sign my timesheet approval box, I would SHOOT any manager who dared to tell me (with a straight face) "I want it YESTERDAY" - or the equivalent thereof.

Remember that time is money. Therefore, whether you build or buy, you ALWAYS get what you pay for.

 

[DCrake]

When I encounter comments like that from clients or management I tend to give the response... "Do you want it on Thursday? or do you want it doing right?"

By "cobbling" something together to meet yesterdays deadline is a sure fire recipe for disaster. Straight away they are going to find a fault with it, even if it is something that was not even discussed earlier, all because someone moved the goalposts.

Don't be sucked in to the position of underdog, tell them if you want it right and you want it to match the company's business rules, then they will have to wait....

You are only the bricklayer you are not the architect, get them to supply the spec and you will build it as per the spec. That way there will be no falling out.

CodeMaster: