Please help

[confused_Pete]

Hello,

First of all thanks to anyone who might be able to offer any assistance.

I have a 6 table stock database which is in access 2000. I have a main store of the stock called tblitem (which contains item_id, item_quantity, description etc...), i have another table tblorderquantity this table contains the fields: Item_ID, Order_no and Quantity_ordered. My problem lies that when stock entering the system comes through via tblorderquantity i need to add the quantity that has been ordered in to tblitem. What options are available to do this? as i seem to be getting no where fast.


Cheers for your time

Pete

 

[The_Doc_Man]

Your problem is common to home-grown inventory systems. You should rarely rely on a "quantity on hand" field in your database for an item.

The excruciatingly correct way to do this is to have a series of tables that involve inventory transactions. Some add inventory because of a receipt of shipment. Some add inventory by restock events. Some remove inventory by drawing it out for a customer operation. Some remove inventory due to overhead actions. And you have one more valid record, the inventory amount determined on a particular date as being correct.

So what you do is make a union query of all of the possible transactions against an inventory item. Each sub-select in the union query is for ONE and ONLY ONE item in your parts list. You add the counts for things that add to your inventory and subtract the counts for things that remove from your inventory. You make the query so that it only considers transactions after the last inventory update of the item.

Then, the issue of something not being counted can be resolved by making sure it contributes to the union query.

You use the item's inventory count field as a reference point or starting point to begin scanning for more recent transactions against that entity. That way, if the item has not been recently transacted, the only thing in the union query is the inventory entry. Otherwise, you have a total of several items.

Then, of course, you make the union query a SUM query for each item.

 

[confused_Pete]

Hello,

Thank you for your reply. In this particular system (analysis, theory) stock is entered into tblItem through tblorderquantity, and then later taken out through tbljobno. is there any way that this can achieved through a form, maybe even through controls on a button?

I do appreciate the help that was given however i am relatively new to access (previous experience being a few tables, totals etc..)

does anyone have any links to any other access databases that have been constructed in this way and maybe solved by using a union query or other method?

Cheers for your time

Pete

 

[confused_Pete]

Hello,

Does anyone have any advice on this topic? I haven't been able to find any information by trawling through numerous books. What is the easiest way to add or subtract an amount in a field from another field in a seperate table?

An example being, you have a main store of stock tblitem. Stock entering the system comes through via an order, this information is held on a table tblorderquantity, the stock is added to the level in the main store tblitem(the part i can't do). It is then subtracted at a later point when it is assigned to production(more than likely identical to the part i can't do just subtracting rather than adding) tbljobno. There must be an easy way to do this or am i just the complete idiot i think i am?

the reason i am not changing the structure of my database is that the systems analysis and normalisation has shown that this is the logical way to create this database.

Cheers for your time

Pete

 

[KevinM]

No it is not the way to create the db.

You will run into numerous problems doing it that way.

I agree with Doc Man, you need a series of transactions to create your stock levels.

Not sure how many trans tables you need, it depends on your company system.

For instance where I work we have a simple stores system where items are ordered from suppliers and booked out to staff.

These two trans won't fit into one table so I created two, one for Stock deliveries and one for StaffIssue/Returns.

I use a UNION query (as menioned by Doc Man) to join the two tables and calculate stock levels.

Any stock adjustments should have a transaction made against them and flagged as such for audit reasons.

You can have your own company in the suppliers table (which is a perfectly 'normal' thing to do) to create these transactions.

 

[confused_Pete]

Hi guys, thanks for your help.

I decided that i was unable to solve this using SQL or QBE so decided at giving VBA a try, with perfect results, see code below:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Order_no.SetFocus
Set rsOrderQuantity = CurrentDb.OpenRecordset("select * from tblorderquantity where order_no = " & Order_no.Text, dbOpenDynaset)
Do While rsOrderQuantity.EOF = False
Set rsItemData = CurrentDb.OpenRecordset("Select * from tblitem where item_id = " & rsOrderQuantity!item_id, dbOpenDynaset)
rsItemData.Edit
rsItemData!item_quantity = rsItemData!item_quantity + rsOrderQuantity!quantity_ordered
rsItemData.update
rsOrderQuantity.MoveNext

Cheers for the other way of doing this, i will give that a go at a later date

Pete