torttion
asked on
Access Asset Inventory Summation
Hi,
I have an simple access database that I'm using the track inventory items. I have a table with four fields, the first is an auto numbered key field, then I have a "From" location field, a "To" location Field and "Quantity" field that tracks the number of units moved from the "From" location to the "To" location.
I'd like to be able to choose a location and have the database add the quantity delivered to a location and then subtract the quantity taken From that location to give me a total number of items at that location.
I've done this in excel simply have a "From Qty" column and a "To Qty" column. Then I multiply the From column by -1 to make it a negative number and add it to the To column which gives me the total number of items at any given location.
Any help is greatly appreciated.
I have an simple access database that I'm using the track inventory items. I have a table with four fields, the first is an auto numbered key field, then I have a "From" location field, a "To" location Field and "Quantity" field that tracks the number of units moved from the "From" location to the "To" location.
I'd like to be able to choose a location and have the database add the quantity delivered to a location and then subtract the quantity taken From that location to give me a total number of items at that location.
I've done this in excel simply have a "From Qty" column and a "To Qty" column. Then I multiply the From column by -1 to make it a negative number and add it to the To column which gives me the total number of items at any given location.
Any help is greatly appreciated.
Oops I forgot a field.
Trx_ID - auto number primary key
Trx_LocationID (foreign key to Location table)
Trx_Date
Trx_TrxType - (foreign key to transaction types)
Trx_ItemID - (foreign key to items)
Trx_Quantity
With the above structurer you simple sum the records to for a location and Item get the quantity.
Trx_ID - auto number primary key
Trx_LocationID (foreign key to Location table)
Trx_Date
Trx_TrxType - (foreign key to transaction types)
Trx_ItemID - (foreign key to items)
Trx_Quantity
With the above structurer you simple sum the records to for a location and Item get the quantity.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1) a record for the From with a negativequantityy
2) a record for the To with positivevquantityty.
transactiontion tawouldoudl look something like:
Trx_ID - auto number primary key
Trx_LocationID (foreign key to Location table)
Trx_Date
Trx_TrxType - (foreign key to transaction types)
Trx_Quantity