Update Query

R

Ratib Hussaini

Registered User.
Local time
Tomorrow, 01:41
Joined
Nov 7, 2012
Messages
31
There is one warehouse and two stores name A and B. as per need some of the products are moved from warehouse to stores and from stores to warehouse and between stores. Company wants the updated list, that how many of the products are in warehouse and stores. Please tell me how to do it, when they move a product from warehouse to store A,, the QTY should be decrease from warehouse and increase in Store A. what tables I require, it can be possible through update query or should I use VBA. Please guide me it is urgent. thanks
 
thanks for the reply, but it didnt help me with the problem. i made following tables
1. Suppliers
2. Products
3. Inventory (supplier ID, Product ID, Location, QTY)
4. Locations
and made some queries and forms for data entry. Just I dont know if they move product from one location to other how to update the inventory. Is it possible through update query or VBA or any other advice. thanks
 
So many questions so lets start with:

Can some of the same product be in the Warehouse, Store A, Store B or a combination? For example:

Product: Wigetts (Large)
Warehouse: 10 Units
Store A : 22 Units
Store B : 12 Units

Product: Thingys (Small)
Warehouse: 0 Units
Store A : 2 Units
Store B : 45 Units

Or will they always be ALL in one place?

Can a single product be sourced from different suppliers?
Does it get a different product code / prefix / suffix for each supplier ?
Do you need to know where the products that you sourced from Supplier ABC are?
Do you need to keep track of batches for the adoption of a first in first out policy?

The answers to these may help to solve the problem.

A simple answer to the question may just be that you run an update query that changes the old location to the new location but that may in fact not meet the company's objectives.
 
thank you for the reply. the answers to your questions are
1. Yes the same product could be in different locations as you showed in example.
2. Yes the single product could be sourced from different suppliers but the product could remain same but the same product could have different colors and sizes and according to that the prices are different. For them product code, color and size is much more important than the supplier.
4. No need to know where the products are sourced from suppliers like ABC
5. No need to keep track of batches.

The newly purchased products are arrived to warehouse first then distributed to stores as per their request, and could be brought back to warehouse, and could be taken from one store to another store as per their need.

I will do the purchasing and sales part but the movement of products from one place to another , i tried but couldnt. Any other question you have please let me know.
 
If product code, color and size are important then you will need to be able to identify from the product code what the characteristics of the product are.

Treat all of these variations as different products and then you will be able to identify excatly what you have in stock and be able to transfer the correct stock. You may already have a product coding convention and if not then this is the time to put one together.

Let me know where you are with product codes.

You have called one table Inventory
Inventory (supplier ID, Product ID, Location, QTY)

You said that there is no need to record where the product comes from.
What use does the supplier Id have in this table.
 
Thank you sir for the reply,, yes there is coding convention but with just product code a person cant identify the color and size. because each product code has different sizes and colors. the supplier id is just for the information, in case my be some day it become important. Actually the inventory has the following fields(supplier ID, Product ID, Size, Color, Location, QTY).
 
I just want to know the following thing.
suppose there is a product code 001.
in warehouse : QTY=10
in Location A: QTY=3
in Location B: QTY=2

If company want to move 5 of the same product from warehouse to location 2,, it first should check where that much amount is available in warehouse and 2nd after moving it should update the qty in both location
if you can help,, it will be appreciated. thanks
 
As has been pointed out by Alan, you should not double post.


Have you considered a transaction type table to record
product movements from one location to another including quantity.
eg. Product XCAZ qty 12 From LocA to LocB date_timestamp

TransId
ProductInvolved
QtyOfProduct
FromLocation
ToLocation
.....other

Just a thought.

Knowing the Inventory Count at some point in Time, you can identify the "verifiable" stock counts using the starting Counts and the Transactions.

How (and how often) do you do counts (take stock) at different locations?

Have you seen Allen Browne's material at
http://allenbrowne.com/appinventory.html
 
Stock Control is a difficult subject but if you just want to have a record for each Supplier / Product / Size / Color / Location combination then the have a look at the attached database.

It is very basic with no front end or error checking but play around with the values in the 'subTestIt' procedure in the module and see the result in the table. Put the cursor in the procedure and F5 to run.

It does not provide any information as to how the stock got into each location and when it was put there.

It may may meet your needs for now and it can be developed further.
 

Attachments

thanks Mr. Highandwild and Mr. Jdraw.
Yes I have created a table which stores the movements of the product as per following.

InternalTransactions: (id, ProductInvolved, QTY, From, To, Date).
I have checked the attachments but still cant solve my problem. could you please tell me step by step. thanks
 
i have attached my database. I have made the customer order form, where I can enter their orders,, but I dont know how to update the inventory, means qty available in stock after sale of items. please help thanks
 

Attachments

I think I see an issue in your relationships between Orders and OrderDetails you should have an inner join not LEFT JOIN. Same issue with PurchaseOrders and PurchaseOrderDetails.

I didn't see a Customer Order Form, I did see Order Details.?????
 
actually I got the idea from Northwind sample database, there they used left join. yeap you are right its Order Details form where we can enter customer's order. But I couldnt get any idea from Northwind sample database, how they update the inventory.
 
Not sure why they use Left join - I'm probably overlooking something. Anyway If I were building it, I wouldn't use Left Join. There may be a condition that's not obvious to me at the moment.

I just installed 2010 an don't have a Northwind db to look at.

I have other things on the go today, so we be away until tonight.
 
Thanks Mr. jdraw, whenever you got time, please let me know, how to update the inventory after product is sold thanks. I will change the left join to inner join.
 
You must log in or register to reply here.

Similar threads

S
Need Help with Stock Transfer Form – Warehouse Issue
Replies
4
Views
172
Pat Hartman
P
D
Warehouse inventory and sites
Replies
10
Views
569
GPGeorge
GPGeorge
H
Any solution for Multi Unit of Measures?
2
Replies
38
Views
2,577
GPGeorge
GPGeorge
R
Current Inventory Calculations
Replies
6
Views
421
tvanstiphout
T
C
Hard problem with query
Replies
6
Views
597
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom