Periodic activity report of (Inventory) Products at each location

[M.I.Sahil]

I am developing a inventory software in VB6 using MS Access 2010 as Database, We have a main Store where we receive Products from different Vendors and then distribute the products to our sub-stores located at different locations. I want to create a Periodic activity report of (Inventory) Products at each location like what was the Opening-Balance of each product at that particular location, how many New items were transferred, what was the quantity Returned and how many were sold. For which I have created two tables

Table Name : Store contain fields as under:

STORE
FieldName (Type)
ProductID (Text)
Onhand (Number)
LocationID (text)
ClosdingDate (Date)
ClosingQty (Number)

LogDetail
Field Name(Type)
LogID(Text)
LogDate (Date)
ProductID (Text)
LocationID (Text)
TransactionType(Text)
Transaction(Number)
Rate (Number)

TransactionType Field contain the value like ( I, T, R and S) where (I =IN, T =Transfer, R=Retuned, S=Sale)
What will be the query for following report format. (Previous Balance is required from the last physical stock taking date say 30/08/2014)
From Dt:01/09/2014 | ToDt: 15/09/2014

LocationID ProductID RATE PreviousBalance Transfer Return Sale NewBalance
B AA1 1000 4 3 0 0 7
B AA2 2500 0 4 0 0 4
B BB1 3000 6 0 0 0 6
B BB2 1000 0 4 0 4 0
B BB3 1500 3 0 0 0 3
B CC1 3000 6 0 6 0 0
B CC3 1200 0 10 2 5 3
19 21 8 9 23

[adam_k]

This is a bad design / idea / report and the solution won't be really nice.
It's been a while since I've done anything with Access, but since nobody else is replying I'm going to give you a general idea and hope you can figure it out.

As you said you are going to need the previous balance. You've easily set it as the last physical stock tacking being the 1st day prior to the period you are looking into, but if that's not the case you need to calculate it as you would the new balance.

It is a pretty easy query to calculate the quantities for Transfer, Return and Sale using a crosstab query. Use the TransactionType field for ColumnHeading, the ProductId for RowHeading and sum the Transaction (I'm not sure if that's the quantity) as the value. Use the dates as criteria, but don't show or group by them as it will mess up things.

Then you simply (!) have to join against the initial balance and calculate the new balance by adding and subtracting the relevant columns. Please take care to outer join as to make sure you include products with inventory but no movements in the set period and new products that didn't have inventory in the start of the period, but have transactions and possibly newbalance.

If you copy this to a second query that would insert into a table the last stock taking transaction for each item and use the starting date of your report, you could have a table with the previous balance.

Good luck.