Simple Stock Control (1 Viewer)

[timbl]

Help needed with Simple Stock Control

Hi, help needed for holiday project!!
I am trying to create a very simple stock control database for controlling the stock level and amounts of fabric used from rolls.
I need to log the amount of cloth used, update the running total of cloth left on the roll, record the job number, how much was lost due to flaws/errors etc.
I can create tables forms etc but I have no idea how to create a simple running total field that updates when a job has been cut.

I am not a programmer but have created databases before using the help on this site.

My database will consist of just 2 tables
Cloth table with fields such as supplier, batch number delivery date etc and another table for useage where I will have a running total, Job number, amount used, amount lost, date cut, cut by etc

Examples that I have seen all seem over complicated for what I need to do. Any help would be much appreciated.

 

[jdraw]

My database will consist of just 2 tables

Write a description of the business processes as you visualize them in plain English.
Identify what is involved in each step, and how the steps relate to each other. Basically, build a blueprint or artist's concept of the business your database will support.

In very general terms it sounds like you have:

Supplier(s) of Rolls of Materials (purchase of stock)
Customer(s) who buy units of Material (sales)
A variety of types of Material(s)
Special Order(s) of specialty Material(s)
ReOrder of Material(s) in anticipation of Sales
Employee(s)
Possibly Invoice(s)/Credit

Just some thoughts for consideration.

 

[timbl]

We are sailmakers and have stock of different rolls of fabric which is used to cut for new sails.
All I want to do is to create a database to record the amount of cloth cut for each job and to keep a record of the amount used and the amount left on each roll. No need for anything else such as invoicing etc just a very simple way of keeping a record of the amount of cloth left on a roll and which job number it was used on.
I will maybe add further functionality at a later date such as suppliers, reorder levels etc.

 

[CJ_London]

Examples that I have seen all seem over complicated for what I need to do.

Perhaps, but once you actually start using a system you may find that it needs to be more complicated. But to get you started you need a minimum of two tables :

tblCloth
ClothPK autonumber
ClothName text
BatchNo number
DeliveryDate date
...

tblTransactions
TransactionID autonumber
ClothFK long
TransactionDate date
TransactionType text
CutBy text
Quantity number


When you receive a roll, enter it into the transaction table - transactionType would be something like 'goods in' - quantity would be something like 50, plus date received etc

When someone takes a length for a job they would complete all fields and enter the job number in the transactiontype field and a negative quantity - say -10

if there is wastage a record would be completed with say transactiontype = 'wastage' and a negative quantity - say -2

for the amount left on the roll, the query would be at a minimum a group by query on tblTransactions, grouping on clothFK and summing quantity, other fields would not be used.

If you want to know more details about the cloth, create a new query on tblCloth and join it to the the groupby query on ClothPK=ClothFK

 

[timbl]

I Have my tables already defined...

tblCloth
ClothID
BatchNo
RollNo
DelDate
Supplier
ClothName

TblUseage
UseageID
ClothID
RunningTotal
JobNo
AmountUsed
AmountLost
CutBy

Created forms etc.
I just don't know how to get the RunningTotal field to calculate and update after I enter the amount used etc.

 

[jdraw]

You use the transactions to get totals.
Rolls coming in are positive transactions.
Job quantities from the Rolls are negative transactions.

Total on a Roll = Starting Amount - Sum(Job quantities)

Do NOT store Totals in table(s). (RunningTotal)(TotalRemaining)

Totals can always be calculated using queries when needed.

I suggest you model the big picture (invoices etc) but implement only what you need now. You will know where the "possible enhancements" fit, without having to start over because things weren't anticipated.

You should read this article before going too far, if only for reference.
Good luck with your project.

 

[timbl]

Okay, thanks I will not store totals in tables.
How then do I calculate the values needed? What is meant by "You use the transactions to get totals."?

 

[jdraw]

See this for similar question/answer.

 

[timbl]

I have been battling with this for hours and made no headway. I anyone can help it would be most appreciated.

I have attached the very basic db.

Thanks

 

[jdraw]

Here's a sample data model. It may not suit your needs since some facts may be unknown.
Do all Cloth Rolls come in same width?
Do you use different widths on same Job?
Do some Jobs use multiple colours?

The kind of queries envisioned are:

Starting Amount of Cloth on a Roll

Select RollSize from Cloth where ClothId = yourRequestedCloth

To get total of ClothUsed, sum up the amount of cloth used by all Jobs using that ClothID

Select Sum(ClothUsed)  from Job
WHERE ClothID = yourRequestedClothID

 

[timbl]

Not all rolls of cloth are the same width.
Multiple rolls of different cloth could be used for a single job.
A roll of cloth will be used for multiple jobs.
It is primarily to be a cloth database to record the stock level and which job numbers the cloth was used for.

All I'm trying to do is create a database to replace a scrappy bit of paper that records the stock level of a roll of cloth.

Trying to explain it simply - We have a piece of paper (Stock Sheet) for each roll of cloth that is filled in when a new roll of cloth comes into the building.

This stock sheet has all the info about the cloth on it.
Cloth Type
Delivery Note No
Supplier
Width
Roll No
Batch No
Lot No
Total amount of cloth on roll when new.

Then each time some of the cloth is used for a job the following is recorded on the stock sheet.
Starting amount (Length of cloth left on the roll either from previous job or if a new roll the total amount when new)
Job No
Date cut
Who cut the cloth (Employee)
Amount of Cloth used for job
Amount of cloth lost due to flaws or errors
Total amount of cloth used for the job (Sum of above 2)

So all I am trying to do is create a DB to replace this all of these bits of paper.

I am happy with creating the tables etc. I just cant seem to create the functionality to calculate the rolling amount of cloth left on the roll after every job.
I hope this helps.

 

[jdraw]

Since you use/could use multiple rolls of different cloth on a Job, then you need a Table involving JobUsesCloth. (And the model changes as attached)
eg Suppose Job 17 used
. 20 yards of Roll 28 with 2 yards flaw/error
. 3 yards of Roll 5 with 1 yard flaw/error
. 8 yards of Roll 4 with 0 yard flaw/error

So Job 17 used 34 yards of cloth. (31 yards in product, 3 yards flaw/error)
Let's say:
Roll 28 was brand new containing 100 yards of cloth.
Roll 5 had 10 yards of cloth before this Job started, and
Roll 4 had 16 yards of cloth at the start of Job 17.

So after Job 17, you now have
(100 - 20 -2) 78 yards on Roll 28,
( 10 -3 -1) 6 yards on Roll 5, and
(16 -8 - 0) 8 yards on Roll 4.


You may currently use a scrap of paper, but when you break down your requirements, you'll see that 2 tables are not sufficient.

Length of cloth Remaining on Roll = Original length of cloth on Roll
                                                   - Sum(that cloth used in all Jobs)
                                                   - Sum(that cloth that was flaw/error)

 

[timbl]

Thanks for your help. You seem to understand what is required but...
As I said I am not a programmer and so would like to know from a technical point of view exactly how to do this. I can create tables and relationships etc but can not see how to create what is needed to calculate and show on a form the amount of cloth left on each roll after each time some of it is used on a job.
Do I need queries? What code is needed? Where does it go? etc

 

[timbl]

So I have created the DB with a couple of "rough and ready" forms and a query.
In the query you will see a "RollingTot" calculated field, but it does not work like a running total, instead just a total for each cut job and does not take into account the quantity of cloth cut in previous jobs.
Also in the Form "FrmUseage" there is a subform "SFrmUseage" with the field "RollStock" that I want to show the running total of the amount of cloth on the roll after the "Used" and "Lost" fields have been filled in.

If there is another way of showing the up to date quantity of cloth left on the roll please advise.

I hope this makes sense, remember I am not a programmer so simple instructions would be appreciated.

Many thanks

 

[CJ_London]

update the running total of cloth left on the roll

Am I missing something - surely you are only interested in the stock you have now, not three weeks ago.

And if you wanted to know the stock three weeks ago, you would simply use the query I provided but with a criteria to only include records where the transaction date is earlier than three weeks ago.

You would never include a running total in a table - what happens if you amend a quantity? You would need to update all the subsequent records.

Also in the Form "FrmUseage" there is a subform "SFrmUseage" with the field "RollStock" that I want to show the running total of the amount of cloth on the roll after the "Used" and "Lost" fields have been filled in.

Try using the DSum Function for the before amount, the after amount is the before amount + transaction amount

Dsum("Quantity","myTable","ClothFK=" & me.ClothFK & " AND TransactionID<>" & Me.TransactionID")

OOPs - see there has much gone on since started to respond

 

[jdraw]

If you really want a Running Total in query, here's info from M$oft
http://support.microsoft.com/kb/290136

 

[jdraw]

I have looked at your latest database. I have used your tables to rebuild some tables as suggested in the data model I provided in post 12.

The key to database is to make sure your data model matches the business it is intended to support.
As others have told you inventory is not simple, even to experienced developers, it can be complex.

I have added some tables to your database based on the model I provided. These have a "J" in the name. I used your tblUseages and some queries with names like mt... to build these tables. The mt... represents MakeTable queries which take data from your table and create a new table.

If you look at query ClothUsedToDateAndRemainingByClothID, it calculates the amount of Cloth Used and the amount of Cloth remaining on the roll.

I realize you are new, and based on posts here and in UA, and not familiar nor experienced with SQL or Access.

I have attached a zipped version of the latest data base I saw. My version is called Cloth Stock2.mdb

Please take a look at the database, the tables(I did not change your tables) and the query and let me know if this is what you are looking for. As others have said Inventory isn't trivial, if you want to pursue this I am willing to help (either here or in UA). But it will require effort on your part.

Good luck with whatever you decide. ( I do participate in several forums).

 

[timbl]

Thanks very much, I feel like I am getting somewhere now.
I have changed your query "ClothUsedToDateAndRemainingByClothID" so that it uses my table "TblUseage" and it seems to work although it does not update straight away when I add a new job using my FrmUseage.

How do I get the RemainingOnRoll Field from the query to appear on my Form "FrmUseage" and to only show the relevant figure for the displayed ClothID.

Thanks

 

[timbl]

I have managed to put the fields from the query into my form but they are not updating when I add a new job or change the values in existing jobs.
The values are correct when I close the form and reopen it but there is no "live" update.

I have added a Refresh Button to manually refresh the form which seems to work but it would be nice for it to happen automatically.

Getting there!!

Found out how...
Private Sub Form_AfterUpdate()
Forms!FrmUseages.Form.Requery
End Sub

Thanks for the help with that query.

 

[jdraw]

Do you ever use 2( or more) different rolls or Lots of the same cloth for a Job?
I purposely did not use your tblUseage, since I feel it is not based on your requirements. I feel that tblJobUsesCloth does meet the requirement when you use multiple clothIds per job.
Glad you are making some progress.
Want to post a copy of your latest database?

I think you should look carefully at the query and the table involved, to see how the useage can be calculated from the jobUsesCloth table.

Yes changes often don't appear until you requery the control, the record source or the form.