Stock (Again)

[Chaz]

Ok, going to pull out what remains of my hair.

I have a table that records incoming stock - no problem. I can add these per stock type, no problems.

When I issue stock, I tie this to a job. I could these by filtering a query and then get a count of.

Problem is Im struggling to find a method by which I can get these 2 totals to work together (Stock type, qty less stock type used).

The problem is that there are multiple stock options when each job is assigned. Some are common, most are not.

Somehow I need to look at updating my stock transactions table when stock is issued - perhaps stock_description with -1 in the stock_type.

Another method - perhaps very tedious is to ask the user which unit he has taken and tick those taken and ignore those not. Problem is this takes too much time and dont want to give the user too many options.

Ideally Id like to report per stock_description but I cant find a way to tie the incoming stock to the outgoing stock.

Argh - any help please? Even just guidance on what may work (type of queries, methods to do stock in and out and how to filter / count). Thanks.

 

[Niroth]

Honestly, I don't fully understand what you meant by "tie the incoming stock to the outgoing stock", maybe you can explain it again with examples of an operation you might want to do but have problem with?

 

[HiTechCoach]

I would recommend this:

Inventory Control: Quantity on Hand

Hope this helps ...

 

[Chaz]

Honestly, I don't fully understand what you meant by "tie the incoming stock to the outgoing stock", maybe you can explain it again with examples of an operation you might want to do but have problem with?

Sorry - was a bit of a frustration rant. What I meant - there must be a way to tie all incoming and outgoing transactions to the same kit / part number. for incoming stock this is easy - simple form tied to a table that captures quantities which can easily be calculated.

What I struggle with is tieing the stock booked against jobs and recording those as 'outgoing' or 'negative value stock'. Maybe I just need to understand how to build the query to update records in the table that has the incoming transactions. I suspect the answer will be relatively simple and I probably just need to step back a bit.

Ill look at that site - think I saw that but was careful to start coding as I prefer to understand what I am doing.

 

[neileg]

If you store the stock in and stock out transactions as positive and negative entries in the same table, you just need to sum by part number to get the current stock level.

 

[Chaz]

If you store the stock in and stock out transactions as positive and negative entries in the same table, you just need to sum by part number to get the current stock level.

Understood. How do I tie the allocation of stock to a job and 'make' that turn into a record with a negative value in the stock_transactions_table?

What I have is:

t_stock_items_and_descriptions which is the detail of all the stock we use. I dont have quantities here.

t_stock_transactions which is currently fed by a form which captures incoming stock which simply looks up the stock item from the first table and then records a positive value (say 200) for units that come in.

This is when it gets messy ....

I have t_services which is a table that records all the detail of each job that we do. The storeman then allocates stock, looked up from t_stock_details (first table above). At present there is no stock value or count number inserted by the storeman. What I have is 10 fields for various bits (IDU x 2, ODU x 2, Trib Cables x 2, Antenna x 2, PSU x 2) as these are the items we issue per job.

All stock issued will be at least 80% of the list above, the PSU are optional at times.

Its easier for me to ask the storeman to click on a form that allows him to pick each version of IDU / ODU / Antenna etc from a drop down box, again tied to the stock_detail (first table).

I have since written queries that can count the number of IDU, ODU etc used which is also then a bit more complicated as we may have 4 different type of ODU, which is counted by a filtered query.

How do I tie the stock_transactions table to these outgoing stock counts or should I find another way to deal with the allocation of stock to each job. I do realise that if I simply as the storeman what quantity of each of the roughly 20 options were taken, then its fairly simple as these could probably just be recorded as outgoing stock in the stock_transactions_table and summed accordingly.

Thanks for your time.

 

[Niroth]

I don't understand why you need to have ten fields for the various parts?

How do I tie the allocation of stock to a job and 'make' that turn into a record with a negative value in the stock_transactions_table?

Make all value that is entered in the outgoing stock form negative, for example:

outgoingStock afterupdate()

me.outgoingstock=-outgoingstock ? I think that should do it.

 

[Chaz]

I don't understand why you need to have ten fields for the various parts?

How else do I capture 10 different parts of stock needed per job?

Should I rather just be looking at tying the job number to each transaction and have 2 fields - one for the stock type and one for the quantity?

The storeman can issue 10 different parts to each order, just thinking how to capture this and then query when needed.

 

[Chaz]

Nevermind - think I have an idea - being 'blonde'. That said - let me see if I get it working.

 

[neileg]

The reason you don't have 10 fields is that when you need to have 11 you will have to redesign your database.

What you should have is 10 records in a separate table. This process is known as normalisation. Youu need to research this. Wikipedia is a good place to start.

 

[The_Doc_Man]

1. Normalization is always good. Regardless of what else you do, look up stuff on database normalization. It is ALWAYS worth it in the long run.

2. One way to tie together disparate tables (i.e. the inbound stock table somehow doesn't look like the stock-issue table) is to build a UNION query and allow the inbound to be summed normally, but use the SQL of the UNION query segment for outbound stock to be the negative of the actual number. You have to supply a common name for all fields in the UNION, but since it is a query, formulas are allowed. Then you can do summations based on the UNION query rather than directly on the tables.

Example (simple-minded, four tables, three fields)

SELECT QTYIN as QTY, ITEMNO as PARTNO, DATEIN as XACTDATE FROM SHIPIN UNION

SELECT -QTYSOLD as QTY, PARTNO, DATESOLD as XACTDATE FROM SOLDIT UNION

SELECT QTYRTND as QTY, PARTNO, DATERTND as XACTDATE FROM RETURNEDSTUFF UNION

SELECT -QTYFELLONTHEFLOORANDBROKE as QTY, STOCKNO as PARTNO, DATEBROKE as XACTDATE FROM MYCLUMSYACCIDENTS ;

This gives you three columns as QTY, PARTNO, XACTDATE based on four sources of information - inbound shipments, sales, returns, and inventory shrinkage. Then just sum this query on QTY group by PARTNO where XACTDATE < some arbitrary date to get running date totals of the quantity of each part number.

 

[Chaz]

1. Normalization is always good. Regardless of what else you do, look up stuff on database normalization. It is ALWAYS worth it in the long run.

2. One way to tie together disparate tables (i.e. the inbound stock table somehow doesn't look like the stock-issue table) is to build a UNION query and allow the inbound to be summed normally, but use the SQL of the UNION query segment for outbound stock to be the negative of the actual number. You have to supply a common name for all fields in the UNION, but since it is a query, formulas are allowed. Then you can do summations based on the UNION query rather than directly on the tables.

Example (simple-minded, four tables, three fields)

SELECT QTYIN as QTY, ITEMNO as PARTNO, DATEIN as XACTDATE FROM SHIPIN UNION

SELECT -QTYSOLD as QTY, PARTNO, DATESOLD as XACTDATE FROM SOLDIT UNION

SELECT QTYRTND as QTY, PARTNO, DATERTND as XACTDATE FROM RETURNEDSTUFF UNION

SELECT -QTYFELLONTHEFLOORANDBROKE as QTY, STOCKNO as PARTNO, DATEBROKE as XACTDATE FROM MYCLUMSYACCIDENTS ;

This gives you three columns as QTY, PARTNO, XACTDATE based on four sources of information - inbound shipments, sales, returns, and inventory shrinkage. Then just sum this query on QTY group by PARTNO where XACTDATE < some arbitrary date to get running date totals of the quantity of each part number.

Many thanks - will look at this a bit later when I havre some time.

 

[Niroth]

The reason you don't have 10 fields is that when you need to have 11 you will have to redesign your database.

What you should have is 10 records in a separate table. This process is known as normalisation. Youu need to research this. Wikipedia is a good place to start.

Agree. And well working with ten field is just a head ache, and there's no reason to have it. If your serviceman can issue ten parts, then there's should be one record for each one that he issues.

 

[Chaz]

Agree. And well working with ten field is just a head ache, and there's no reason to have it. If your serviceman can issue ten parts, then there's should be one record for each one that he issues.

Understood. I revised this soon after - hence the 'blonde moment' above.

Ill try these unions when I get back from town - I believe it is what I needed to see to help me along ...

 

[Niroth]

From what I understand from your posts (which I'm afraid to say is not very clear to me), I don't think you need to use union query. Just putting the transaction as negative in the same table should do it. Keep things simple - that's my motto. Using union queries and what not when you don't really need them will just create more headache down the road.

 

[Chaz]

From what I understand from your posts (which I'm afraid to say is not very clear to me), I don't think you need to use union query. Just putting the transaction as negative in the same table should do it. Keep things simple - that's my motto. Using union queries and what not when you don't really need them will just create more headache down the road.

I realise my posts arent too clear - finding it difficult to explain how I am trying to get things to work. The problem that I have is that the mechanism for booking stock in and out is different and that is why there is difficulty in doing it.

Ill mess around with it later today and see what I can do. Im probably missing something basic and overlooking a simple way to sort this.

Thanks for both your time.