Member Avatar for 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

  • 2 Contributors
  • 1 Reply
  • 241 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by adam_k

Dani AI

Generated

A cleaner, reliable approach is to treat physical stocktakes as the authoritative snapshot and the transaction log as the single source of movement. Compute PreviousBalance by finding the latest stocktake for each product/location on or before the report start date, aggregate movements for the date range from the transaction log, then calculate NewBalance = PreviousBalance + additions − subtractions. ’s suggestion to aggregate (or use a crosstab) and outer-join the snapshot is exactly the right direction.

Step 1 — latest stocktake per product/location (two-query pattern in Access):

-- qryMaxStockDate
SELECT LocationID, ProductID, Max(StockDate) AS MaxStockDate
FROM StockTake
WHERE StockDate <= [FromDate]
GROUP BY LocationID, ProductID;

Join that back to StockTake to return the quantity recorded on that MaxStockDate.

Step 2 — aggregate movements for the period (Access example using IIF):

SELECT LocationID, ProductID,
  Sum(IIF(TransactionType='T', Quantity, 0)) AS TransferQty,
  Sum(IIF(TransactionType='R', Quantity, 0)) AS ReturnQty,
  Sum(IIF(TransactionType='S', Quantity, 0)) AS SaleQty
FROM LogDetail
WHERE LogDate BETWEEN [FromDate] AND [ToDate]
GROUP BY LocationID, ProductID;

Step 3 — left-join aggregates to the latest-stocktake query, use NZ(...) to treat NULLs as zero, and compute NewBalance. Use outer joins so products with only a snapshot or only transactions still appear.

Practical notes: model transfers as two postings (out from source, in to destination) or use a transfer header with two legs so sums are accurate; avoid maintaining a hand-edited OnHand field — recompute from snapshots + movements or maintain nightly snapshots if performance requires. Index LogDetail on (ProductID, LocationID, LogDate) and be careful with Access date literals/parameters.

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.