Periodic activity report of (Inventory) Products at every location

[Mazhar.Iqbal]

I am developing an inventory system in vb6 with access 2010. 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 every location like what was the opening-balance, how many New items were transferred or repeated , what was the quantity Returned and how may were sold. For which I have created two tables
Table Name : Store contain fields as under:
STORE
Field Name
Type
ProductID
Text
Onhand
Number
LocationID
text
ClosingDate
Date
ClosingQty
Number
LogDetail
Field Name
Type
LogID
Text
LogDate
Date
ProductID
Text
LocationID
Text
TransactionType
Text
QtyIN
Number
QtyTransfer
Number
QtyRetrun
Number
QtySale
Number
Rate
Number


What will be the query for following report format.
From Dt:01/09/2014 ToDt: 15/09/2014
LocID ProductID RATE PrevBalance Transfer Return Sale EndBal
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

 

[Uncle Gizmo]

Not the answer you were looking for but you might find it useful to have a read and there's some free code as well!

http://allenbrowne.com/appinventory.html

 

[plog]

I'd give Gizmo's link a read--what you have posted isn't a good structure. You've got way to many fields in your tables, specifically LogDetail.

When you have similar field sets (QtyIn, QtyTransfer, QtyReturn...) its a sign you have a bad structure. Instead of adding data horizontally (with more columns), you need to add it vertically (with more rows). LogDetail needs only 1 Qty field, but another field to tell you what that Qty represents (In, Transfer, Return, etc.). That way you can add that 1 quantity field and get your total.

This should look like a checkbook register. You enter a new line for every transaction, wether that transaction is a deposit, interest, a check, an ATM transaction, etc.--it has just one amount column. Then when you need your balance, you add those up to determine it. Your LogDetail should resemble that.

Again, give Gizmo's link a read to see how it should be done.

 

[Mazhar.Iqbal]

I'd give Gizmo's link a read--what you have posted isn't a good structure. You've got way to many fields in your tables, specifically LogDetail.

When you have similar field sets (QtyIn, QtyTransfer, QtyReturn...) its a sign you have a bad structure. Instead of adding data horizontally (with more columns), you need to add it vertically (with more rows). LogDetail needs only 1 Qty field, but another field to tell you what that Qty represents (In, Transfer, Return, etc.). That way you can add that 1 quantity field and get your total.

This should look like a checkbook register. You enter a new line for every transaction, wether that transaction is a deposit, interest, a check, an ATM transaction, etc.--it has just one amount column. Then when you need your balance, you add those up to determine it. Your LogDetail should resemble that.

Again, give Gizmo's link a read to see how it should be done.

Thank u very much, I have created 1 qty field namley Transactions as per ur guidance , and use TrnasactionType field to clarify the transaction as I for IN , T for Transfer, R for Return and S for Sale (save R and S qty with -ve sign). can u please, let me know what should be the query for the required report format.

 

[plog]

Now its simply a running total query. Search this forum for that term ("running total query").

 

[Mazhar.Iqbal]

Now its simply a running total query. Search this forum for that term ("running total query").

I have created a query as under :

SELECT A.ProductID, Sum(IIf(IsNull(A.Trans),0,A.Trans)) AS OP, B.TRN, B.RTN
FROM LogDetail AS A LEFT JOIN (SELECT ProductID, Sum(IIf([Action]="TR",[Trans],0)) AS TRN, Sum(IIf([Action]="RT",[Trans],0)) AS RTN FROM LogDetail WHERE LocationID=[MyLocation] AND LogDate between [FromDt] and [ToDt] GROUP BY ProductID) AS B ON A.PRODUCTID = B.PRODUCTID
WHERE (((A.LogDate)<[FromDt]) AND ((A.LocationId)=[MyLocation]))
GROUP BY A.ProductID, B.TRN, B.RTN, A.LocationID
HAVING (((A.LocationID)=[MyLocation]) AND ((Sum(IIf(IsNull([A].[Trans]),0,[A].[Trans])))<>0));

(Where OP stands for Opening Balance)
BUT the result of this query is not as I want, it gives the correct previous balance of every product but It does not retrieves all the transaction made during the given period i.e FromDt and ToDt. Can u please, let me know what should be the correct query, which show all the products with there previous balance and also show all the transactions made during the a given period of time i.e Between FromDt and ToDt. but Hope u understand what does i mean.

 

[plog]

Can you post sample data from LogDetail--include field names. Then post what resulting data should be generated based on the initial sample data. I need 2 sets of data--starting from the table and what the query should generate based on that starting data.

 

[Mazhar.Iqbal]

Can you post sample data from LogDetail--include field names. Then post what resulting data should be generated based on the initial sample data. I need 2 sets of data--starting from the table and what the query should generate based on that starting data.

Thanks for response, please find attached the sample data including field names.
sample-data.pdf in zip

 

[plog]

You've given me too much. I just wanted 2 sets of data--starting and resulting. I think you've included your ill fated attempt at it, which does me no good, especially since I don't know which is which.

Additionally, you're formatting this like a report--with a data section in the top (to define a period) and then records of data. For now, let's work within the parameters of a query. You can only produce rows of data.

So give me 2 sets of data--starting data and expected result. And you can't define a period in the header.

 

[Mazhar.Iqbal]

You've given me too much. I just wanted 2 sets of data--starting and resulting. I think you've included your ill fated attempt at it, which does me no good, especially since I don't know which is which.

Additionally, you're formatting this like a report--with a data section in the top (to define a period) and then records of data. For now, let's work within the parameters of a query. You can only produce rows of data.

So give me 2 sets of data--starting data and expected result. And you can't define a period in the header.

You are very much right that we can't define period in the header, (What I have provided is a query based report, where period is a query criteria for example (.....Where LocationID=[Mylocation] And LogDate Between [FromDt] and [ToDt]), anyhow I have attached a new sample which hopefully explain that what is actually required. I once again thank u very much for response.

 

[plog]

This SQL will (almost) produce the results you identified:

SELECT LogDetail.LocationID, LogDetail.ProductID, Max(LogDetail.Rate) AS EndRate, 1*NZ(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#9/1/2014#"),0) AS OpeningBalance, Sum(IIf([Action]="TR",[Trans],0)) AS Transfer, Sum(IIf([Action]="RT",[Trans],0)) AS Retrun, Sum(IIf([Action]="SL",[Trans],0)) AS Sale, 1*NZ(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<=#9/10/2014#"),0) AS ClosingBalance
FROM LogDetail
WHERE (((LogDetail.LogDate)>=#9/1/2014# And (LogDetail.LogDate)<=#9/10/2014#))
GROUP BY LogDetail.LocationID, LogDetail.ProductID, 1*NZ(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#9/1/2014#"),0), 1*NZ(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<=#9/10/2014#"),0);

I say almost, because your results have 5 CC03 sales from 9/1/2014 - 9/10/2014, but that's not supported by the data you provided. To change time periods, there are 3 spots you have to update the query: In the criteria under LogDate, in the OpeningBalance field and the ClosingBalance field.

Additionally, I had to make an assumption. What happens if a product has 2 different rates for a period? My query returns the one with the highest value.

 

[Mazhar.Iqbal]

This SQL will (almost) produce the results you identified:

SELECT LogDetail.LocationID, LogDetail.ProductID, Max(LogDetail.Rate) AS EndRate, 1*NZ(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#9/1/2014#"),0) AS OpeningBalance, Sum(IIf([Action]="TR",[Trans],0)) AS Transfer, Sum(IIf([Action]="RT",[Trans],0)) AS Retrun, Sum(IIf([Action]="SL",[Trans],0)) AS Sale, 1*NZ(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<=#9/10/2014#"),0) AS ClosingBalance
FROM LogDetail
WHERE (((LogDetail.LogDate)>=#9/1/2014# And (LogDetail.LogDate)<=#9/10/2014#))
GROUP BY LogDetail.LocationID, LogDetail.ProductID, 1*NZ(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#9/1/2014#"),0), 1*NZ(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<=#9/10/2014#"),0);

I say almost, because your results have 5 CC03 sales from 9/1/2014 - 9/10/2014, but that's not supported by the data you provided. To change time periods, there are 3 spots you have to update the query: In the criteria under LogDate, in the OpeningBalance field and the ClosingBalance field.

Additionally, I had to make an assumption. What happens if a product has 2 different rates for a period? My query returns the one with the highest value.

Thanks, I have tried ur code but the output does not match the required results.

I m attaching the Database with a sample data and also attaching a Excel file with two sets of data and required results. I will be very thankful to u for solving this problem as i m stuck with...

 

[plog]

How Come Product CC02 doesn't show up in the 9/1 - 9/10 data results?

 

[Mazhar.Iqbal]

How Come Product CC02 doesn't show up in the 9/1 - 9/10 data results?

As you see the closing Balance of Product CC02 is 0 (The Products having closing Balance > 0 should be retrieved ) and also there is no movement (Transaction) of CC02 from 01/09/2014 to 10/09/2014.

 

[plog]

That makes a little more difficult, but not impossible. It's going to require 2 sub-queries. Here's the SQL for the first one:

SELECT LogDetail.LocationID, LogDetail.ProductID, Sum(IIf([Action]="TR",[Trans],0)) AS Transfer, Sum(IIf([Action]="RT",[Trans],0)) AS Retrun, Sum(IIf([Action]="SL",[Trans],0)) AS Sale
FROM LogDetail
WHERE (((LogDetail.LogDate)>=#9/1/2014# And (LogDetail.LogDate)<=#9/10/2014#))
GROUP BY LogDetail.LocationID, LogDetail.ProductID;

Name that query 'sub_InventoryTotals_1'. It adds up all the transactions and divides them into the appropriate categories. Here's the second sub-query:

SELECT LogDetail.LocationID, LogDetail.ProductID, Max(LogDetail.Rate) AS EndRate, 1*NZ(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#9/1/2014#"),0) AS OpeningBalance, 1*NZ(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<=#9/10/2014#"),0) AS ClosingBalance, 1*NZ(DCount("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<=#9/10/2014# AND [LogDate]>=#9/1/2014#"),0) AS Transactions
FROM LogDetail
GROUP BY LogDetail.LocationID, LogDetail.ProductID, 1*NZ(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#9/1/2014#"),0), 1*NZ(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<=#9/10/2014#"),0), 1*NZ(DCount("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<=#9/10/2014# AND [LogDate]>=#9/1/2014#"),0);

Name that query 'sub_InventoryTotals_2'. It gets the opening and closing balances for all products for the timeframe as well as the number of transactions that occured in the timeframe. Finally, this SQL will generate the results you want:

SELECT sub_InventoryTotals_2.LocationID, sub_InventoryTotals_2.ProductID, sub_InventoryTotals_2.EndRate, sub_InventoryTotals_2.OpeningBalance, 1*NZ([Transfer],0) AS TotalTransfer, 1*NZ([Retrun],0) AS TotalReturn, 1*NZ([Sale],0) AS TotalSale, sub_InventoryTotals_2.ClosingBalance
FROM sub_InventoryTotals_2 LEFT JOIN sub_InventoryTotals_1 ON (sub_InventoryTotals_2.ProductID = sub_InventoryTotals_1.ProductID) AND (sub_InventoryTotals_2.LocationID = sub_InventoryTotals_1.LocationID)
WHERE ((([ClosingBalance]+[Transactions])>0));

It merges the balances with the individual transactions of the timeframe and excludes any that had 0 transactions and a 0 opening balance for the timeframe.

This too requires you to update the timeframe start and ending times in multiple places. My recommendation is to create a form where you enter those dates and have the forms reference those field values instead of having it prompt the user for the dates. Without a form, because the 2 sub-queries require the timeframe dates, you will have to input both dates twice (once for each sub-query). With a form, both queries can reference the same input value fields.

 

[Mazhar.Iqbal]

That makes a little more difficult, but not impossible. It's going to require 2 sub-queries. Here's the SQL for the first one:

SELECT LogDetail.LocationID, LogDetail.ProductID, Sum(IIf([Action]="TR",[Trans],0)) AS Transfer, Sum(IIf([Action]="RT",[Trans],0)) AS Retrun, Sum(IIf([Action]="SL",[Trans],0)) AS Sale
FROM LogDetail
WHERE (((LogDetail.LogDate)>=#9/1/2014# And (LogDetail.LogDate)<=#9/10/2014#))
GROUP BY LogDetail.LocationID, LogDetail.ProductID;

Name that query 'sub_InventoryTotals_1'. It adds up all the transactions and divides them into the appropriate categories. Here's the second sub-query:

SELECT LogDetail.LocationID, LogDetail.ProductID, Max(LogDetail.Rate) AS EndRate, 1*NZ(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#9/1/2014#"),0) AS OpeningBalance, 1*NZ(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<=#9/10/2014#"),0) AS ClosingBalance, 1*NZ(DCount("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<=#9/10/2014# AND [LogDate]>=#9/1/2014#"),0) AS Transactions
FROM LogDetail
GROUP BY LogDetail.LocationID, LogDetail.ProductID, 1*NZ(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#9/1/2014#"),0), 1*NZ(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<=#9/10/2014#"),0), 1*NZ(DCount("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<=#9/10/2014# AND [LogDate]>=#9/1/2014#"),0);

Name that query 'sub_InventoryTotals_2'. It gets the opening and closing balances for all products for the timeframe as well as the number of transactions that occured in the timeframe. Finally, this SQL will generate the results you want:

SELECT sub_InventoryTotals_2.LocationID, sub_InventoryTotals_2.ProductID, sub_InventoryTotals_2.EndRate, sub_InventoryTotals_2.OpeningBalance, 1*NZ([Transfer],0) AS TotalTransfer, 1*NZ([Retrun],0) AS TotalReturn, 1*NZ([Sale],0) AS TotalSale, sub_InventoryTotals_2.ClosingBalance
FROM sub_InventoryTotals_2 LEFT JOIN sub_InventoryTotals_1 ON (sub_InventoryTotals_2.ProductID = sub_InventoryTotals_1.ProductID) AND (sub_InventoryTotals_2.LocationID = sub_InventoryTotals_1.LocationID)
WHERE ((([ClosingBalance]+[Transactions])>0));

It merges the balances with the individual transactions of the timeframe and excludes any that had 0 transactions and a 0 opening balance for the timeframe.

This too requires you to update the timeframe start and ending times in multiple places. My recommendation is to create a form where you enter those dates and have the forms reference those field values instead of having it prompt the user for the dates. Without a form, because the 2 sub-queries require the timeframe dates, you will have to input both dates twice (once for each sub-query). With a form, both queries can reference the same input value fields.

Thank u very much.