Inventory Location

[Daryl Au]

I have a table storing invenotry transaction in my warehouse. The 5 field
names are: doc (document number), outloc (out_location), inloc (in_location),
serial (item_serial), qty (quantity), recordtime (Recoard_date & time). I
would like to make a querry that I can check the location & quantity of
different items (serial). How can I do that?

Thanks so much.

Daryl

 

[KARL DEWEY]

QTY fields assumes you are rolling items up but the Item_Serial field
indicates a discrete record per item. Which is it?

Seems to me your need several tables instead of just one.
--- doc (document number), outloc (out_location), inloc (in_location), qty
(quantity), recordtime (Recoard_date & time) would record transactions or
item movement.

You need other tables to record individual item information - Serial, Model,
Price, Acquisition_Date, etc.

 

[Daryl Au]

Dear Karl,

You're right, I've 3 tables for that inventory system: The first one is
Location Table storing location codes (outloc & inloc); The second one is
Item Table storing item informaiton which includes model, item description,
item serial (item_serial); The third one is Transaction Table which used to
record the movement of items (from one point to another point).

One document (one document number woudl be used) of stock-in order may
contain more than one piece of one the item (but with the same serial), as
follows:

doc outloc inloc serial qty recordtime
123456 000 123 987 10 2007/12/10 11:00
123457 000 123 987 8 2007/12/10 12:00
123457 000 125 987 5 2007/12/10 12:30

p.s. loc "000" mean not in my warehouse.

In my query, I would like to show as follows:
serial qty loc
987 18 123
987 5 125

I don't know how to make the query.
Thanks

Daryl


"KARL DEWEY" 來函：