How to make such kind of query?

[Desperate]

I am trying to make a material management system.
Basically It consists of

-Material Receipt (Material Code,Received Quantity, Stored Location) and -Material Issued( Material Code, Issued Quantity, Issued Location) .

I have a form for the user to enter receipt material and another form to issue material from the stock.

There is another form I should make "Material Transfer". When there is physical transfer between store locations user should be able to update this but I stuck on this point so bad.In example;

A123 50 pieces received to W5A1 location.
A123 30 pieces issued from W5A1 location.
Stock quantity remain 20 pieces in W5A1.

Till this point I have no problem to create a query and show quantity as per each location.

10 pieces transferred from W5A1 to W5A2.
W5A1 remain 10, W5A2 remain 10. I have no clue how to make this. It can be a query like adding to store W5A2 10 pieces like material receipt and issuing 10 pieces from W5A1 but I have no clue how to make this. I wish I explained my problem well and sorry for a long post but this makes me to bang my head to walls for a long time.

 

[namliam]

cannot you " just " issue it from your W5A1 location and then make a reciept in W5A2?

 

[PNGBill]

Not sure how to solve the problem but I would start with a "Stock Transfer Form" that performed 3 activities.

1. It held the total stock count in all locations for a given item
2. It allowed an input of store location & qty for that item for Stock Out
3. ditto for Stock IN

The key here is that being a stock transfer the total stock qty must not change.

You could have a Stock Transfer Table that records the activity with a record number.

Fields would be:
Key/recordnum, date, sku, qty, locout, locin

This would provide a simple audit trail.

Assuming you are not dealing with cost / average cost then the qty issue should be simple.

Sorry can't spend time on detail but if the above helps you to get your head working then good.

 

[Desperate]

Well the transfer form will consist of 3 parameters that user should input.
From_Store (Store for materials to be taken), To_Store (New location), Transfer quantity. As I have 2 tables for material receipt and issued,I will try an append query or an insert into statement in vba code to put transfer quantity in to receipt and issued table at the same time so it will not change the stock quantity. Not sure if there is a more practical logic but so far this is the best I could find. Will update you with the solution and thanks for your time.

 

[highandwild]

This is just like a double entry book keeping system.

Keep receipts and issues in the same table.

Each record has a type. Either it is a receipt "R" or an issue "I", (a credit or a debit.)

When receving stock in from an external source you debit the external source location and credit the internal location.

The external location will always be in the red (will always have a negative balance) unless you send stock back to a supplier.

Internal stock transfers will involve a credit to the receiving location and a debit to the issueing location.

When entering these through the form and press the submit button then the two records will be created. Each record will need to reference the other.

If you make a mistake then do not delete the records just do a reverse transfer.

You do not need to hold the stock count for each location, just calculate it at run time. Unles you are really careful you will be issueing stock that you do not have.

Adjustments at stock take time will just involve a debit or credit for the location into a virtual "adjustments" location. Stock does go missing and unaccounted for.

You just need one form for both issue and receipt.

 

[Desperate]

Well making table was also a real headache for me. My table is more complicated than I explained here. There are too many columns to be in one table. I am working in a field engineering business so there are so many things to be entered both when receiving the materials and issuing them especially for piping materials. At first I made one table.Than decided to split it in to smaller tables since I doubt it may cause performance problems when there is too many data. I know this question should be asked in table subject but started discussion already here so could you tell me if I make one table can it cause queries to take long time when I have millions of data in same table?

 

[PNGBill]

Can't advise on speed of database but I would have more then one table.

At least:

sku table (item). This may have quite a number of fields such as length, weight, bin location (could be another table in itself) pack qty, order qty supplier (preferred or fixed and this could go on - last cost, average cost, suppliers price, currency sale price(this can grow with disc, special price etc) ....

Store Table - locations. Name, address, contact details, (if not in same compound) maybe bin locations ....

TransactionTable date, Ref(PJ, SJ, ST,...) SKU, QTY, ..

Customer Table CustNum, Name, Address, Contact ...

Team Table Your own operator table (if req'd)

Some tables could have a number of Flag Fields which would help your queries and forms keep track of pending and completed purchases, sales, orders and stock transfers.

There would few activities that got away with only one table.

Trust this asissts