Merging two Queries?

[chriscook]

Hi,

I am in the process of setting up a stock database.

The database will be used by two people. The stores person who will adjust stock in once goods have been delivered, and occasionaly adjust stock out.

The other user will load PO's with various different Part numbers and will issue the delivery note to the stores person.

I have created 4 tables and 4 forms:

Tables:

tblAdjustments - For the storesperson to use to adjust stock in. Fields: ID, Date, PartNo, QtyIn, PONo, QtyOut, ContractNo, QtyTotal (calculated field), Notes

tblCustomerPO - to load PO's. Fields: ID, Date,Person, ContractNo, CustomerName, PONo, RigNo, PADNote, ComInv, POComplete(check box)

tblCustPOPN - To load Part Numbers for PO's - Fields: ID, PartNumber, Qty, Notes, Contract No, Shipped (checkbox)

tblStockList - To enter the stock items onto database - Fields: ID, PartNumber,Location, StockCheck, UnitPrice, PriceUpdate

Forms:

frmAdjustments - To enable the storesperson to adjust in or out a part number
frmCustomerPO - Main form to enter in PO Details
frmCustPOPN subform - To enter details of part(s) for the PO entered on mainform
frmStockList - To enter/modify stock details

I have called each field a nuique name such as tblStockListID also.

The adjustments will make a immediate impact to the stock, but the PO's will not change the stock figure until the line is shipped (checkbox)

My intial idea was to create two queries.

1. Sum of QtyTotal from tblAjustments and PartNo
2. PartNumber, Shipped, and Sum of Quantity from tblCustPoPN

There may be the same part number in both queries

But I then need to combine both these queries into one query (or a new table) to allow me to calculate the true stock value.

I am very new to access and would appreciate any advice anyone could offer.

Thanks

 

[jdraw]

There are several posts in this forum regarding Stock Control and/or Inventory Management.
I recommend you search the forum and read through some of the posts/dialogs to get some understanding of the issues/options from those who have experienced what you are undertaking.

You should read this article by Allen Browne to understand Quantity on Hand.

Read and research -- do not jump in to Access too quickly.
Good luck.

 

[KenHigg]

Storing QoH or calculating it on demand is an on going debate...